How to move your data off spreadsheets without losing anything
Migration is the most underestimated part of any software project, and the part most likely to derail it. Here is the sequence that keeps it safe, and the cleanup you can do yourself for free.
Every software project has a step where years of accumulated data has to move from where it is now into where it is going. It sounds administrative. In practice it is where projects quietly go wrong, because the data is always messier than anyone remembers.
This post covers why that happens, what you can clean up yourself before spending money on it, and the sequence that makes migration safe rather than nerve-wracking.
Why migration costs more than people expect
A spreadsheet is forgiving. It will happily hold a date as text in one row and a real date in the next, accept a phone number with spaces or without, and let someone type Not applicable into a numeric column. Nothing complains, so nobody notices.
A database is not forgiving, and that is the entire point of using one. It enforces rules, which is what makes reporting trustworthy. But every inconsistency accumulated over ten years has to be resolved before the data can go in, and someone has to decide what each one should become.
That decision work is the real cost. It is also why migration is often quoted separately, and why a quote that does not mention it is incomplete. There is more on this in what a custom CRM actually costs.
The four kinds of mess
1. Duplicates that are not exact duplicates
The same customer appears as J Smith, John Smith, and Smith John Ltd. A human can see these are one record. Software cannot, at least not reliably, so someone has to review the matches. This is usually the largest single chunk of migration effort.
2. Inconsistent formats
Dates written as 01/02/24, 1 Feb 2024, and Feb 2024. Phone numbers with and without country codes. Prices with currency symbols embedded in the cell. Each of these needs a rule, and some need a judgement call about what was originally meant.
3. Meaning hidden in formatting
This one catches everybody. A row highlighted amber means the customer is on hold. Bold text means priority. A strikethrough means cancelled. None of that survives an export, because colour is not data. If your spreadsheets carry meaning in their formatting, write down what each convention means before anyone exports anything.
4. Free text doing a structured job
A single Notes column containing delivery instructions, complaint history, and the fact that this client always pays late. Valuable information, impossible to report on. Part of migration is deciding which of it becomes structured fields and which stays as notes.
What you can clean up yourself
Migration cost scales with mess, so cleanup done in house is money saved directly. None of the following needs a developer.
- Delete what you no longer need. Old records you will never look at again should not be migrated at all. Under UK GDPR, holding personal data you have no use for is a liability rather than an asset.
- Standardise the obvious columns. Pick one date format and one phone format, and apply them consistently.
- Split combined fields. A single Name column becomes first and last name. One Address column becomes street, town, postcode.
- Resolve the duplicates you already know about. Your team can usually identify the worst offenders from memory faster than any matching algorithm.
- Document the colour coding, then replace it with an actual status column containing words.
A safe migration sequence
The order matters, because it determines whether problems surface while they are still cheap to fix.
- Take a full backup of everything, untouched. Before any cleanup, before any export. Keep it somewhere separate and do not work in it.
- Agree the mapping. Every column in the old data gets a destination in the new system, or an explicit decision that it is being dropped. Write this down and review it together.
- Do a test migration into a copy of the new system. Nothing goes live. The purpose is to find out what breaks.
- Check totals, not samples. Compare record counts, sums of key numeric columns, and the number of customers with no email. Discrepancies here reveal problems that spot checks miss.
- Review the edge cases by hand. The oldest records, the largest customer, anything with unusual characters in the name. Problems cluster at the extremes.
- Fix, then repeat the test migration. Expect to do this two or three times. A migration that works first time usually means the checks were not thorough enough.
- Go live with a known cutoff point, and keep the old system readable for a period afterwards.
Run both systems briefly, but set an end date
A short overlap where the old spreadsheets stay readable is sensible insurance. It lets people check something if the new system looks wrong, which builds confidence in the switch.
The trap is letting the overlap run indefinitely. If people can still edit the old spreadsheets, some of them will, and you end up maintaining two versions of the truth. Make the old data read only from day one of the switch, and set a date after which it is archived.
Keep the old system readable for a while. Do not keep it writable for a day longer than necessary.
What to archive rather than migrate
Not everything deserves to move. Records for customers you have not dealt with in years, superseded pricing, and data you kept because deleting felt wrong are all candidates for archiving instead.
There is a compliance angle here too. Storage limitation under UK GDPR means personal data should not be kept indefinitely without a reason, so a migration is a natural moment to apply a retention policy. Our practical GDPR guide covers what that involves.
The general rule: migrate what you actively use, archive what you might need for legal or reference purposes, and delete what you have no business reason to hold.
Frequently asked questions
For a small business moving from a handful of spreadsheets, typically one to three weeks of the overall project, though it varies enormously with data quality. Cleaning done in house beforehand reduces this directly, and is the single most effective way to control the cost.
Not if it is done properly. The protections are a full untouched backup taken before anything starts, a written mapping of every field, test migrations into a copy of the system, and verification by comparing totals rather than spot checking rows.
Often yes, and it can be a sensible way to reduce risk. Move active records first so the system is usable, then bring across historical data as a second phase. This works best when the historical data is for reference rather than something you report on daily.
Document what each colour means before exporting anything, because formatting does not survive an export. Then replace the colours with an explicit status column containing text. This is worth doing even if you never migrate, since it makes the spreadsheet itself far less fragile.