The riskiest part of moving off spreadsheets is not building the system — it is the afternoon your records go into it. Two of our own importers had large, passing test suites and both were broken, because every test handed the parser a list of values the test itself had written. That proves the rules agree with the developer. It proves nothing about your actual file.

This is an account of what we found in our own code, because a supplier telling you their import is "fully tested" is telling you less than they think.

Why a green test suite misses this

An import has two halves. The second half is the rules: this column is a company name, that one is a date, a blank in this position means the row is incomplete. Those are easy to test and are usually tested well.

The first half is getting from a file on somebody's desktop to a grid of values at all — opening the workbook, finding the right sheet, working out which row the headings are on, deciding what a cell containing a date actually is. Almost nobody tests that half, because doing so means keeping real spreadsheets in the codebase and opening them properly rather than describing them in code.

The comment we left in our own test file when we finally fixed this says it plainly: a workbook whose headings sit on row 2, whose dates come back as date objects rather than text, or whose first sheet is a cover page, all pass a hand-made test and fail on a customer's Monday morning.

Three failures we found only by using a real file

A heading claimed by two different columns. Two headings in the sheet were similar enough that the matcher assigned both to the same field. One column silently won, the other silently vanished. Nothing errored. The import reported success and a column of real data simply was not there.

A decimal in a whole-number column. A figure that had been typed with a fraction went into a field that only accepts whole numbers. In a test written by hand, nobody types 4.5 into a column they have just decided is an integer. In a real sheet somebody did, two years ago, and forgot.

A missing field becoming an empty one. This is the nastiest, because it is invisible until it is catastrophic. When rows are inserted in bulk, a row that simply does not carry a particular field can be written as empty rather than taking the column's normal default. If half your sheet fills in a field and half does not, the whole import can be rejected — and the error message points at the database, not at the sheet.

We fixed that last one explicitly, and left a note in the code saying not to tidy the fix away: the day somebody adds a field to only some rows, it is the difference between the import working and every row being refused.

How to actually prove an import works

The only convincing test is your real file against the real system, before anyone commits to anything. That sounds dangerous and does not have to be.

The technique we use is a rehearsal that is thrown away: run the genuine parsed rows into the live database inside a transaction, check that everything the system is supposed to do actually happened, and then roll the whole thing back. Nothing is kept. We ran 86 rows from a real customer sheet this way — every check, every required field and every automatic flag applied exactly as it would in earnest, 26 rows correctly marked for review — and when it finished the table held precisely what it had held before.

One discipline goes with it: replace the names before you start. Even inside a transaction you intend to discard, one organisation's records have no business being typed into another organisation's database. We substitute placeholder names first, every time.

What this means for your migration

Assume the first import will be wrong somewhere, and plan for finding out rather than for being lucky. Practically:

  • Send the messiest file you have, not a tidied one. A cleaned sample proves nothing, and the mess is where the process actually is.
  • Ask to see the result before it counts. You should be able to look at what came across and say "that is not right" while it is still reversible.
  • Check a column that should be full. The silent failure is a field that arrives empty, not a crash — so count something you know the answer to.
  • Keep the spreadsheet. Do not delete anything for a good while, whatever anybody tells you about the migration being complete.

The question worth asking a supplier

Not "is the import tested?" — the answer is always yes, and we would have said yes too while ours was broken. Ask instead: "have you run my actual file through it, and can I see what came out?"

A supplier who has done that will show you. One who has not will explain their testing. The difference between those two answers is the whole thing.

We built our own product's import around this: you send the spreadsheet you already keep, it reads your columns, shows you what it found, and you look at it before anything is created. If you are weighing up the move at all, we have written separately about when it is genuinely time, and if you would rather we just looked at your file and told you what we see, send it over.