Parsing 16 shipper load formats with header mapping, count expansion and ditto marks

A broker agency's shippers don't share a format. At SmartLoads' first agency, 16 automated feeds arrive as plain-text email lists, HTML tables pasted into emails, Excel attachments, PDFs with and without a text layer, Google Sheets that shippers edit all day, and TMS CSV exports.

We made one early decision that shapes everything below: one deterministic parser per format, each tested against that shipper's real files, instead of one general-purpose reader. A general reader has to guess on every file. A per-format parser guesses once, when it's written, and the tests catch it when the shipper changes something. Super intelligence (SI) is used where a deterministic parser can't work at all: scanned PDFs (covered separately).

The formats

Every parser writes the same load record. Normalization of cities, equipment, mileage and pay rules happens once, after parsing, not inside each parser.

Routing an email to the right parser

Inbound email goes through two decisions, in this order:

1. Which agency? This is decided first, from an import code in the subject line or a token in the address the email was sent to. A customer's intake address is only trusted if exactly one agency uses it. Otherwise the agency's allowed sender domains decide. There is no default agency: an email that can't be placed isn't imported into anyone's board.
2. Which parser? Checked in order: known sender rules, the inbound address it was sent to (each customer can have its own), subject keywords, and finally the shape of the body.

Getting the agency right comes first because a misrouted parser produces bad loads, but a misrouted agency leaks one company's freight into another's board.

Finding the header row

Shipper sheets rarely start at row 1. There's a logo, a title, a date and a note from the plant manager. The sheet parser scores the first 15 rows and picks the first one with at least two known column names (origin, destination, rate, date, equipment, weight and their synonyms) as the header row.

Columns are then mapped by name, with ordered synonym rules:

if (h.includes("due date") || h === "date" || h === "ready date") colMap["due_date"] = i;
else if (h.includes("rate")) colMap["rate"] = i;

It's exact and substring matching, deliberately not fuzzy. Fuzzy matching feels robust until "Rate" and "Rated Weight" swap. With explicit rules, a new header name is a one-line change with a test, and a shipper inserting a column doesn't break anything.

Counts: "5 loads" means five

Email lists often put a count first:

5 - Opa Locka, FL - cars - Jackson, TN $2000 / Baldwin, FL $800 - ALL IN

The parser matches the count, emits five records and stores each one's slot number (1 of 5 through 5 of 5). The slot becomes part of each load's fingerprint, so re-sending the email reproduces exactly the same five loads (how the fingerprints work).

Counts in sheets are guarded. A "number of loads" cell that holds a ZIP code or a year isn't a count, anything over 100 is treated as 1, and no single row expands to more than 50 loads. A notes field that says "3 loads" is capped at 100. These limits exist because the alternative is one typo posting thousands of loads.

Ditto marks

Some shippers type a ditto mark instead of repeating the pickup city:

ORIGIN        DESTINATION     NOTES
Lamar, MO     Perryville, MO
"             Joplin, MO      3 loads

The parser recognizes the straight, curly, prime and other ditto characters. A ditto or an empty cell inherits the last explicit value for that column. A row with neither an origin nor a destination is treated as notes and attached to the next load.

We're upfront about the limits. Only origin and destination inherit, not rate or notes. And the PDF version of that shipper's list is parsed from text, where a lone ditto is harder to align, so it's less forgiving.

Emails are threads

A shipper's "same list as yesterday, one change" email usually contains yesterday's list, quoted underneath. The line parser knows the common reply markers ("On … wrote:", "From:", "Sent:", "Original Message", > quote lines, "Get Outlook for…"). Markers before the first lane line are skipped. After the first lane, the first marker ends the parse, so yesterday's quoted list never becomes today's loads. Trailing phrases like "- all in" and "- DAILY" are stripped before lanes are split.

Sheets carry signals, not just rows

Google Sheets that plants edit all day carry information in their structure, and the sync reads it:

• Hidden tabs (a plant that isn't shipping) and hidden rows are skipped, detected from the workbook's own flags and the Sheets API.
• "CLOSED FRI" in a plant's pickup hours means no Friday loads from that plant. Partial-day notes ("closed Fri after 11") don't count, and delivery hours are ignored.

Failing closed

The most important rule in the sheet sync isn't a parsing rule. Before writing anything, the sync counts the distinct rows it parsed. If that's above the source's cap (250 rows for one plant sheet, and configurable), it writes nothing to the board, returns an error, and records the per-tab counts for a person to look at.

A shipper reorganizing their sheet should produce an alert, not 900 phantom loads posted to Truckstop before anyone notices.

Testing

There are 15 parser test files with 153 tests, built from real shipper files. The largest suites cover the plant sheets (32 tests), scanned-PDF extraction (24), messy city and state parsing (22) and the email line parser (14). The TMS CSV parser has its own Python tests.

Parser fixes come with a test from the file that broke: a merged city, a ditto that didn't inherit, a quoted thread that leaked through.

New formats: SI drafts, a person decides

When an agency uploads a file SmartLoads hasn't seen, an SI model (Claude, through a forced tool call so the output is always structured) reads a sample and drafts a column mapping with a confidence score per field. The draft is shown to a person as a first pass. One email source currently applies its drafted mapping automatically, and the loads it produces go through the same normalization and checks as every hand-written parser's.

Frequently asked questions

Why not use one SI model to read every format?

Because the same sheet arrives dozens of times a day and must produce exactly the same loads every time. Deterministic parsers are exact, fast, free to run and testable. SI is used where they can't work: scanned documents and first drafts of new formats.

What happens when a shipper changes their sheet layout?

Header mapping by name absorbs most changes, like new or moved columns. Bigger changes trip the row cap or the field sanity checks, which alert a person before anything bad reaches the board.

How long does a new format take?

A new format needs a parser built and tested against the shipper's real files. After that it runs automatically.

Are the parsers shared between agencies?

Yes. A format's parser can serve any agency whose shipper sends that format, and each agency's loads stay separate.