DuckDB Skipping Bad Rows While Reading a Messy CSV

Sooner or later a CSV arrives with a few rows that do not parse: a letter in a number column, a record with a field missing, a date that does not exist. A strict reader stops at the first one, which is correct and unhelpful when you have a million good rows and three bad ones. DuckDB can read past them and, more usefully, keep a list of what it skipped. This article runs both options on a six row file with four different problems, shows what is returned, what is recorded, and a behavior of ignore_errors that changed my counts in a way I did not expect. The sniffer that decides how a file is read is covered in the sniffing article, and the full description of the options is on the faulty CSV files page.

The file

A header and six rows, each of which is a different kind of wrong:

id,name,qty,joined
1,Ana,3,2025-01-15
2,Bo,x,2025-01-16
3,Cy,5
4,"Dee,6,2025-01-17
5,Eli,7,2025-02-30
6,Fay,8,2025-01-18

Row 2 has x where qty should be a number. Row 3 has three fields where four are expected. Row 4 has an opening double quote that is never closed. Row 5 has 2025-02-30, a date that does not exist. Rows 1 and 6 are fine.

What a plain read does

With no options, the result is not what you might expect from a file with one bad row in each of four columns:

duckdb -c "SELECT * FROM read_csv('faulty.csv')" 2>&1 | head -6

┌─────────────────────┐
│ id,name,qty,joined  │
│       varchar       │
├─────────────────────┤
│ 1,Ana,3,2025-01-15  │
│ 2,Bo,x,2025-01-16   │

There is one column, named id,name,qty,joined, and every line of the file is a value in it, commas included. The sniffer could not find a delimiter and quote combination that gave every row the same width, since row 3 is short, so it gave up and fell back to reading each line whole. That is a quiet failure, and the one to recognize first: a single column named after your entire header is the sign that the file did not parse. It is also the first reason to turn on ignore_errors, which tells the sniffer it may disregard some rows when it decides.

Skip what does not parse

ignore_errors=true makes the reader drop rows it cannot parse and continue:

duckdb -box -c "SELECT * FROM read_csv('faulty.csv', ignore_errors=true)"

┌────┬──────┬─────┬────────────┐
│ id │ name │ qty │   joined   │
├────┼──────┼─────┼────────────┤
│ 1  │ Ana  │ 3   │ 2025-01-15 │
│ 2  │ Bo   │ x   │ 2025-01-16 │
│ 4  │ "Dee │ 6   │ 2025-01-17 │
│ 5  │ Eli  │ 7   │ 2025-02-30 │
│ 6  │ Fay  │ 8   │ 2025-01-18 │
└────┴──────┴─────┴────────────┘

Now there are four columns and five rows. The short row 3 is gone. The rest survive, including row 2 with x in qty and row 5 with 2025-02-30, because nothing has said that qty is a number or joined is a date: all four columns are text, and x is valid text. The row 4 value "Dee also came through, with its stray quote attached, since the sniffer did not choose a quote character for this file at all. Skipping rows only has an effect on the checks that the declared structure can make, and the CSV import overview lists the options that control it. To catch the bad quantity and the impossible date, declare the types:

duckdb -box -c "SELECT * FROM read_csv('faulty.csv', ignore_errors=true, types={'qty': 'INTEGER', 'joined': 'DATE'})"

┌────┬──────┬─────┬────────────┐
│ id │ name │ qty │   joined   │
├────┼──────┼─────┼────────────┤
│ 1  │ Ana  │ 3   │ 2025-01-15 │
│ 4  │ "Dee │ 6   │ 2025-01-17 │
│ 6  │ Fay  │ 8   │ 2025-01-18 │
└────┴──────┴─────┴────────────┘

With types={'qty': 'INTEGER', 'joined': 'DATE'} the result is three rows: 1, 4 and 6. Row 2 failed the integer conversion, row 3 was short, row 5 failed the date conversion. All three disappeared, and the output says nothing about it. Row 4, with its unclosed quote, is in the result as "Dee. That is a reminder that "no error" does not mean "correct": the reader kept it because its fields parsed.

Record what was skipped

store_rejects=true is the option that makes skipping safe enough to use. It keeps the data returned the same and records every rejected row in a table named reject_errors:

duckdb -box -c "SELECT * FROM read_csv('faulty.csv', store_rejects=true, types={'qty': 'INTEGER', 'joined': 'DATE'})" -c "SELECT line, column_name, error_type, csv_line, error_message FROM reject_errors ORDER BY line"

┌────┬──────┬─────┬────────────┐
│ id │ name │ qty │   joined   │
├────┼──────┼─────┼────────────┤
│ 1  │ Ana  │ 3   │ 2025-01-15 │
│ 4  │ "Dee │ 6   │ 2025-01-17 │
│ 6  │ Fay  │ 8   │ 2025-01-18 │
└────┴──────┴─────┴────────────┘
┌──────┬─────────────┬─────────────────┬────────────────────┬────────────────────────────────────────────────────────────────────────────────────────┐
│ line │ column_name │   error_type    │      csv_line      │                                     error_message                                      │
├──────┼─────────────┼─────────────────┼────────────────────┼────────────────────────────────────────────────────────────────────────────────────────┤
│ 3    │ qty         │ CAST            │ 2,Bo,x,2025-01-16  │ Error when converting column "qty". Could not convert string "x" to 'INTEGER'          │
│ 4    │ joined      │ MISSING COLUMNS │ 3,Cy,5             │ Expected Number of Columns: 4 Found: 3                                                 │
│ 6    │ joined      │ CAST            │ 5,Eli,7,2025-02-30 │ Error when converting column "joined". Could not convert string "2025-02-30" to 'DATE' │
└──────┴─────────────┴─────────────────┴────────────────────┴────────────────────────────────────────────────────────────────────────────────────────┘

The first table is the data, the same three rows. The second is the explanation, one row for each rejected line:

• line 3: column qty, error type CAST, the original text 2,Bo,x,2025-01-16, and the message Could not convert string "x" to 'INTEGER'

• line 4: column joined, error type MISSING COLUMNS, text 3,Cy,5, and Expected Number of Columns: 4 Found: 3

• line 6: column joined, error type CAST, text 5,Eli,7,2025-02-30, and Could not convert string "2025-02-30" to 'DATE'

The line numbers count lines of the file, with the header as line 1, so line 3 is the second data row. That differs from the id column and is what you want when you open the file in an editor. This table is the thing to hand to whoever produced the file: three lines, each with its reason, taken from the file itself.

A second table describes how each file was read:

duckdb -line -c "SELECT count(qty) AS qty_parsed FROM read_csv('faulty.csv', store_rejects=true, types={'qty': 'INTEGER', 'joined': 'DATE'})" -c "SELECT scan_id, file_path, delimiter, quote, has_header, skip_rows FROM reject_scans"

qty_parsed = 4
   scan_id = 2
 file_path = faulty.csv
 delimiter = ,
     quote = (empty)
has_header = true
 skip_rows = 0

reject_scans holds the file path, the delimiter, quote, header flag and skipped rows used for that read, keyed by scan_id. The scan_id in the errors table points to it. Both tables live for the connection, so they are for inspecting within the session or copying out with CREATE TABLE bad_rows AS SELECT * FROM reject_errors. In the command line client, each -c runs in the same session, which is why the queries above can see them.

A limit on the list

Reading a file that is mostly garbage should not fill memory with rejects. rejects_limit caps how many are recorded:

duckdb -box -c "SELECT count(qty) AS qty_parsed FROM read_csv('faulty.csv', store_rejects=true, rejects_limit=1, types={'qty': 'INTEGER', 'joined': 'DATE'})" -c "SELECT count(*) AS rejects_recorded FROM reject_errors"

┌────────────┐
│ qty_parsed │
├────────────┤
│ 4          │
└────────────┘
┌──────────────────┐
│ rejects_recorded │
├──────────────────┤
│ 1                │
└──────────────────┘

With rejects_limit=1, one reject was recorded, but the read carried on: count(qty) still saw 4 values. The limit only stops the recording. Note that the number of rows recorded is therefore not the number of rows dropped once you set it.

The row count depends on what you select

