Reading erroneous CSV files is possible by utilizing the ignore_errors option. With that option set, rows containing data that would otherwise cause the CSV Parser to generate an error will be ignored.

Using the ignore_errors Option

For example, consider the following CSV file, faulty.csv:

Pedro,31
Oogie Boogie, three

If you read the CSV file, specifying that the first column is a VARCHAR and the second column is an INTEGER, loading the file would fail, as the string three cannot be converted to an INTEGER.

For example, the following query will throw a casting error.

FROM read_csv('faulty.csv', columns = {'name': 'VARCHAR', 'age': 'INTEGER'});

However, with ignore_errors set, the second row of the file is skipped, outputting only the complete first row. For example:

FROM read_csv(
    'faulty.csv',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'},
    ignore_errors = true
);

Outputs:

One should note that the CSV Parser is affected by the projection pushdown optimization. Hence, if we were to select only the name column, both rows would be considered valid, as the casting error on the age would never occur. For example:

SELECT name
FROM read_csv('faulty.csv', columns = {'name': 'VARCHAR', 'age': 'INTEGER'});

Outputs:

Retrieving Faulty CSV Lines

Being able to read faulty CSV files is important, but for many data cleaning operations, it is also necessary to know exactly which lines are corrupted and what errors the parser discovered on them. For scenarios like these, it is possible to use DuckDB’s CSV Rejects Table feature. It is important to note that the Rejects Table can only be used when ignore_errors is set, and currently, only stores casting errors and does not save errors when the number of columns differ.

The CSV Rejects Table returns the following information:

Parameters

The parameters listed below are used in the read_csv function to configure the CSV Rejects Table.

To store the information of the faulty CSV lines in a rejects table, the user must simply provide the rejects table name in therejects_table option. For example:

FROM read_csv(
    'faulty.csv',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'},
    rejects_table = 'rejects_table',
    ignore_errors = true
);

You can then query the rejects_table table, to retrieve information about the rejected tuples. For example:

FROM rejects_table;

Outputs:

Additionally, the name column could also be provided as a primary key via the rejects_recovery_columns option to provide more information over the faulty lines. For example:

FROM read_csv(
    'faulty.csv',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'},
    rejects_table = 'rejects_table',
    rejects_recovery_columns = '[name]',
    ignore_errors = true
);

Reading from the rejects_table will return: