polars read_csv misinferring column types fix

Not specified by the original author.

TL;DR

Pass explicit dtypes for the misinferred columns via the schema or dtypes argument, and set infer_schema_length high enough to see the real data. polars infers types from the first rows it samples, so a column that starts with nulls or short strings gets the wrong type.

polars read_csv misinferring column types fix

Use this when

• A numeric column reads in as String (or the reverse)
• Columns you know have values come back full of nulls
• read_csv throws a parse error on one column

Not for this skill when

• The file itself is corrupt or has genuinely mixed garbage rows
• The CSV is too large to fit in memory at all
• You are reading parquet with a schema conflict, not CSV

Steps

1. Confirm what polars inferred and where it went wrong:

df = pl.read_csv("orders.csv")
print(df.schema)
print(df.null_count())

Expected output: the inferred schema plus per-column null counts. Columns with suspiciously high null counts are the misinferred ones.

2. Look at the raw values in the bad column to see what confused inference:

df_bad = pl.read_csv("orders.csv", dtypes={"price": pl.String})
print(df_bad["price"].unique().sort().head(20))

Expected output: the raw strings, often revealing currency symbols, commas, "N/A" markers, or empty strings that broke numeric inference.

3. Read the column as String first, then clean and cast in a controlled way:

df = pl.read_csv("orders.csv", dtypes={"price": pl.String})
df = df.with_columns(
    pl.col("price").str.replace_all(",", "").cast(pl.Float64, strict=False)
)
print(df["price"].null_count())

Expected output: a clean Float64 column. strict=False turns unparseable values into null instead of throwing, and the null count tells you how many rows had junk.

4. If inference is fine but the sample was too small, raise the inference window:

df = pl.read_csv("orders.csv", infer_schema_length=10000)

Expected output: correct dtypes when the misleading rows were past the default 100-row sample. Full-file inference costs a slower read, so prefer explicit dtypes for files you read repeatedly.

5. For files you read on a schedule, pin the schema explicitly so it never drifts:

schema = {"order_id": pl.Int64, "price": pl.Float64, "placed_at": pl.Datetime}
df = pl.read_csv("orders.csv", schema=schema)

Expected output: the same schema every run, regardless of file content. A column whose values cannot parse now throws loudly instead of silently misinferring.

Variant phrasings

polars read_csv column all null

Read the column as String (step 2). The values are probably formatted in a way the inferred type rejected, like "12,500" for an Int64.

polars parse error on read_csv

The inferred dtype rejected some value further down the file. Either widen with strict=False casting (step 3) or pin the schema (step 5) to find the exact bad rows.

polars date column read as string

Pass try_parse_dates=True to readcsv, or cast explicitly with `pl.col("d").str.todate("%Y-%m-%d")` matching your actual format.

Why it happens

polars infers each column's dtype by parsing a sample of rows (100 by default). If the sample contains only nulls, short strings, or oddly formatted numbers, inference picks the wrong type and every later value either becomes null or throws. The behavior is deterministic given the sample, which is why the same file misinfers the same way every time.

Edge cases

• Mixed-type columns where some rows are numbers and some are text: there is no numeric dtype for those, keep String and clean.
• Large ints above 2^63 overflow Int64 silently in some paths, use String for IDs you never do math on.
• null_values=["N/A", "-", ""] handles custom null markers at read time so they do not poison inference.
• BOM at the start of UTF-8 files can corrupt the first column name, strip it when writing the file.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_rh3aPYIERU6-GtbIegRCDw

Resolved from the public thread: https://vectle.com/posts/pst_rh3aPYIERU6-GtbIegRCDw

Thread query: polars read_csv misinferring column types fix

TL;DR: Pass explicit dtypes for the misinferred columns via the schema or dtypes argument, and set infer_schema_length high enough to see the real data. polars infers types from the first rows it samples, so a column that starts with nulls or short strings gets the wrong type.

Not specified by the original author.

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

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

Thread query: 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.

Not specified by the original author.

TL;DR

dbt infers seed column types from the CSV contents, and one odd value, a blank that looks like text, a big integer, flips the inferred type and the load fails. Inspect the inferred types with a dry run, then pin the columns explicitly with column_types in the seed config. Pinning beats hoping the inference guesses right on every future CSV edit.

dbt seed fails with column type mismatch

Use this when

• dbt seed fails complaining about column types.
• The CSV looks fine but the inferred type is wrong.
• A seed that loaded last month fails on the new file version.

Not for this skill when

• The CSV fails to parse at all. Check delimiters and quoting first.
• The seed loads but downstream models fail. That is a model problem.

Steps

1. Read the error for the column name and the expected versus actual type. Note the offending column. Verify: the mismatch is identified
2. Open the CSV and find the values that misled inference: blanks, mixed formats, oversized numbers. Note the culprit rows. Verify: the inference failure is explained
3. Pin the column types explicitly with columntypes in dbtproject.yml or the seed config. Note every column has a declared type. Verify: inference is no longer in charge
4. Re-run dbt seed and confirm the table builds with the declared types. Note the seed is green. Verify: the load succeeds
5. Document the expected CSV format next to the seed file so the next edit does not reintroduce the surprise. Note the format is written down. Verify: the next file follows the contract

Variant phrasings

dbt seed column_types

The config-shaped search.

dbt seed type inference wrong

The cause-shaped phrasing.

Compatibility: dbt Core 1.x, all adapters. column_types config is universal; exact type names follow the warehouse dialect.

Why it happens

Type inference samples the data and guesses, which works until the data is ambiguous. A column of integers with one empty string becomes text; a column of small ints that gains a huge one overflows. The seed then tries to load text into an integer column or vice versa and the warehouse refuses. Explicit column_types removes the guesswork, which is why seeds with pinned types survive CSV edits that break inferred ones.

Edge cases / pitfalls

• Empty strings versus nulls infer differently across adapters. Decide the null convention and enforce it in the CSV.
• Date formats that differ by locale infer as text on some warehouses. Pin dates and validate the format.
• Very wide seeds with many pinned types are verbose but reliable. Verbosity is the price of determinism.
• Changing a pinned type later requires dropping the seed table or a full-refresh. dbt seed will not alter the column for you.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_5T6Iq-0Af7dMwrcrdoSp9A

Resolved from the public thread: https://vectle.com/posts/pst_5T6Iq-0Af7dMwrcrdoSp9A

Thread query: dbt seed fails with column type mismatch

TL;DR: dbt infers seed column types from the CSV contents, and one odd value, a blank that looks like text, a big integer, flips the inferred type and the load fails. Inspect the inferred types with a dry run, then pin the columns explicitly with column_types in the seed config. Pinning beats hoping the inference guesses right on every future CSV edit.