This is the part that surprised me. The rows that ignore_errors drops depend on which columns the query uses. These three queries read the same file with the same options:

duckdb -box -c "SELECT count(*) AS count_star FROM read_csv('faulty.csv', ignore_errors=true, types={'qty': 'INTEGER', 'joined': 'DATE'})" -c "SELECT count(qty) AS count_qty FROM read_csv('faulty.csv', ignore_errors=true, types={'qty': 'INTEGER', 'joined': 'DATE'})" -c "SELECT count(*) AS rows_returned FROM (SELECT * FROM read_csv('faulty.csv', ignore_errors=true, types={'qty': 'INTEGER', 'joined': 'DATE'}))"

┌────────────┐
│ count_star │
├────────────┤
│ 5          │
└────────────┘
┌───────────┐
│ count_qty │
├───────────┤
│ 4         │
└───────────┘
┌───────────────┐
│ rows_returned │
├───────────────┤
│ 5             │
└───────────────┘

count(*) returned 5, count(qty) returned 4, and SELECT * returned 3 rows, as shown above. The explanation that fits all three is that DuckDB converts only the columns a query needs, and a row is rejected only when a needed column fails. count(*) needs no columns, so only the structural failure, the short row, removes a row. count(qty) needs qty, so the row with x goes as well. SELECT * needs every column, so the impossible date goes too. I did not confirm this against the source; it is the reading of the results that I trust.

The practical consequence is that a row count taken with count(*) can be higher than the number of rows you would get from the same file when you select columns, and two queries on one file can disagree. When it matters, count with reject_errors rather than by subtracting. And compare with the file:

printf 'file has %s lines\n' "$(wc -l < faulty.csv)"
duckdb -list -noheader -c "SELECT 'rows kept by ignore_errors: ' || count(*) FROM read_csv('faulty.csv', ignore_errors=true, types={'qty': 'INTEGER', 'joined': 'DATE'})"

file has 7 lines
rows kept by ignore_errors: 5

The file has 7 lines, so 6 data rows, and count(*) with ignore_errors said 5. That 5 is not "five good rows".

Keep a short row with null padding

If rows that are short should be kept with NULL in the missing fields, rather than dropped, null_padding=true does that:

duckdb -box -c "SELECT * FROM read_csv('faulty.csv', null_padding=true, ignore_errors=true, types={'qty': 'INTEGER', 'joined': 'DATE'})"

┌────┬──────┬─────┬────────────┐
│ id │ name │ qty │   joined   │
├────┼──────┼─────┼────────────┤
│ 1  │ Ana  │ 3   │ 2025-01-15 │
│ 3  │ Cy   │ 5   │ NULL       │
│ 4  │ "Dee │ 6   │ 2025-01-17 │
│ 6  │ Fay  │ 8   │ 2025-01-18 │
└────┴──────┴─────┴────────────┘

Row 3 is now present with qty 5 and joined NULL. The result has four rows, 1, 3, 4 and 6, and still drops the rows that fail a conversion. Use it when a missing trailing field is a real condition of the data (an optional last column) and not when it means a line was cut off.

How to tell it worked

Compare three numbers: the lines in the file, the rows returned by SELECT * with ignore_errors, and the rows in reject_errors. For this file, 6 data rows, 3 returned and 3 rejected. If the three do not add up, some rows were dropped for a reason you have not seen, or rejects_limit stopped the recording. Read every distinct error_type and error_message; a hundred rejects with one message are one problem, and a hundred with a hundred messages are a different file. Finally, look at the rows that were kept, and not only at the ones that were rejected: "Dee passed every check.

When skipping rows is the wrong answer

Never skip rows silently in anything where a missing row changes a number someone will act on: financial totals, counts of events, anything audited. In those cases, read strictly, let it fail, and fix the file or write down why each rejected row can be ignored. Skipping is right for exploratory work, for logs where a few corrupt lines are expected, and for a first pass whose purpose is to find out how bad the file is. If the reject list is long, the file has a systematic problem such as the wrong delimiter or encoding, and the right move is to fix that, using the tools in the format rules hub, not to skip a third of the data. The same idea in PostgreSQL is in skipping bad rows during COPY.