Migrating Data From Excel or an Old System: How to Check the Result

A data migration isn't judged by whether the data made it in. It's judged by whether you can prove that all of it made it in, correctly. The difference shows up three months later, when someone looks for a document from two years ago and can't find it: at that point, no one can say anymore whether it wasn't there to begin with, was deliberately excluded, or got lost during the transfer.

The hard part isn't moving the information. It's preserving the links between it — the customer connected to their orders, the order connected to its documents — and keeping the ability to verify it. This guide will help you prepare the transfer so it's checkable, not just doable.

Inventory: What Exists, and What Shouldn't Come Along

Before looking at fields, look at the sources. There are almost always more than what's officially declared: alongside the management system there are spreadsheets someone used to solve a problem the system didn't handle, and those spreadsheets hold data no one else has.

• For each source: who uses it, who updates it, how long it's existed, how many rows it contains, and which is the most recent.
• Quality, in concrete terms: how many rows have an empty required field, how many addresses are incomplete, how many dates are impossible. A few checks are enough to understand what you're dealing with.
• What shouldn't come along: records inactive for years, tests, service rows, data you no longer have a reason to keep. This is a decision to make and write down, not to put off until the moment of transfer.
• What should stay accessible but not active: historical records. Often the right choice is to bring the last few years into the new system and keep the rest in a readable archive, instead of dragging everything along.

The Source-to-Destination Map

This is the central document of the whole job: one row per field, with the rule written next to it. It's filled in before doing anything, and it also serves as documentation afterward. The values are illustrative.

The last row is a useful general rule: whatever can't be reliably interpreted gets transferred as is, into a text field. Worse than losing a piece of data is transforming it based on a wrong assumption, because the first error is visible and the second isn't.

Identifiers and Duplicates

Every entity being transferred needs a stable key, and the old system's key should be kept in the new one too, in a support field. It costs nothing and lets you answer, for years to come, the question "where does this customer come from?"

• Duplicates are searched for before the transfer, in the source, where they can still be corrected by people who know the data.
• Merging two records needs to be decided by a person, not by an automatic rule: two locations of the same company might be two distinct customers for accounting reasons.
• When two rows are merged, both old keys are kept: historical documents point to one or the other.
• Duplicates that can't be resolved are transferred anyway, flagged. A visible duplicate is a problem; a duplicate deleted by mistake is a loss.

The Trial Run, and the Three Checks That Matter

The transfer is first run in a test environment, with a sample that includes the hard cases: the customer with the most orders, the one with the odd address, the one with accented characters in their name, the oldest order and the most recent one.

1. Counts: how many rows in the source, how many in the destination, how many discarded and why. The sum needs to match exactly, and discards need to be listed by name, not just counted.
2. Totals: the sum of amounts per year, in source and destination. This is the check that catches, in one shot, both misread decimal separators and lost rows.
3. Relationships: take a customer — do they have all their orders? Take an order — does it have all its line items and documents? This is the check that gets skipped most often, because the overall count can still match even when the links have broken.

The Reconciliation Record and Cutover Day

The reconciliation record is a page that gets signed and kept: transfer date, version of the sources, counts and totals compared, list of discards, decisions made on exclusions, name of who verified it. It's there to settle the discussion months later, when memory alone isn't enough anymore.

Cutover is planned the way you'd plan a move. You choose a moment when activity is at a standstill, tell people the point after which the old system is no longer updated, and decide in advance what to do about information entered in the meantime, because someone will enter it anyway.

• A complete backup of both source and destination, verified before starting: a backup no one has tried to read back isn't a backup.
• The old system stays accessible in read-only mode for an agreed period. That's the real fallback, more than a technical restore.
• A bail-out condition written down beforehand: if the checks don't match by a certain point, you cancel and try again another time. Deciding this in the middle is impossible, because everyone is already exhausted by then.
• Known exceptions need to be assigned to someone with a deadline, not left sitting in a list: addresses that need fixing by hand don't fix themselves.

What This Guide Doesn't Cover

This guide covers a one-time transfer: moving data from where it is to where it needs to be, once. Keeping two systems that both stay in use aligned is a different problem, with its own rules about which one governs which data and what happens when the two disagree, and it's covered separately. Also out of scope is the upstream decision of whether to replace the current tool in the first place.