strict-table-parser

Reads a CSV or XLSX file against a declared schema and accounts for every row it saw.

The rule the design rests on: nothing disappears quietly. Each data row ends up in exactly one of two lists — accepted or rejected — and a rejected row keeps the reasons why, located to the row and column in the source file. A parser that returns clean-looking data while the difference went nowhere cannot be audited, and the failure only surfaces weeks later when a total does not match.

from datetime import date
from src import ColumnSpec, Schema, parse_file

schema = Schema(
    columns=(
        ColumnSpec("order_id", dtype="str"),
        ColumnSpec("quantity", dtype="int", min_value=1, max_value=10_000),
        ColumnSpec("unit_price", dtype="float", min_value=0.0),
        ColumnSpec("order_date", dtype="date", max_value=date(2026, 12, 31)),
        ColumnSpec("status", dtype="str", allowed=frozenset({"open", "shipped"})),
        ColumnSpec("note", dtype="str", required=False, allow_empty=True),
    ),
    key_columns=("order_id",),
)

result = parse_file("export.xlsx", schema)

assert result.is_balanced()          # rows read == accepted + rejected
result.accepted_frame()              # clean data, types already settled
result.exception_frame()             # one line per issue, ready to hand over

Run it

pip install -r requirements.txt
python demo.py        # parses the three files in examples/
pytest -q             # 101 tests

demo.py is the quickest way to see the behaviour: one clean file, one carrying every defect the parser knows about, and one spreadsheet whose header sits on row 7.

What it refuses, and how

Structural problems raise SchemaViolation before any data is read. A file missing a required column is not partially valid — every row would be short a field, and returning them would push the failure somewhere it cannot be attributed.

Code	Raised when	Effect
MISSING_COLUMN	a required column is absent	file refused, columns named
HEADER_NOT_FOUND	no row looks like a header	file refused
TYPE_ERROR	a cell will not read as its declared type	row rejected
MISSING_VALUE	a required cell is blank	row rejected
OUT_OF_RANGE	outside bounds or not in the allowed set	row rejected
SUBTOTAL_ROW	an aggregate line among the records	row separated
DUPLICATE_KEY	a key repeats	row flagged, both kept
UNKNOWN_COLUMN	a column the schema does not describe	warning only

Every problem on a row is reported together. Stopping at the first forces the operator through repeated fix-and-rerun cycles to discover things that were known on the first pass.

Decisions worth arguing about

Header position is found, not assumed. Real exports carry a title block, a generation date and blank spacers above the table. Detection is deliberately partial: demanding a complete column match would make a file that is merely missing one column report "no header found", sending the operator to look for a header that is right there.

Blank rows keep their position. Row numbers in the report match line numbers in the source. "Row 9" opens at line 9 in the spreadsheet, which is the only thing that makes the report usable by someone who is not the author.

Zero, False and "NA" are values. Zero is the classic silent data loss: a free item priced at zero, dropped as if the cell were empty. Which strings count as blank is set per column rather than globally — in a numeric column "n/a" is an absent value, in a free-text column it is text the source chose to write, and nulling it is the same silent rewrite the reader disables.

Ambiguity is refused, not guessed. "1.234,50" could be 1234.50 or something else. Both readings are defensible, which is exactly why the parser picks neither. A value guessed correctly nine times in ten is worse than one rejected every time, because the tenth is invisible.

Duplicates are flagged, never merged. What a repeated key means — a correction, a genuine second record, a double submission — is a business decision. Both rows are preserved with their provenance, and a person decides.

Aggregate detection looks only at the key column. Scanning every cell for "total" would discard a real record whose note reads "total agreed by phone", and it would vanish without explanation.

Why pandas, configured this way

pandas does the reading, with its inference switched off:

header=None             # the header is located by the schema, not guessed
dtype=object            # no type inference; coercion is the schema's job
keep_default_na=False   # "NA", "null" and "None" stay as written
skip_blank_lines=False  # blank rows keep their position

Those four settings are the point. A default read_csv assigns the header, coerces types and drops blank lines on load — which is the guessing this parser exists to prevent — and shifts every row number in the process.

pandas then does what it is genuinely good at, on the way out: accepted_frame() and exception_frame() return the results ready for analysis or export, with no further inference needed because the types were settled by the schema.

The reading layer is about forty lines and isolated in _read_rows. Swapping it for csv and openpyxl directly, or for a streaming reader on files too large to hold in memory, changes nothing above it.

Layout

src/
  errors.py    issue codes, positions, the structural exception
  schema.py    column rules, type coercion, what counts as blank
  parser.py    header discovery, validation, the three output lists
tests/
  test_schema.py          column rules in isolation
  test_parser_rejects.py  the eight ways a file goes wrong — read this first
  test_parser_valid.py    clean data survives intact
examples/
  build_examples.py       generates the sample files

Examples are generated by a script rather than committed as binaries: a fixture should be readable in a diff, and an .xlsx is not.

On the tests

test_parser_rejects.py is the file to read. The happy path is one shape; the ways a real export goes wrong are many, and each case in that file was chosen because it silently corrupts a result when handled carelessly.

Every test that reads a file also asserts the accounting invariant. A parser can return plausible data and still be wrong if the difference went nowhere.

Two defects were found by these tests during development, both of the kind that corrupts data quietly and surfaces a month later. Both now carry regression cover with the reason recorded in the docstring:

• A file missing one column reported HEADER_NOT_FOUND instead of naming the column. True, but it points the operator at the wrong problem.
• A literal "NA" in a free-text column was turned into a null by a global blank list — the same silent rewrite disabled in the reader, reintroduced one layer down.

Requirements

Python 3.10+, pandas, openpyxl, pytest. See requirements.txt.