The file that doesn’t fit.
Why one spreadsheet was the whole problem
A route-planning platform runs on exact optimisation models: give it clean stops, time windows, capacities and a depot, and it returns routes a dispatcher can trust. The catch is the word “clean”.
Every new client arrives with the order file from their old system.
An Excel that follows nobody’s pattern: column names in their language, merged address cells, time windows written as “8–12”, vehicles named after drivers. Getting that file in meant an afternoon of re-keying per client, repeated every week until they migrated. It was also the step where deals died: a prospect who likes the routing still has to get data in, and if that step looks like work, the trial never starts.
I owned the import end to end and built it three times: a template people filled in by hand, then automatic column mapping, and finally an AI-assisted parser that splits merged cells into stops, windows, vehicles and routes and lists everything it assumed.
The import, step by step
Four steps that stayed the same across all three approaches: read, map, review, plan. The approaches differ only in who does the mapping: the client, a rule set, or a model. The review step never goes away, because a wrong time window silently produces a wrong plan.
Clients arrive with a file, so the file is the default source, and the drop zone says up front that nothing needs reformatting. The template stays for people who prefer it, as a small row below. After reading, the orders land in the same table and filters the planner uses for planning, so checking an import happens where they already work.
Three approaches, kept as working prototypes
Each one shipped or was tested with real files. The prototypes below run on their own, with invented data.
Approach 1 · Our template
We send the client an import template; their team copies every order into our columns. The system trusts the result completely.
- Nothing to guess: every field lands where the optimiser expects it
- No engineering on our side
- Hours of manual work per client, every week, until they migrate
- Clients refuse to do it; trial-to-active conversion was the lowest of the three
- Typos go straight into routes
Approach 2 · Automatic mapping
Upload the file as it is. The system matches columns by name, type and sample values, asks the planner only about the leftovers, and refuses politely when a required field doesn’t exist anywhere.
- Minutes instead of hours; most columns match on the first try
- A clear contract: what we could not map, you map; what nobody can, goes back to the client with one sentence
- Rules only look at column names and types: a merged “address, window, van” cell is one column to them
- Every new client file adds a rule, and the rule set is the thing that drifts
Approach 3 · AI-assisted parsing
The client’s sheet has one “Delivery” column with everything in it: street, city, window, van, parcels, a note. A model reads it the way a dispatcher would and writes each row out into our seven fields, marking what it had to assume. The planner confirms or corrects before anything touches the optimiser.
- Handles merged cells, free text and the client’s own naming without new rules
- Assumptions are visible, so a wrong guess costs one click to fix before it reaches a route
- Onboarding a file takes minutes of review
- Tokens cost money per file; cheap next to a dispatcher’s hour, but still a cost
- Confidence is not correctness: the review step cannot be skipped
- Needs a fallback to approach 2 when the model is unavailable
Columns and review
Our fields on the left, the client’s columns as dropdowns with a sample value, so a planner can check them without opening the file. A time window written as “8–12” becomes two fields. What is not in the file is filled from defaults and marked as assumed, never silently.
Review lists every assumption as one line with its evidence: how many rows, which pattern. Keep or change. Rows that nobody can map become a list the planner sends back to the client in one click, and the rest of the import goes on.
| Our field | Column in your file | Status |
|---|---|---|
| Customerreq | KundeNordhavn Bakery | matched |
| Street + cityreq | Adresse / byHarbour St 12, Northbridge | matched · split on comma |
| Earliest · latest arrivalreq | Tidsvindue · start8Tidsvindue · end12 | guessed from “8–12” |
| Parcelsreq | Kolli4 | matched |
| Weight, kg | Not in file · assume from parcelsavg 6 kg | assumed |
| Depotreq | Not in file · use defaultMain depot | assumed |
They can be imported with the default 08:00–16:00, or sent back to the client as a list.
Side by side
Why the third one became the default: it is the one clients actually finish.
| Criterion | 1 · Template | 2 · Auto mapping | 3 · AI-assisted |
|---|---|---|---|
| Client effort | Re-type every order | Upload, answer a few questions | Upload, confirm |
| Time per file | Hours | Minutes, if columns are clean | Minutes, messy files included |
| Reliability | Exact by construction | Blind to merged content | High, assumptions reviewed |
| Running cost | None | None | Tokens per file |
| Trial → active | Lowest | Better | Highest |
What I did
Design engineering here meant I owned how the import behaves, failures included.
Outcome
The work ran as a loop: prototype in code, test with the focus group, change, test again. The AI-assisted import shipped as the default path for new clients.