Schema-driven CSV/Excel import for Python, powered by Pydantic.
Every backend eventually needs a "let the user upload a spreadsheet" feature. The parsing part is a solved problem. The part that always gets hand-rolled badly is: matching messy real-world headers to your actual fields, coercing types safely, catching duplicate records, and reporting which row and column went wrong without a single bad row taking down the whole import.
tabmodel is that missing layer, built on the schema library Python
developers already reach for.
from typing import Annotated
from pydantic import BaseModel, EmailStr
from tabmodel import Aliases, import_file
class Customer(BaseModel):
name: Annotated[str, Aliases("Full Name", "Customer Name")]
email: Annotated[EmailStr, Aliases("Email", "E-mail", "email address")]
age: int | None = None
result = import_file("customers.csv", schema=Customer)
result.valid_rows # list[Customer] - only the rows that validated
result.errors # list[RowError] - row number, field, message
result.duplicates # list[DuplicateGroup] - if you passed unique=
result.column_mapping # {"Full Name": "name", "Email": "email", ...}
result.unmapped_columns # source columns nothing matched
result.unmatched_fields # fields nothing in the file matchedA row with a malformed email doesn't crash the import - it shows up as
RowError(row_number=7, field='email', message='value is not a valid email address') in result.errors, while every other row still comes
through in result.valid_rows. (EmailStr needs the pydantic[email]
extra; a plain str field works too if you'd rather not add it.)
A messy header like "Cust. Name " or "customer_name" still resolves
to name automatically - no manual header-normalization code required.
pip install tabmodel # csv / tsv
pip install tabmodel[xlsx] # adds .xlsx support (read and write)Real apps rarely import from a path on disk - they import from whatever a
web framework handed them. import_file accepts a path, an open file-like
object, or raw bytes:
from tabmodel import import_file
# FastAPI: upload.file is a SpooledTemporaryFile, upload.filename is separate
result = import_file(upload.file, schema=Customer, filename=upload.filename)
# no filename at all available - say so explicitly
result = import_file(raw_bytes, schema=Customer, format="xlsx")Automatic matching handles almost everything, but sometimes a header is
too far removed from the field name or its aliases to guess safely - a
column literally called "Contact" that should map to email, say.
Override just that one column and let everything else still get matched
automatically:
result = import_file(
"customers.csv",
schema=Customer,
mapping={"Contact": "email"}, # always honored
)Real upload flows usually want a "does this mapping look right?" step
before processing a potentially huge file. preview_file reads only the
first few rows - regardless of how large the file actually is:
from tabmodel import preview_file
preview = preview_file("customers.csv", schema=Customer, sample_size=5)
preview.column_mapping # what we detected
preview.sample_rows # [(2, Customer(...)), (3, [RowError(...)]), ...]Show the user preview.column_mapping to confirm, let them override with
mapping= if something's off, then call import_file for the real run.
Pass unique= to flag rows sharing a value in a field that should be
one-of-a-kind. Duplicates are reported, not silently dropped - you decide
the policy. A typo'd field name raises immediately, before any file I/O,
rather than silently checking nothing:
result = import_file("customers.csv", schema=Customer, unique=["email"])
for group in result.duplicates:
print(group) # "duplicate email='a@x.com' at rows 4, 9, 15"ImportResult.to_dict() gives you a plain, JSON-serializable summary -
handy for handing straight back from a FastAPI/Flask endpoint. It
deliberately excludes valid_rows (those are Pydantic models - use
[row.model_dump() for row in result.valid_rows], your own natural way
to serialize them) and focuses on the part that's awkward to serialize by
hand:
import json
json.dumps(result.to_dict())
# {"success_count": 42, "error_count": 1, "ok": false,
# "errors": [{"row": 7, "field": "email", "message": "..."}],
# "duplicates": [...], "column_mapping": {...}, ...}from tabmodel import export_file
export_file("cleaned_customers.csv", result.valid_rows, Customer)
export_file("cleaned_customers.xlsx", result.valid_rows, Customer)from tabmodel import iter_import
for row_number, row in iter_import("huge_export.csv", schema=Customer):
if isinstance(row, list): # list[RowError] for this row
log_errors(row_number, row)
else:
save(row) # a validated Customer instance(unique= duplicate detection isn't available in the streaming form - it
needs to see every row before it can report anything. Use import_file.)
Validate a file in CI without writing any glue code:
pip install tabmodel
tabmodel validate customers.csv --schema myapp.models:Customer --unique emailExits 0 if every row validated cleanly, 1 otherwise - drop it into a
pipeline step as-is.
dataclass-csv (and its
Pydantic fork, pydantic-csv) solve a related but narrower problem, and
are worth knowing about. The differences that matter most:
- Mapping is manual, one column at a time, via
reader.map('First Name').to('firstname'). There's no way to declare "here are the variants I expect" once and have the rest guessed.Aliases(...)does that for every field up front - andmapping=is still there as an escape hatch for the rare column neither can guess. - A bad row raises and halts the read.
CsvValueErroris thrown on the first invalid row, so one malformed line stops the entire import.tabmodelnever raises for bad data - it collects every error and still returns every row that did validate. - No duplicate detection, no preview step, no CLI, no unified
.xlsx. XLSX support for the pydantic fork lives in a separate, differently designed, alpha-stage package.tabmodelhandles CSV, TSV, and XLSX - reading and writing - through one API and one error-reporting shape.
csvmodel is a CLI linter
for hand-edited CSV files (think flake8, but for data), which validates
against a Pydantic model or JSON Schema - closer in spirit to
tabmodel's own CLI. But it requires the header to match the field name
exactly (no fuzzy matching or aliases at all), is CSV-only, has no
duplicate detection, no export, and isn't meant to be imported as a
library for powering an app's upload feature - it's a standalone
validator for files you edit by hand, not a pip install-and-build-on-it
toolkit.
- Pydantic-native. Your import schema is a Pydantic model. No new
schema language to learn, and every validator,
Field(...)constraint, and custom type you already know how to write works unchanged. - Fuzzy header matching out of the box. Real spreadsheets never send
header names in the format you asked for.
Aliases(...)lets you list the variants you expect, and unlisted-but-close variants still match. - A bad row never aborts the import. Validation errors are collected per row, with the row number and field name attached, instead of raising on the first bad cell.
- Streams large files.
iter_import()yields row-by-row without buffering the whole file, so a 500k-row export behaves the same as a 50-row one. - Zero required dependencies beyond Pydantic.
.csvand.tsvuse only the standard library..xlsxsupport is an optional extra so you aren't forced to installopenpyxlif you don't need it.
Each field's acceptable header names are its Python name plus anything
passed to Aliases(...). Source headers are normalized (lowercased,
punctuation collapsed to spaces) and matched against every candidate
using a similarity score that combines character-level similarity (catches
typos) with word-level containment (catches descriptive variants like
"email address" matching email); matches are resolved greedily from
the best score down, so exact matches always win first and no column or
field is claimed twice. Tune the acceptance threshold with threshold=
(default 0.6) if your headers are especially terse or especially noisy,
or bypass matching entirely for a specific column with mapping=.
MIT