Validate Excel Rows with pydantic Models

Checking a sheet cell by cell with if statements works for three rules and becomes unreadable at thirty. A pydantic model turns the rules into a declaration — this column is a date, that one a positive whole number, this one one of three statuses — and validating a row against it reports every broken rule for that row, with the field name and the value that failed. Because each row is checked separately, you can map each error back to its Excel row and column and hand the person who filled in the sheet a precise list to fix. This page is part of Validating Excel Data with Python.

Prerequisites

pip install "pydantic>=2" openpyxl pandas

orders.xlsx has six orders. The first is clean; the rest between them have a bad email address, a quantity of zero, a status in the wrong case, a date with a time, a price of "n/a", an unknown status, a discount on a small order and a missing order ID.

Step 1 — Describe a valid row as a model

Each field is a column. The type says what the value must be, Field adds constraints, and alias maps the field to the Excel header, which usually contains spaces and capitals:

from datetime import date
from typing import Literal, Optional
from pydantic import BaseModel, ConfigDict, Field

class Order(BaseModel):
    model_config = ConfigDict(str_strip_whitespace=True)

    order_id: str = Field(alias="Order ID", pattern=r"^SO-\d{4}$")
    email: str = Field(alias="Customer email", pattern=r"^[^@\s]+@[^@\s]+\.[a-z]{2,}$")
    order_date: date = Field(alias="Order date")
    qty: int = Field(alias="Qty", gt=0)
    unit_price: float = Field(alias="Unit price", gt=0)
    status: Literal["open", "paid", "void"] = Field(alias="Status")
    discount_pct: Optional[float] = Field(default=None, alias="Discount %", ge=0, le=50)

print(list(Order.model_fields))

str_strip_whitespace removes stray spaces before any check runs, so " cai@example.com " passes. Literal restricts the status to three values, pattern checks the ID and email format, and Optional[...] = None makes the discount column optional while still limiting its range when filled. For proper email validation, install email-validator and use pydantic's EmailStr instead of the pattern.

Step 2 — Validate each row and keep the errors

Read the header and rows with openpyxl — empty cells arrive as None, which is what pydantic expects for a missing value — and validate one row at a time. ValidationError.errors() lists every failure for the row:

from openpyxl import load_workbook
from pydantic import ValidationError

ws = load_workbook("orders.xlsx", read_only=True)["Orders"]
rows = ws.iter_rows(values_only=True)
header = next(rows)

def validate_sheet(model, header, rows):
    valid, errors = [], []
    for excel_row, values in enumerate(rows, start=2):
        record = dict(zip(header, values))
        try:
            valid.append(model.model_validate(record))
        except ValidationError as exc:
            for err in exc.errors():
                errors.append({"row": excel_row, "column": err["loc"][0] if err["loc"] else None,
                               "value": err.get("input"), "problem": err["msg"]})
    return valid, errors

valid, errors = validate_sheet(Order, header, rows)
print(len(valid), "valid rows")
for e in errors:
    print(e["row"], e["column"], repr(e["value"]), "-", e["problem"])

Two rows pass. Row 3 fails on two fields at once — both reported, not only the first. Row 4's only error is its status, "Paid", which is not one of the lower-case choices; its quantity, the text "12", and its date, the text "2026-09-03", were accepted, because pydantic's default lax mode converts compatible values. Row 5 shows an Excel subtlety: a date field rejects a datetime with a time part, so a timestamp typed into a date column is caught. Row 7 has no order ID, and None is not a string. Numbering from 2 makes row the real Excel row, because row 1 is the header.

Step 3 — Normalise values before checking

Some values are acceptable once tidied: a status in the wrong case, a code with lower-case letters. A field_validator with mode="before" runs on the raw value before type checks, so it can clean up what the model should accept:

from pydantic import field_validator, model_validator

class Order(Order):
    @field_validator("status", mode="before")
    @classmethod
    def lower_status(cls, v):
        return v.lower() if isinstance(v, str) else v

    @model_validator(mode="after")
    def discount_needs_volume(self):
        if self.discount_pct and self.qty < 10:
            raise ValueError("a discount needs a quantity of 10 or more")
        return self

rows = load_workbook("orders.xlsx", read_only=True)["Orders"].iter_rows(values_only=True)
header = next(rows)
valid, errors = validate_sheet(Order, header, rows)
print(len(valid), "valid rows:", [o.order_id for o in valid])
for e in errors:
    print(e["row"], e["column"], repr(e["value"]), "-", e["problem"])

Row 4 now passes. The model_validator with mode="after" sees the whole validated row, so it can express rules that span columns — here, that only orders of 10 or more may carry a discount, which catches row 6. Its error has no field, so column is None; the report calls it a row-level problem. A model validator only runs when every field is valid, so a row with field errors is reported for those first.

