Python Excel AutomationPractical guides for developers
Fix
Advanced Data Transformation And CleaningDeep dive

Fix Schema and Type Errors When Reading Excel with Polars

Polars reads Excel fast, but its type inference can turn mismatched cells into nulls without a word. This page shows how a code like A-77 or a text date silently disappears, how infer_schema_length and schema_overrides change that, how to read raw text and cast with a report of every failure, and how to handle blank and duplicate headers, empty sheets and mixed date formats.
Polars & ArrowReading Excel files

Reading a workbook with pl.read_excel is quick and usually just works — which is exactly why its one dangerous behaviour goes unnoticed. Polars, through the calamine engine, decides each column's type from the first rows and then converts every cell to that type. A cell that does not fit — a product code A-77 in a column of numbers, n/a in an amount column, a date typed as text — is not reported as an error. It becomes null, and the row loses its value silently. This page reproduces that with a small but realistic sheet, shows how to detect it, and fixes it properly: control the inference, read awkward columns as text, cast them yourself with a list of everything that failed, and deal with blank and duplicate headers and empty sheets along the way. It belongs to Reading Excel with Polars and Arrow.

Prerequisites

Bash
pip install polars fastexcel

mixed.xlsx has 150 well-behaved rows followed by one awkward row: a text product code, the text n/a in the Amount column, a date typed as text in day/month/year order, and a fractional quantity. Its header has a blank column name and a duplicated Qty. empty.xlsx contains a sheet with nothing on it. The examples were run with Polars 1.44 and the default calamine engine.

Step 1 — Reproduce the silent nulls

Read the sheet with defaults and look at the schema and the last row:

Python
import polars as pl

df = pl.read_excel("mixed.xlsx")
print(df.schema)
print(df.tail(2))
print("nulls per column:", df.null_count().row(0, named=True))

The read succeeds without a warning. Polars inferred Code as Int64, Amount as Float64 and Date as Date from the first rows — and in the last row all three are null. A-77, n/a and 31/05/2026 were simply dropped because they did not fit. The fractional 3.5 survived because Qty was inferred as a float. Nothing in the output says three values were lost; only counting nulls reveals it, and only if you know there should not be any. In a large file, this is how a handful of rows quietly fall out of a total.

What the default read does to an awkward row The last row holds the code A-77, the text n/a, the text date 31/05/2026 and the quantity 3.5. Columns were inferred from the first rows as integer, float and date, so the code, the amount and the date become null without any warning. The quantity survives because the column was inferred as a float. A B C D 1 Code Amount Date Qty 2 A-77 n/a 31/05/2026 3.5 Code Amount Date Qty 3.5 in the sheet in the DataFrame inferred types

Step 2 — Detect silent losses on every read

The reliable way to catch this is to read the same columns twice — once as plain text, once with the types you want — and compare where values exist:

Python
raw = pl.read_excel("mixed.xlsx", infer_schema_length=0)       # every column as String
print({c: str(t) for c, t in raw.schema.items()})

def lost_values(raw, typed):
    report = []
    for col in typed.columns:
        mask = raw[col].is_not_null() & typed[col].is_null()
        for idx in mask.arg_true().to_list():
            report.append({"row": idx + 2, "column": col, "value": raw[col][idx]})
    return pl.DataFrame(report)

print(lost_values(raw, df))

infer_schema_length=0 tells Polars not to infer at all and to read every column as String, so nothing can be lost. Any cell that has text in the raw read but is null in the typed read was dropped by the conversion; the report lists its Excel row (data rows start at row 2) and the original value. This check costs a second read, which with calamine is cheap, and it turns a silent loss into a list someone can act on. Running it in the pipeline's tests or as a guard before loading data elsewhere is a good habit.

Step 3 — Control the inference

Two parameters change how types are chosen. infer_schema_length sets how many rows Polars samples; None means all of them. schema_overrides fixes the type of named columns:

Python
whole = pl.read_excel("mixed.xlsx", infer_schema_length=None)
print("infer from all rows:", {c: str(t) for c, t in whole.schema.items() if c in ("Code", "Amount", "Date")})

pinned = pl.read_excel("mixed.xlsx", schema_overrides={"Code": pl.String, "Amount": pl.String, "Date": pl.String})
print(pinned.select("Code", "Amount", "Date").tail(2))

Inferring from every row makes Code and Date strings — the presence of A-77 and the text date means no stricter type fits — but Amount still comes out as Float64 with n/a as null, because one text value among 150 numbers is treated as not fitting rather than as a reason to give up on numbers. Pinning the awkward columns to String keeps every value as written; note that real dates read as text arrive in the 2026-05-30 00:00:00 form, while the typed-in date keeps its 31/05/2026 form. Neither parameter makes the data clean; they decide whether problems are dropped or kept for you to deal with. Keeping them is almost always the right choice.

Step 4 — Cast yourself, and report what fails

With the awkward columns read as text, cast them explicitly with strict=False and collect the values that could not be converted:

