CSV type issues troubleshooting

Common issues and solutions for CSV type problems in ClickHouse and Tinybird.

Common errors

CANNOT_PARSE_TEXT

Error: CANNOT_PARSE_TEXT: Cannot parse CSV

Cause: Malformed CSV data or wrong delimiter

Solution: Check CSV format and delimiter

-- Valid CSV
id,user_id,value,timestamp
123,456,10.5,2023-01-01 12:00:00

-- Invalid CSV (wrong delimiter)
id;user_id;value;timestamp
123;456;10.5;2023-01-01 12:00:00

TYPE_MISMATCH

Error: TYPE_MISMATCH: Cannot convert String to UInt32

Cause: CSV column contains non-numeric data in numeric column

Solution: Use safe conversion functions

-- Use safe conversion
SELECT toUInt32OrNull(numeric_column) as safe_number
FROM events

CSV format issues

Missing headers

Issue: CSV file without column headers

Solution: Specify column names in schema

-- Specify column names
SCHEMA >
  id UInt32,
  user_id String,
  value Float64,
  timestamp DateTime

Inconsistent delimiters

Issue: Mixed delimiters in CSV file

Solution: Use consistent delimiter or handle in schema

-- Handle different delimiters
-- Use tab-separated values
FORMAT TabSeparated

Quote handling

Issue: CSV fields with quotes causing parsing issues

Solution: Handle quotes properly

-- Use proper quote handling
FORMAT CSVWithNames
SETTINGS format_csv_allow_single_quotes = 1

Data type problems

Mixed data types in columns

Issue: Same column has different data types

Solution: Use string type and convert later

-- Use string for mixed types
CREATE TABLE events (
  id UInt32,
  mixed_column String,  -- Handle mixed types as string
  value Float64
)

Date format issues

Issue: CSV dates in unexpected format

Solution: Parse dates explicitly

-- Parse dates with specific format
SELECT toDateTime(date_string, 'YYYY-MM-DD HH:MM:SS') as parsed_date
FROM events

Best practices

  1. Validate CSV format - Check delimiter and quote handling
  2. Use consistent types - Keep column types consistent
  3. Handle mixed data - Use string type for mixed data
  4. Parse dates explicitly - Don't rely on automatic date parsing
  5. Use schema hints - Specify expected types in schema
Updated