Step 4 — Write an error report people can act on

Turn the errors into a sheet with the Excel row, the column letter and header, the value and the problem. Mapping the header back to its letter lets the reader jump straight to the cell:

import pandas as pd
from openpyxl.utils import get_column_letter

report = pd.DataFrame([{
    "cell": f"{get_column_letter(header.index(e['column']) + 1)}{e['row']}" if e["column"] else f"row {e['row']}",
    "column": e["column"] or "(whole row)",
    "value": e["value"] if e["column"] else None,
    "problem": e["problem"],
} for e in errors])
report.to_excel("orders_errors.xlsx", index=False)
print(report.to_string(index=False))

Pydantic's messages are clear enough to show as they are; replace them with your own wording by mapping on err["type"] — greater_than, literal_error, string_pattern_mismatch and so on — if the audience needs friendlier text. Highlight Invalid Cells in Excel with Python marks the same cells in a copy of the workbook.

Step 5 — Load only the valid rows

Valid rows are model objects with clean, typed values: dates are date, quantities int, text stripped and statuses lower-case. model_dump() turns them back into dicts for a DataFrame, a database insert or an API call:

clean = pd.DataFrame([o.model_dump() for o in valid])
print(clean.to_string(index=False))
print(clean.dtypes.to_dict())

The field names, not the Excel headers, become the columns — convenient for loading into a database. Pass by_alias=True to model_dump to get the original headers back.

Reading with pandas instead

If the rows come from pd.read_excel, empty cells arrive as NaN, a float — not None. An optional field then receives NaN, and a constraint such as le=50 rejects it with a confusing "less than or equal to 50" message; an unconstrained optional float would silently accept it. Replace NaN with None before validating:

df = pd.read_excel("orders.xlsx")
records = df.astype(object).where(df.notna(), None).to_dict("records")
print(df.loc[0, "Discount %"], "->", records[0]["Discount %"], "->", Order.model_validate(records[0]).discount_pct)

pandas also turns the date column into Timestamps at midnight, which pydantic accepts for a date field.

Strict or lax

Lax mode accepted "12" as the quantity 12. That is usually what you want for spreadsheet data, where numbers are often typed as text, but when a text number signals a real problem — a column of IDs imported as text that should be numeric — use Field(strict=True) on that field, or ConfigDict(strict=True) for the whole model, and the row is rejected instead.

Common pitfalls

• Stopping at the first error. Validate row by row and collect exc.errors(); each lists every failed field.
• Losing the Excel row number. Start the row counter at 2 so it matches the sheet.
• Passing pandas NaN. Convert to None first, or read with openpyxl.
• Datetimes in date fields. A time part fails date; decide whether that is an error or truncate it in a before-validator.
• Cross-column rules hidden by field errors. Model validators run only when fields pass; fix field errors first.

Performance notes

pydantic v2 validates tens of thousands of rows a second. For very large sheets, validate with TypeAdapter(list[Order]) in one call when you only need the valid/invalid split, or use a column-based tool such as pandera, which checks whole columns at once.

Conclusion

Describe a valid row as a pydantic model: types for kinds of value, Field for ranges and patterns, Literal for choices, aliases for Excel headers, before-validators to tidy raw values and a model validator for rules across columns. Read rows with openpyxl so blanks are None, validate each row, and collect every error with its Excel row and column into a report. Load only the valid rows, using model_dump() for clean, typed records.

Frequently asked questions

Why use pydantic to validate Excel data? A pydantic model states the rules for a row in one place — types, ranges, allowed values, patterns and rules across columns — and reports every broken rule for a row at once, with the field and the offending value. That makes a precise error report straightforward.

How do I map Excel headers with spaces to pydantic fields? Give each field an alias matching the header, such as order_id: str = Field(alias="Order ID"), and validate a dict built from the header row and the data row with Model.model_validate(record).

Why does pydantic reject an empty Excel cell read with pandas? pandas turns empty cells into NaN, which is a float, not None. An optional field receives NaN instead of None, and constraints such as le=50 fail on it. Read rows with openpyxl, where empty cells are None, or replace NaN with None before validating.

Does pydantic convert "12" to 12? Yes, by default pydantic coerces compatible values — the text "12" becomes the integer 12. Set strict=True on a field or in the model config to reject text where a number is required.

Related

• Up one level: Validating Excel Data with Python — every way to check a sheet.
• Validate Excel Data with pandera Schemas — column-based validation.
• Validate Excel Columns Before Import with pandas — header and type checks.
• Validate an Uploaded Excel File in a Web App — the same checks behind an upload form.