Python
clean = pinned.with_columns(
    pl.col("Code").str.strip_chars().alias("Code"),
    pl.col("Amount").cast(pl.Float64, strict=False).alias("Amount num"),
    pl.coalesce(
        pl.col("Date").str.to_date("%Y-%m-%d %H:%M:%S", strict=False),
        pl.col("Date").str.to_date("%d/%m/%Y", strict=False),
    ).alias("Date parsed"),
)
failed = clean.filter(
    (pl.col("Amount").is_not_null() & pl.col("Amount num").is_null())
    | (pl.col("Date").is_not_null() & pl.col("Date parsed").is_null())
).select("Code", "Amount", "Date")
print(clean.select("Code", "Amount num", "Date parsed").tail(2))
print("could not convert:", failed.to_dicts())

cast(strict=False) turns n/a into null knowingly — and because the original text is still in the Amount column, the failure can be listed. Dates are parsed with each known format in turn and pl.coalesce takes the first that works, so both the real Excel dates (which arrived as 2026-05-30 00:00:00) and the typed 31/05/2026 end up as proper dates. The failed frame is the honest output: one row whose amount was n/a, for a person to decide about. Codes stay text, which is what they are — a code is never a number, even when it looks like one. Placeholder text such as n/a is discussed in Replace Placeholder Text with NaN in pandas.

A read that never loses a value silently Read awkward columns as text with schema overrides, cast them explicitly with strict set to false, try each known date format and keep the first that parses, then list every value that failed to convert for review. Read as text schema_overrides → String Cast explicitly strict=False Dates coalesce of formats Report failures original text kept

Step 5 — Blank and duplicate headers, and empty sheets

Headers cause their own surprises. Look at the column names Polars produced, and at what an empty sheet does:

Python
print(df.columns)
renamed = df.rename({"__UNNAMED__4": "Flag", "Qty_1": "Qty returned"})
print(renamed.columns)

try:
    pl.read_excel("empty.xlsx")
except Exception as e:
    print(type(e).__name__, "-", str(e).splitlines()[0])
print(pl.read_excel("empty.xlsx", raise_if_empty=False).shape)

A blank header becomes __UNNAMED__4 (the number is the column position) and a repeated Qty becomes Qty_1. Neither is an error, but code that selects "Qty" silently gets the first of the two, so rename deliberately right after reading — and if the positions can change between exports, find columns by header text rather than position. An empty sheet raises NoDataError; raise_if_empty=False returns an empty frame instead, which suits batch jobs where an empty export is normal and should simply contribute no rows. Duplicate headers in pandas are covered in Fix Duplicate Column Names When Reading Excel.

Header and sheet surprises and how Polars reports them A blank header becomes a column named __UNNAMED__ plus its position. A repeated header gets a suffix such as _1. An empty sheet raises NoDataError unless raise_if_empty is false. A cell that does not fit the inferred type becomes null without any message. what you get what to do blank header __UNNAMED__4 rename right after reading duplicate header Qty, Qty_1 rename deliberately empty sheet NoDataError raise_if_empty=False misfit cell silent null read as String, cast, report

Making it routine

For a recurring import, write the expected schema down once — column names, the type each should end up as, and which columns are read as text first — and keep it next to the code as a dictionary. The reader then pins every column with schema_overrides, casts with a failure report, and fails the job if a required column is missing or the failure list is longer than an agreed threshold. That turns a silent data-quality problem into a visible, reviewable one, and when an export changes shape the job says so on the first run instead of producing quietly wrong totals for months. The same contract can be checked in pandas with a schema library, as in Validate Excel Data with pandera Schemas.

Common pitfalls

  • Trusting a clean-looking read. Mismatched cells become nulls without a warning; compare with a text read.
  • Assuming infer_schema_length=None fixes everything. It changes some types but still nulls out odd values.
  • Casting codes to numbers. Codes are text; keep them as strings.
  • Selecting a duplicated header by name. You get the first one; rename after reading.
  • One date format. Try each known format and coalesce.

Conclusion

Polars' read_excel infers each column's type from the first rows and turns cells that do not fit into nulls without telling you. Detect it by comparing a typed read with an all-text read (infer_schema_length=0); prevent it by pinning awkward columns to String with schema_overrides, then casting explicitly with strict=False and listing every value that failed. Rename blank and duplicate headers straight after reading, and use raise_if_empty=False where empty sheets are expected.

Frequently asked questions

Why are some values null after reading Excel with Polars? Polars infers each column's type from the first rows, and cells that do not fit that type, such as text in a numeric column, are converted to null without an error.

How do I read every Excel column as text in Polars? Pass infer_schema_length=0 to read_excel, which reads all columns as String, or set specific columns to pl.String with schema_overrides.

How do I find which values Polars could not convert? Read the sheet once as text and once with types, then list cells that are not null in the text read but null in the typed read.

How do I stop Polars failing on an empty Excel sheet? Pass raise_if_empty=False to read_excel to get an empty DataFrame instead of a NoDataError.

More on polars & arrow

More on polars & arrow →