Data migration checklist: moving off spreadsheets and legacy systems without losing anything

Most software projects that go badly wrong do not fail at the code. They fail at the data. The new system works perfectly with clean test records, then real data arrives: duplicates, missing fields, three date formats, customers who exist twice with different spellings. This is the checklist we work through on every migration, whether the source is a legacy application or fifteen years of spreadsheets.

1. Audit what you actually have

• List every source: databases, spreadsheets, exports, email attachments, that one file on someone's desktop.
• For each source, record who maintains it, how often it changes, and whether it is still authoritative.
• Count the rows. People are routinely wrong by an order of magnitude about how much data they hold.
• Profile the fields: how many are empty, how many formats appear, what the outliers look like.

Do this before agreeing a scope or a price. It is the single best predictor of how long the migration will take.

2. Decide what not to migrate

Not all of it deserves to move. Agree explicitly on what stays behind: records older than a retention period, cancelled or test entries, fields nobody has used in years, and duplicates of duplicates. Keep a read-only archive of the old system so nothing is truly lost, then migrate only what the business needs to operate.

3. Clean at the source where you can

Cleaning during a migration is normal; cleaning twice is not. Where the old system will stay alive for a few more weeks, fix obvious problems there so the final extract is better. Otherwise clean in a staging copy, never in the live target.

• Standardise formats: dates, phone numbers, country codes, currencies, units.
• Split or join fields to match the new structure, such as one address column becoming five.
• Find duplicates on more than one key: email, phone, tax number, and fuzzy name plus postcode.
• Decide the merge rule before merging: which source wins, and what happens to the loser's history.

4. Map every field, in writing

A mapping document lists each source field, its destination, the transformation applied, and what happens when the value is missing or invalid. It is boring and it prevents most disputes at go-live. Include the fields that have no destination, with a note explaining why.

5. Do test runs, more than once

Run the full migration into a staging environment and let real users look at real records. The first run always surfaces surprises. The second proves the fixes worked. The third is a rehearsal with timings, so you know how long the real cutover needs.

6. Reconcile with numbers, not opinions

Before anyone signs off, compare:

• Row counts per entity, source versus target, with a documented explanation for every difference.
• Control totals: outstanding balances, stock quantities, open orders, active customers.
• Spot checks on the awkward records: the largest customer, the oldest order, the one with special characters in the name.
• Referential integrity: every order still points at a real customer and real products.

7. Plan the cutover

• Pick a quiet window and agree who is on call.
• Freeze changes in the old system, or capture the delta and migrate it last.
• Take a full backup of both systems before you start.
• Write the rollback plan and the exact point of no return.
• Agree what "done" means, and who signs it off.

8. Watch the first two weeks

Keep the old system readable for a while. Log errors, listen for "this record looks wrong", and fix quickly. Most residual problems appear in the first fortnight, then stop.

How this fits a wider data project

If the same records live in several places, migration is a good moment to fix the root cause rather than move the mess. That is what an EDM platform does: one governed version of each record, with rules at the point of entry so quality does not decay again. Our guide to EDM versus MDM explains which part to start with, and data management and reporting covers the dashboards that follow.

Planning a migration?

Send us a description of your sources and the system you are moving to, and we will come back with a migration plan and a fixed quote. Tell us what you are moving.