Migrating Data Between Systems Without Losing Information

Why do data migrations go wrong?

Choosing a new system is usually the easy part of the decision. The hard part is taking what you already have with you: customers, products, invoices, history, balances. A badly done migration of data between systems shows up within weeks, when someone asks why a customer appears twice, why the 2022 invoices are missing, or why inventory no longer adds up.

It rarely fails because of the technology. It fails because it’s treated as a last-minute chore (“we’ll move the data Friday”) instead of a project with its own steps. The most common mistakes:

Nobody knows what data actually exists. Some lives in the old system, some in spreadsheets, and some in two people’s heads.
Everything gets migrated, junk included. Duplicates, incomplete records, and customers who closed years ago move over as if they were valid.
Nothing is tested with real data until cutover day.
There’s no way back. If something breaks, you can’t return to where you were.

All of these are preventable with a bit of order. Here’s how.

What should you check before starting?

Before moving a single record, take inventory. You don’t need special tools; a spreadsheet is enough.

Which systems and files hold data that matters: the current software, spreadsheets, email, accounting, the point of sale.
What data you truly need in the new system. Ten years of history doesn’t always need the same level of detail as this year’s.
Who owns each piece of data. If nobody answers for the customer list, nobody can say whether it came through right.
What obligations apply. For example, tax or accounting records you must keep for a set period; ask your accountant what applies to you.
How old or closed the source system is, and whether it can export data in a usable format (CSV, Excel, a database) or whether you’ll need another way to get it out.

This exercise also shows whether the real problem is the migration or that your systems simply don’t talk to each other, something we cover in the hidden cost of double entry.

How do you clean data before moving it?

Migrating dirty data just moves the mess to a new place. Cleaning happens at the source or on a copy, before anything is loaded into the new system. Typically you look for:

Duplicates: the same customer spelled three ways (“Lopez Hardware”, “Lopez Hardware LLC”, “J. Lopez Hardware”).
Empty or invalid fields: phone numbers with missing digits, emails without an @, impossible dates.
Obsolete records: discontinued products, suppliers you no longer use.
Mixed formats: dates in different orders, amounts with and without thousands separators, inconsistent capitalization.

Decide a rule for each case and write it down. For example: “if two customers share the same tax ID, merge them.” That makes the cleanup repeatable instead of depending on one person’s memory.

What is data mapping and why does it matter?

Mapping is the document that says: this field in the old system goes to that field in the new one, with this transformation. It’s the most underestimated piece.

A simple mapping has these columns:

Source field (for example, “CustName”).
Destination field (for example, “company name”).
Transformation rule (for example, “trim extra spaces and capitalize”).
What to do if the value is missing.

The trouble usually comes from fields with no direct equivalent: one system stores the address on a single line while the other wants five fields; one handles several contacts per customer and the other only one. Better to find that out on paper than after the data is loaded.

How do you test a migration?

You never migrate “live” on the first try. The safe path:

Run a trial migration into a separate environment, using a copy of real data.
Compare totals: number of customers, number of products, sum of outstanding balances, invoices per year. If the old system says 1,240 customers and the new one 1,198, you need to understand where the 42 went.
Check a sample by hand. Pick twenty or thirty records of different kinds and compare them field by field. Include the odd cases: the customer with a very long name, the voided invoice, the product with variants.
Ask the people who use the system to try it with their daily work. Whoever invoices every day spots at once what a totals report won’t show.
Repeat until every difference is explained.

These tests are also the time to refine the mapping and cleanup rules you wrote earlier.

Can you migrate without stopping the business?

It depends on your operation, and it’s worth deciding calmly. Two common strategies:

Direct cutover: pick a moment (a weekend, a month-end close), freeze the old system, migrate, and start on the new one. It’s simpler, but you need a window where normal operations pause.
Phased or parallel migration: the old and new systems coexist for a while. Master data (customers, products) moves first, then transactions. It’s gradual, but you must avoid entering the same thing in two places and keep the data from drifting apart.

Pick the date with a calendar in hand. A switch in the middle of an accounting close or your peak sales season raises the risk for no reason.

What if something goes wrong?

A good plan assumes it might. Before the big day:

Make a full backup of the old system and confirm it can be restored. A backup that has never been tested is not a guarantee.
Define who decides to roll back, and under what conditions: for example, if totals don’t match or a critical process fails.
Keep the old system in read-only mode for a while after the switch, so you can check a doubtful figure.
Tell the team what changes, when, and who to go to with questions.

When should you get professional help?

A small migration, from a spreadsheet into a simple system, can be handled by your own team with care. It’s worth bringing in specialists when:

the source system is old, closed, or undocumented;
several sources must be unified;
the data feeds accounting, invoicing, or inventory, where an error costs money;
you can’t afford to stop operations.

When the source and destination must stay connected after the switch, the work looks more like systems integration than a one-time load. And if no off-the-shelf tool fits how you operate, the next step may be custom software, where the migration is designed together with the system.

How much does a data migration cost?

There’s no honest number without seeing your case. It depends on how many systems and files are involved, the volume and quality of the data, how much cleanup it needs, how open the source system is, how much history must come along, and whether operations can pause. That’s why you should be wary of anyone quoting a fixed price before looking at your data.

Where should you start?

Start with the inventory: a list of where your information lives, who answers for it, and how much of it you need to take along. With that in hand, any conversation with a vendor, or with your own team, gets much more concrete.

If you’re about to change systems, or already did and the numbers don’t match, tell us your case on the first call, which is free. We’ll go over what needs to move, the risks, and your options, and you’ll leave with an honest recommendation and a real estimate. Book your call here.