Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 38 ยท Reading Files and Formats

Reading CSV Files

Query CSV files directly in DuckDB with read_csv: dialect sniffing, header and delimiter options, explicit types, date formats, error handling, COPY into tables and fixing messy files.

CSV is the world's most common data format and also one of the most inconsistent: different delimiters, quoting, header rows, date formats, encodings and stray bad lines. DuckDB's CSV reader is one of its best features. It sniffs the dialect and types automatically, handles most files with no options, and offers precise controls when a file is truly nasty.

The CSV reader needs real files on disk, which a web page does not have. The DuckDB code is read-only. The runnable stand-ins show the same cleaning steps on text columns in SQLite, because most CSV work is about deciding what the raw text means.

The one-line way

A file name where a table goes is enough:

SQL
-- DuckDB (read-only example)
SELECT * FROM 'customers.csv';
SELECT country, count(*) FROM 'customers.csv' GROUP BY country;

When DuckDB sees a name ending in .csv (or .csv.gz) it calls the read_csv table function with the defaults. You can call it explicitly to pass options:

SQL
-- DuckDB (read-only example)
SELECT * FROM read_csv('customers.csv', header = true, delim = ',');

What auto-detection finds

The sniffer reads a sample of the file (20,480 rows by default) and works out:

  1. The dialect: delimiter (comma, semicolon, tab, pipe), quote and escape characters, newline style.
  2. Whether there is a header row.
  3. The type of each column: BIGINT, DOUBLE, BOOLEAN, DATE, TIMESTAMP, or VARCHAR as a fallback.

To see what it decided, ask:

SQL
-- DuckDB (read-only example)
DESCRIBE SELECT * FROM 'customers.csv';
FROM sniff_csv('customers.csv');

sniff_csv returns the detected delimiter, quote, header flag, date formats and even a ready-to-paste read_csv(...) call. Always check DESCRIBE on a new file: if a column that should be a number shows as VARCHAR, there is a dirty value somewhere (the sample may also have been unlucky, see "Type detection" below).

How read_csv decides

Sniff Read a sample of rows
Dialect Delimiter, quote, header
Types Try narrow types first
Read Parse the whole file in parallel

You can override any step with an option.

1 / 2

The options you will use most

Option Meaning
header = true/false Is the first line a header?
delim = ';' Field separator (sep is an alias)
quote = '"', escape = '"' Quoting rules
columns = {'id': 'INTEGER', ...} Fix names and types, no detection
types = {'zip': 'VARCHAR'} Override only some columns
dateformat = '%d/%m/%Y' Date parse format
timestampformat = '...' Timestamp parse format
skip = 3 Skip lines before the header
nullstr = ['', 'NA', 'N/A'] Text values that mean NULL
all_varchar = true Read everything as text, convert later
ignore_errors = true Skip rows that cannot be parsed
null_padding = true Fill missing trailing columns with NULL
union_by_name = true When reading several files, match columns by name
filename = true Add a column with the source file name
sample_size = -1 Sniff using the whole file
encoding = 'latin-1' Non-UTF-8 files

Taking control of types

Auto-detection sometimes guesses wrong. A ZIP code 01234 looks like a number, so DuckDB would give 1234 and lose the leading zero. State the type yourself:

SQL
-- DuckDB (read-only example)
SELECT *
FROM read_csv('customers.csv',
              types = {'zip': 'VARCHAR', 'signed_up': 'DATE'},
              dateformat = '%d/%m/%Y');

-- or describe every column explicitly
SELECT * FROM read_csv('customers.csv',
  header = true,
  columns = {'id': 'INTEGER', 'name': 'VARCHAR', 'zip': 'VARCHAR', 'signed_up': 'DATE'});

The stand-in below mimics the same pitfall: values that look numeric but are identifiers. Casting them destroys information:

SQL Stand-in: identifiers are not numbers

Press Run (or Ctrl + Enter) to see the result here.

Dirty data: find it, then decide

When a file has bad rows, you have three choices. First, make the problem visible:

SQL
-- DuckDB (read-only example)
SELECT * FROM read_csv('orders.csv', store_rejects = true);
SELECT * FROM reject_errors;     -- line, column, the offending text, the error message
SELECT * FROM reject_scans;      -- the options used for the scan

Second, read everything as text and convert safely, so you can inspect the failures:

SQL
-- DuckDB (read-only example)
SELECT id,
       try_cast(amount AS DECIMAL(10,2)) AS amount,
       try_strptime(placed, '%d/%m/%Y')::DATE AS placed
FROM read_csv('orders.csv', all_varchar = true)
WHERE try_cast(amount AS DECIMAL(10,2)) IS NULL AND amount IS NOT NULL;   -- the bad ones

Third, if some bad rows are acceptable, ignore_errors = true skips them. Prefer the first two options: silently dropping rows hides problems.

The cleaning pattern with text columns works in the stand-in as well:

SQL Stand-in: clean text into proper types

Press Run (or Ctrl + Enter) to see the result here.

Type detection and large files

The sniffer samples 20,480 rows by default. If the first 20,000 rows of a column are integers and row 90,000 says "n/a", the type is chosen as integer and the read later fails with a conversion error that tells you the line. Remedies: sample_size = -1 (check everything), all_varchar = true, or declare the type with types = {...}.

DuckDB reads CSV in parallel across your cores, even for one big file. Reading a quoted-newline-heavy file may fall back to a slower single-thread mode (parallel = false forces it). For files you will query repeatedly, convert once:

SQL
-- DuckDB (read-only example)
CREATE TABLE orders AS SELECT * FROM 'orders.csv';
COPY (SELECT * FROM 'orders.csv') TO 'orders.parquet' (FORMAT parquet);

Querying the Parquet or the table afterwards is far faster than re-parsing text.

COPY: loading and saving

COPY ... FROM loads a CSV into an existing table with its own types:

SQL
-- DuckDB (read-only example)
CREATE TABLE customers (id INTEGER, name VARCHAR, zip VARCHAR);
COPY customers FROM 'customers.csv' (HEADER, DELIMITER ',');

And COPY ... TO writes a CSV, covered in Writing files and remote data.

Compressed files

.csv.gz and .csv.zst files are decompressed on the fly. A reading tip: a gzip file cannot be split, so one thread decompresses it; for repeated work convert it to Parquet.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does DuckDB's CSV sniffer detect?

    The sniffer samples the file and infers its dialect, header and column types automatically.
  2. 2. A ZIP code column loses its leading zeros. What is the best fix?

    Identifiers are text, so read them as VARCHAR to keep every character.
  3. 3. Which option reads every column as text so you can clean it later?

    all_varchar skips type detection, and then you convert with try_cast or try_strptime.
  4. 4. Why can a type error appear on line 90,000 of a file the sniffer accepted?

    By default only about 20,000 rows are sampled, so later rows may not fit the guessed type.
  5. 5. What is a good step for a large CSV you will query many times?

    Text parsing is slow; a table or Parquet file avoids repeating it.

Summary

  • SELECT * FROM 'file.csv' works; read_csv adds options.
  • The sniffer detects dialect, header and types; check with DESCRIBE or sniff_csv.
  • Override with types, columns, dateformat, nullstr, skip, all_varchar.
  • Surface bad rows with store_rejects or try_cast before using ignore_errors.
  • Convert repeatedly used CSV into a table or Parquet.

Next: Parquet files