Reading messy CSV files

Real CSVs are rarely clean, and knowing the options turns a failing import into a one-line fix.

read_csv runs a sniffer that samples the file to guess the delimiter, quote and escape characters, whether there is a header, and the type of each column. It is usually right, but you can override anything:

delim, quote, escape, header for the dialect
columns = {'id': 'INTEGER', 'name': 'VARCHAR'} to fix names and types (disables type detection), or types = {...} to override only some
dateformat / timestampformat for non-ISO dates, e.g. '%d/%m/%Y'
nullstr for values like 'NA' that should be NULL
all_varchar = true to load everything as text and cast later
ignore_errors = true to skip rows that fail to parse, and store_rejects = true to keep them in a reject_errors table for inspection
filename = true adds a column with the source file name

sniff_csv('file.csv') shows what the sniffer decided, which is the first thing to run when a load goes wrong. A common failure is a column inferred as integer from the sample that later contains text; increase sample_size (or -1 for the whole file) or set the type explicitly.

CSV parsing is parallel and fast, but it is still parsing: convert large CSVs to Parquet once if they will be read repeatedly.

Example

import duckdb, os, tempfile

os.chdir(tempfile.mkdtemp())
with open("people.csv", "w") as f:
    f.write("name;born;score\nAnn;03/04/1990;7\nBob;NA;x\nCy;12/11/1985;9\n")

rel = duckdb.sql("""
    SELECT * FROM read_csv('people.csv', delim = ';', header = true,
        dateformat = '%d/%m/%Y', nullstr = 'NA',
        columns = {'name': 'VARCHAR', 'born': 'DATE', 'score': 'INTEGER'},
        ignore_errors = true)
""")
print(rel.fetchall())
# [('Ann', datetime.date(1990, 4, 3), 7), ('Cy', datetime.date(1985, 11, 12), 9)]

Remember

The sniffer guesses dialect, header and types from a sample
columns/types, dateformat and nullstr fix most bad guesses
ignore_errors skips bad rows; store_rejects keeps them for review
Run sniff_csv to see what was detected

Watch out for

Silently dropping rows with ignore_errors = true and never checking how many were rejected.

Check yourself

A column was detected as BIGINT, but row 2 million contains 'n/a'. What is a good fix?

Official docs: duckdb.org/docs/current/data/csv/overview

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks