Building a multi-format document parser without losing your mind

Some problems look simple until you see the data.

"We receive commission reports from our suppliers each month and enter them into a spreadsheet. Can you automate that?"

Sure. How many suppliers?

"Sixty-five."

And they all send the same format?

Long pause.

Why can't you just parse the file?

Here's what sixty-five suppliers actually means in practice.

Supplier A sends a clean CSV with sensible column headers. Supplier B sends an Excel file where the real data starts on row 7, because rows one to six are a logo and some blurb. Supplier C sends a PDF. Supplier D sends an Excel file with the data split across three sheets and a summary sheet that doesn't quite reconcile with the detail. Supplier E changed their format at some point without telling anyone.

Then it gets interesting. Column headers that mean the same thing across suppliers: "Net", "Net Amount", "Net Sales", "Net Value", "NET", "Amount (Net)", "Sales Net of VAT". Dates written as 31/03/2026, 2026-03-31, Mar-26, 31.03.26 and — memorably — an Excel serial number that had been text-formatted at some point and no longer converted cleanly.

Currency mixed in the same file. Negative values written as -100, (100) and 100 CR. Product codes with and without leading zeros. Rows that are subtotals mixed in with rows that are line items, distinguishable only by whether a particular cell is bold.

You cannot write a parser for this. You can write sixty-five parsers, and then you have sixty-five things that break independently.

How do you build one engine for sixty-five formats?

The approach that holds up is configuration-driven, multi-stage processing. Rather than one parser per supplier, you build one engine and configure it per supplier. The stages run in order on every file.

Stage 1 — ingestion and normalisation

Whatever comes in — CSV, XLS, XLSX, PDF — gets converted to a common intermediate representation: a grid of cells with type hints and formatting metadata preserved.

That last part matters more than you'd expect. Bold formatting is often the only signal distinguishing a subtotal row from a data row. Cell background colours sometimes indicate status. Merged cells usually mean a header spanning columns. Throwing away formatting during ingestion loses information you'll need later.

Stage 2 — source identification

Before you can parse a file, you need to know who sent it. Filenames are unreliable — people rename things. Email sender addresses help but break when someone forwards from a personal account.

What works is multi-signal identification: check for known markers in the file (a company name in a header cell, a specific column signature, a document reference format), combine them, and score the confidence. If confidence is below threshold, flag the file for human review rather than guessing.

Getting this wrong is expensive — parsing a file with the wrong supplier's configuration produces data that looks plausible and is entirely wrong.

Stage 3 — structure detection

Where does the data actually start? Which row holds the headers? Are there multiple tables in the sheet?

Heuristics that work: look for the first row where a majority of cells are non-empty and the row below has consistent types. Look for a row whose values match expected header synonyms. Look for the transition from sparse formatting to dense data.

Store the answer in the supplier's configuration once detected, but re-verify on each file rather than assuming it hasn't moved.

Stage 4 — column mapping

This is where a language model genuinely earns its place.

You have a set of canonical fields you need — order reference, date, product code, quantity, net amount, commission rate, commission value. You have a set of column headers from the file that might be any of the variants above.

Rule-based synonym matching handles most of it. Build a dictionary per canonical field, so that "net amount" matches "net", "net sales", "net value", "amount net", "sales net of VAT" and the rest. Normalise case and whitespace, strip punctuation, then match.

For the cases synonym matching misses — a header you've never seen, or an ambiguous one — send the header row plus a few sample data rows to a language model with the canonical field list and ask it to map them. The sample rows matter enormously; a column headed "Value" is ambiguous, but a column headed "Value" containing 1250.00, 890.50 and 2100.00 alongside another column headed "Qty" containing 5, 2 and 8 is not.

Cache the resulting mapping in the supplier's configuration. You only pay for the model call once per new format, not once per file.

Stage 5 — row classification

Not every row is a data row. You have headers, subtotals, section breaks, blank spacers and footnotes.

Signals that classify reliably: does the row have values in the columns you'd expect for a data row? Does it have a value in the identifier column? Is it formatted differently from its neighbours? Does its numeric value equal the sum of the rows above it (that's a subtotal)?

That last check is the strongest signal and it's cheap to compute.

Stage 6 — value normalisation

Every field type needs its own normaliser.

• Dates. Try a sequence of known formats, prefer the supplier's configured format first, and fall back to fuzzy parsing. Reject anything that parses to an implausible date — if you get 2019 in a 2026 file, something is wrong.
• Currency. Strip symbols and thousands separators, handle bracket negatives and CR/DR suffixes, and detect the currency from a symbol or a configured default.
• Numbers. Handle comma and point decimal separators, which vary by locale and sometimes vary within one file if it has been through multiple hands.
• Identifiers. Preserve leading zeros. Store them as strings, never as numbers. This one catches everyone at least once.

Stage 7 — per-row validation

Every row gets checked before it's accepted.

The most valuable check is arithmetic: if you have quantity, unit price and line total, verify that quantity × unit price equals line total within a tolerance for rounding. If it doesn't, either your column mapping is wrong or the source data is wrong. Either way you want to know.

Similarly, if commission value should equal net amount × commission rate, check it. These checks catch mapping errors that would otherwise sail through and corrupt your data silently.

Rows that fail validation get flagged, not dropped. A human decides what to do.

Stage 8 — reconciliation

If the file contains a total — and most do — sum your parsed rows and compare. A mismatch means you've missed rows, double-counted, or misclassified a subtotal as data.

This is the single most valuable check in the whole pipeline, because it catches whole categories of error at once. If the total reconciles, you're probably fine. If it doesn't, something specific is wrong and you know to look.

Stage 9 — format drift detection

Suppliers change their formats. They don't tell you.

Fingerprint each file: column count, header text hash, structure signature. Compare it to the last known good fingerprint for that supplier. If it's changed, flag it before processing rather than after.

Format drift caught proactively is a five-minute configuration update. Format drift caught three months later via a discrepancy in the accounts is a very different conversation.

Where does an LLM fit (and where doesn't it)?

The temptation is to throw the whole file at a language model and ask for structured JSON. Don't. The naive approach has four problems.

• Cost. It scales with volume — you pay per file, forever, rather than once per format.
• Non-determinism. The same file can produce different results across runs.
• Context limits. Large files simply don't fit.
• No audit trail. Nothing explains why a particular value ended up where it did. When a client asks why a row shows £1,250, "the model said so" is not an answer.

Where language models work well is one-off interpretation tasks with small inputs and cacheable outputs. Column mapping is the perfect example — you're sending a header row and a few sample rows, getting back a mapping, and storing it. Bounded input, bounded cost, and the output is reviewable by a human before it goes into production.

Deterministic code does the actual data processing. The model helps configure the deterministic code. That division keeps costs predictable and results auditable.

What else keeps a system like this maintainable?

• Store the raw file forever. Disk is cheap. When someone queries a figure from eight months ago, you want to re-run the parse against the original rather than trusting your processed output.
• Version the configurations. When a supplier's format changes and the mapping is updated, keep the old version. Historical files need to parse with the configuration that was current when they arrived.
• Make the review queue good early. Every parse produces some exceptions. If the interface for reviewing them is painful, people stop reviewing and start ignoring, and then data quality quietly degrades. Build a decent review screen before you build clever features.
• Log the decision chain. For every parsed value, record how it got there — which column it came from, which normaliser ran, whether validation passed. When something looks wrong six months later, this is what lets you diagnose it in minutes rather than hours.

Is it worth it?

We're building exactly this pipeline at the moment. For sixty-five suppliers producing monthly reports, the manual process is several days of work each month, with the attendant transcription errors and the delay before anyone can see the numbers.

The pipeline is designed to process the same volume in minutes, flag the handful of rows that need human judgement, and reconcile every file against its own totals. Just as importantly, the data becomes available immediately rather than a week later, which changes what you can do with it.

The build is substantial — considerably more than the "just parse the file" framing suggests. But the alternative isn't a simpler system. It's sixty-five fragile scripts and someone maintaining them forever.

If you're facing a similar problem: build one engine, configure it per source, validate everything, reconcile against totals, and keep a human in the loop for exceptions. The complexity is real, but it's manageable if you structure it properly.

Frequently asked questions

Can't you just send the whole file to an AI model and ask for the data?

Not for production data. Cost scales with volume, results vary between runs, large files exceed context limits, and there's no audit trail explaining why a value ended up where it did. Language models earn their place on small, one-off interpretation tasks — such as mapping unfamiliar column headers — where the output is cached and reviewed by a human before use.

What is the single most valuable check in a document-parsing pipeline?

Reconciliation. If the file contains a total, sum the parsed rows and compare. A match means the parse is probably sound; a mismatch tells you rows were missed, double-counted or a subtotal was misclassified as data — one cheap check that catches whole categories of error at once.

How do you catch a supplier changing their format without telling you?

Fingerprint every file — column count, a hash of the header text, a structure signature — and compare it with the last known good fingerprint for that supplier. Any change is flagged before processing, which turns format drift into a five-minute configuration update rather than a discrepancy discovered in the accounts months later.

Why not just write one parser per supplier?

Because you end up with sixty-five fragile scripts that break independently, and someone maintaining them forever. One configuration-driven engine keeps the hard logic in a single well-tested place, and reduces each new supplier — or each format change — to a configuration update.

If your team retypes supplier reports into a spreadsheet every month, that work can almost certainly be automated — properly, with validation, reconciliation and a human in the loop. We build systems like this for UK small businesses on a proven open-source ERP core. Book a free 30-minute discovery call, bring one of your ugliest files, and we'll tell you honestly whether it's worth automating.