Getting off a spreadsheet without losing the history

The hard part of leaving a spreadsheet is not the import. It is that the sheet encodes four different things in one row, and you have to decide what each column actually meant before anything can be loaded.

Start by naming the columns properly

Go through the header row and label every column with which of the four nouns it belongs to. Most sheets sort cleanly into these four once you force the question:

• Asset: serial number, model, manufacturer, location, department, owner.
• Obligation: certificate number, expiry, issuing body, contract reference.
• Plan: service interval, calibration frequency, inspection schedule.
• Evidence: last service date, result, engineer, certificate file.

The columns that refuse to sort are the interesting ones, and there are usually two kinds. A column called Status that mixes asset lifecycle with obligation validity is really two columns. A column called Notes is really a dozen columns that were never created, and you will find dates, names and results buried in the free text.

The column that holds three dates

Almost every sheet has a single date column doing the work of three. Before you map it, sample twenty rows and ask what the date actually is in each one. You will typically find a mix of the expiry, the date the renewal process has to start, and the date somebody last looked at it.

Do not try to infer this. Map the column to expiry, import it, and then set renewal lead times as a second pass — as a policy per obligation kind rather than a guess per row. It is faster and it is defensible, which a heuristic over old data is not.

Rows nobody can explain

Every sheet has them: an asset with no serial number, a certificate with no issuing body, a row that is highlighted yellow for a reason that left with the person who highlighted it. They are the reason migrations stall, because the team tries to resolve them before importing anything.

Import them. Bring them in flagged as incomplete, with the original row preserved, and work the list down afterwards. A register that is ninety per cent correct and visibly marks the other ten per cent is far more useful than a spreadsheet that is ninety per cent correct and marks nothing.

The rows you cannot explain are not an obstacle to the migration. They are the first thing the migration is for.

Make the import reversible

Run the mapping as a dry run first and read what it says it will create. Check the row count, then check five rows by hand against the sheet — specifically the ones with the messiest dates. Then commit the batch, knowing that the whole batch can be undone in one action if the mapping was wrong.

This is what makes it reasonable to import on day one rather than after a month of cleaning. The cost of being wrong is one undo, so there is no reason to defer.

Keep the old sheet, read-only

For the first few months, keep the original file somewhere read-only rather than deleting it. Not as a fallback to keep updating — two live registers is worse than one bad one — but as the record of what the data looked like at the point of migration. The first time somebody disputes an imported value, you will want it.