Short answer: Migrate in five stages: design the target schema first, clean and standardise the source data, split flat rows into related records, import in dependency order (parents before children), then validate with reconciliation counts before switching anyone over. The cleaning stage takes longest and determines whether the migration succeeds.
Stage 1: Design the target before touching the source
The most common migration failure is recreating the spreadsheet's shape in the database. A spreadsheet row typically mashes several entities together — client details, project details and invoice details all on one line. Importing that as-is gives you a slow spreadsheet with a nicer interface.
Design the schema first: which entities exist, what fields each holds, how they relate. Then work out how to get the spreadsheet's contents into that shape.
Stage 2: Clean the source
This is where most of the effort lives. Working through a copy of the source, never the original:
- Standardise formats. Dates into one format, phone numbers into one convention, currency as numbers not text.
- Resolve inconsistent naming. "Acme Ltd", "Acme Limited" and "ACME" are one company and will otherwise become three records.
- Deduplicate. Identify a matching key — email, company number, postcode plus name — and merge. Record the merge decisions; you'll be asked about them.
- Handle blanks explicitly. Decide what an empty cell means: unknown, not applicable, or zero. These import very differently.
- Strip formatting-as-data. Colour-coded rows carry meaning that won't survive. Convert it to a real column first.
Stage 3: Split flat rows into related records
A row containing client name, project name and invoice amount needs to become three linked records. The method:
- Extract unique values for each entity into its own sheet — a deduplicated list of companies, one of projects, and so on.
- Give each a stable unique identifier. Do this now; retrofitting IDs after import is painful.
- Build the child sheets carrying the parent's identifier as a linking column.
Stage 4: Import in dependency order
Parents before children, always. Companies before contacts, contacts before deals, deals before invoices. Airtable will create linked records automatically from matching text values, which is convenient and dangerous — a typo creates a new company rather than linking to the existing one. Import parents first, then match children against the existing records rather than letting them create new ones.
Import in batches and check the first batch thoroughly before running the rest.
Stage 5: Validate before you switch
Reconcile before anyone starts working in the new system:
- Record counts per table against expected counts from the cleaned source.
- Financial totals — sum the value column in both and confirm they match exactly. Any discrepancy indicates a parsing problem.
- Orphan check — child records with no linked parent, which indicates failed matching.
- Spot check twenty random records end to end against the source.
- Date range check — earliest and latest dates in each table, which catches misparsed date formats immediately.
Then: close the old system
Make the source spreadsheet read-only on a defined date and communicate it clearly. Migrations that leave the old system editable end with two live systems and a reconciliation problem that never resolves. Keep an archived copy indefinitely — you'll want it once.
Frequently asked questions
How long does a migration take?
For a few thousand records with moderate mess, one to two weeks. Cleaning typically consumes seventy percent of that.
Should I migrate historical data?
Migrate what you'll actually use — usually two to three years. Archive the rest as a static export rather than importing it and slowing the working system.
Can this be automated?
The import can be scripted, and should be for anything above a few thousand rows. The cleaning decisions need a human who understands the business.
We handle migrations from spreadsheets, legacy CRMs and multi-source datasets. Talk to us.
