duckdb "Conversion Error: could not convert string to INT32" null handling

TL;DR

DuckDB inferred (or was told) the column is INT32, then hit a string it cannot convert, often a null marker like "N/A" or an empty field it does not recognize. Declare the column types explicitly, tell the reader which strings mean null, or clean the values before casting. Do not just cast harder; find the offending value first.

Error

Conversion Error: could not convert string to INT32

Steps

1. Find the offending values. Read the column as text and look at what is not numeric:

SELECT DISTINCT raw_col FROM read_csv('data.csv', columns={'raw_col': 'VARCHAR'})
WHERE raw_col IS NOT NULL
LIMIT 50;

Expected output: the distinct strings, including the culprit ("N/A", "null", "", "1,000"). The error names the column; this shows you the values.

2. Tell the reader which strings mean null and set the column type explicitly:

SELECT * FROM read_csv(
  'data.csv',
  columns={'id': 'INTEGER', 'amount': 'INTEGER'},
  nullstr=['N/A', 'null', '']
);

Expected output: the read succeeds with those markers as NULL. nullstr accepts a list; add every marker your data actually uses.

3. If some values are genuinely dirty (commas, currency symbols), clean before casting instead of forcing the read:

SELECT CAST(REPLACE(raw_col, ',', '') AS INTEGER) AS amount
FROM read_csv('data.csv', columns={'raw_col': 'VARCHAR'})
WHERE raw_col NOT IN ('N/A', 'null', '');

Expected output: clean values convert, dirty markers are excluded. Cleaning in SQL keeps the pipeline reproducible.

4. Re-run the original query against the fixed read and confirm the full file loads.

Expected output: row count matches the source file, and the integer column holds numbers with NULLs where the markers were.

When to use

• readcsv or readparquet raises a string-to-INT conversion error
• A cast in a query fails on values you expected to be numeric
• The error appears on a new file but the same query worked on old files

When not to use

• The column is legitimately text (fix the schema expectation, not the data)
• The error is on a different type (dates and decimals have their own parsing rules)
• The file itself is corrupt or truncated (that is a source-data problem)

Variant phrasings

could not convert string to INT64 or DOUBLE

Same pattern, wider type. The nullstr and cleaning steps are identical; only the target type changes.

conversion error only on some files

Those files use different null markers or encodings. Normalize markers per source before the shared read.

Why it happens

DuckDB's CSV reader samples the file to infer types, then converts every value to the inferred type. One unrecognized string in an integer column, a null marker it was not told about, or a dirty value like "1,000", breaks the conversion for the whole column. Explicit types plus explicit null markers remove the guessing.

Edge cases

• Empty strings: DuckDB treats them as empty VARCHAR, not NULL, unless listed in nullstr.
• unionbyname across files with different inferred types can reintroduce the error; pin types on every read.
• try_cast returns NULL on failure instead of raising, useful for quarantine queries, but it hides dirty data, so use it for diagnosis, not as the load path.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_mRy4tvlHGiY8VYGIFNpuZg