DuckDB read_csv Options You Will Actually Need in Practice

DuckDB's read_csv has more than forty parameters and a good sniffer, so most files need none of them and a few files need three or four. This article covers the ones I have needed, each on a file that makes it necessary, with the output before and after. It also covers the one mistake that took me a while to see, the way nullstr replaces the empty string rather than adding to it. The list of parameters, with types and defaults, is the CSV import reference for 2.0, and the bigger picture is in the querying guide.

The file that needs several

A finance report with a title line, a comment line, semicolons, European numbers and two ways of writing "nothing":

Quarterly report generated 2025-04-01
# prepared by finance, do not edit
region;q1;q2;note
EMEA;1.234,50;2.000,00;n/a
APAC;900,00;;-
AMER;1.500,25;1.750,00;ok

Read with no options, DuckDB's sniffer already copes better than I expected:

duckdb -box -c "SELECT * FROM read_csv('report.csv')" 2>&1 | head -12

┌────────┬──────────┬──────────┬──────┐
│ region │    q1    │    q2    │ note │
├────────┼──────────┼──────────┼──────┤
│ EMEA   │ 1.234,50 │ 2.000,00 │ n/a  │
│ APAC   │ 900,00   │ NULL     │ -    │
│ AMER   │ 1.500,25 │ 1.750,00 │ ok   │
└────────┴──────────┴──────────┴──────┘

It skipped the first two lines, found the header on the third, and used the semicolon. The numbers stay as text, 1.234,50, since the sniffer will not guess a locale, and n/a and - are ordinary strings. sniff_csv reports what it did, and its Prompt column is the call that reproduces it:

duckdb -line -c "SELECT Delimiter, SkipRows, HasHeader, Prompt FROM sniff_csv('report.csv')"

Delimiter = ;
 SkipRows = 2
HasHeader = true
   Prompt = FROM read_csv('report.csv', auto_detect=false, delim=';', quote='', escape='', new_line='\n', skip=2, comment='', header=true, columns={'region': 'VARCHAR', 'q1': 'VARCHAR', 'q2': 'VARCHAR', 'note': 'VARCHAR'});

SkipRows = 2, Delimiter = ;, HasHeader = true. Everything that follows is about telling it the rest.

The numbers: decimal_separator and thousands

decimal_separator=',' and thousands='.' say how the numbers are written. Combined with nullstr for the two markers, and skip and comment spelled out so the file is read the same way every time, the whole call is:

duckdb -box -c "SELECT * FROM read_csv('report.csv', skip=1, comment='#', delim=';', decimal_separator=',', thousands='.', nullstr=['', 'n/a', '-'], header=true)"

┌────────┬─────────┬────────┬──────┐
│ region │   q1    │   q2   │ note │
├────────┼─────────┼────────┼──────┤
│ EMEA   │ 1234.5  │ 2000.0 │ NULL │
│ APAC   │ 900.0   │ NULL   │ NULL │
│ AMER   │ 1500.25 │ 1750.0 │ ok   │
└────────┴─────────┴────────┴──────┘

1.234,50 became 1234.5 and 2.000,00 became 2000.0, the n/a and - cells are NULL and 900,00 became 900.0. The resulting types confirm it:

duckdb -box -c "DESCRIBE SELECT * FROM read_csv('report.csv', skip=1, comment='#', delim=';', decimal_separator=',', thousands='.', nullstr=['', 'n/a', '-'], header=true)"

┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ region      │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ q1          │ DOUBLE      │ YES  │ NULL │ NULL    │ NULL  │
│ q2          │ DOUBLE      │ YES  │ NULL │ NULL    │ NULL  │
│ note        │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘

q1 and q2 are DOUBLE, and region and note are VARCHAR. I used skip=1 and comment='#' there instead of the sniffer's skip=2, because the first line is a title and the second a comment, and both routes land in the same place. Pick whichever makes the intent clear to the next reader.

The nullstr trap

My first attempt at this call set nullstr=['n/a', '-'] and declared q1 and q2 as DOUBLE. It failed:

duckdb -c "SELECT * FROM read_csv('report.csv', skip=1, comment='#', delim=';', decimal_separator=',', thousands='.', nullstr=['n/a', '-'], header=true, types={'q1': 'DOUBLE', 'q2': 'DOUBLE'})" 2>&1 | head -4

Conversion Error: CSV Error on Line: 5
Original Line: APAC;900,00;;-
Error when converting column "q2". Could not convert string "" to 'DOUBLE'

Could not convert string "" to 'DOUBLE' on line 5, the APAC row, where q2 is empty. The empty cell was no longer a NULL. By default DuckDB treats the empty string as NULL, and nullstr replaces that default with whatever list you give, so a list that names the other markers has to name the empty string too. The working call uses nullstr=['', 'n/a', '-']. The error message even says "Check whether the null string value is set correctly", which is accurate and easy to read past. It is the most common option mistake I have seen, since most people add a marker and forget the one that was already there.

Names and types

When the file has no usable header, or you want better names than the file's, names supplies them in order and types overrides the type of chosen columns, by name, leaving the sniffer to decide the rest:

duckdb -box -c "SELECT * FROM read_csv('customers.csv', header=true, names=['cust_id','full_name','mail','joined','bal'], types={'cust_id': 'INTEGER', 'bal': 'DECIMAL(10,2)'})"

┌─────────┬─────────────┬───────────────────┬────────────┬────────┐
│ cust_id │  full_name  │       mail        │   joined   │  bal   │
├─────────┼─────────────┼───────────────────┼────────────┼────────┤
│ 1       │ Ana Silva   │ [email protected]  │ 2025-01-15 │ 120.50 │
│ 2       │ Smith, Jane │ [email protected] │ 2025-02-03 │ 0.00   │
│ 3       │ Bo Chen     │ NULL              │ 2025-02-20 │ NULL   │
│ 4       │ Cy Dubois   │ [email protected]   │ 2025-03-01 │ 15.75  │
└─────────┴─────────────┴───────────────────┴────────────┴────────┘

Five columns got the names cust_id, full_name, mail, joined and bal. The types struct is keyed by the new names, cust_id as INTEGER and bal as DECIMAL(10,2), and the date column was still sniffed as a date. If you want the opposite, no guessing at all, columns={...} declares every column and name and turns detection off, which is how the view in the guide is written. When a file has a header you want to keep but one column goes wrong, types alone is enough.

When every column should be text, as the first step of a careful import, all_varchar=true is the shortcut:

duckdb -box -c "DESCRIBE SELECT * FROM read_csv('customers.csv', all_varchar=true)"

┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ id          │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ name        │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ email       │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ signup_date │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ balance     │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘

All five columns are VARCHAR, including id and balance. From there you cast with TRY_CAST where you choose, which makes every conversion visible in SQL.

Awkward headers

Names with spaces and leading digits are legal in a CSV and a nuisance in SQL. normalize_names=true cleans them: it lowercases, replaces non alphanumeric characters with underscores and prefixes a name that starts with a digit.

printf 'Order ID,Customer Name,2025 total\n1,Ana,10\n' > messy-names.csv
duckdb -box -c "SELECT * FROM read_csv('messy-names.csv')" -c "SELECT * FROM read_csv('messy-names.csv', normalize_names=true)"

┌──────────┬───────────────┬────────────┐
│ Order ID │ Customer Name │ 2025 total │
├──────────┼───────────────┼────────────┤
│ 1        │ Ana           │ 10         │
└──────────┴───────────────┴────────────┘
┌──────────┬───────────────┬─────────────┐
│ order_id │ customer_name │ _2025_total │
├──────────┼───────────────┼─────────────┤
│ 1        │ Ana           │ 10          │
└──────────┴───────────────┴─────────────┘

The upper table shows the file's names, Order ID, Customer Name and 2025 total, which need double quotes in every query. The lower one shows order_id, customer_name and _2025_total with normalize_names=true. The underscore prefix on the last one is how DuckDB marks a name that began with a digit. If another system will read the output, check that the names it expects survived the cleaning.

Short rows

A file whose rows have different numbers of fields is the case the sniffer handles worst. In the test file below, row 2 has two fields and row 3 has one:

printf 'a,b,c\n1,2,3\n4,5\n6\n' > short.csv
duckdb -c "SELECT * FROM read_csv('short.csv')" 2>&1 | head -6
duckdb -box -c "SELECT * FROM read_csv('short.csv', null_padding=true)"

┌─────────┐
│  a,b,c  │
│ varchar │
├─────────┤
│ 1,2,3   │
│ 4,5     │
┌───┬──────┬──────┐
│ a │  b   │  c   │
├───┼──────┼──────┤
│ 1 │ 2    │ 3    │
│ 4 │ 5    │ NULL │
│ 6 │ NULL │ NULL │
└───┴──────┴──────┘

The first output is the default: one column named a,b,c, with every line, commas included, as a value. The sniffer could not find a delimiter that gave a consistent width, so it gave up on splitting. The second, with null_padding=true, is the table you wanted, with NULL where fields were missing. That option fixes the shape and says nothing about why the rows are short, which could be an unquoted newline or a truncated export. The article on skipping bad rows shows how to see which rows were damaged.

A short list of the rest

sample_size controls how many rows the sniffer reads (the auto detection page lists what it detects), 20,480 by default, and -1 reads them all, which the sniffing article shows matters when a type changes late. dateformat and timestampformat declare how dates are written, as in mixed date formats. union_by_name and filename are for several files at once, in globbing a folder. ignore_errors and store_rejects keep a read going past bad rows. encoding accepts utf-8, utf-16 and latin-1, and the reference says other encodings need an extension or a conversion first. compression is detected from the file extension, and delim, quote and escape override the dialect when the sniffer is wrong.

Options I left out

The reference lists more: rejects_limit, max_line_size, buffer_size, parallel, hive_partitioning, force_not_null and auto_type_candidates, among others. Each is for a specific situation I have not needed in this section, and I would rather you read the table when you hit one than rely on my summary. The rule I keep is to start with no options, read DESCRIBE, and add the smallest option that fixes the first thing that is wrong. A call with fifteen options is a sign that the file wants converting once, with a script you can read, instead of being interpreted every time.