Data Migration: Moving Off Spreadsheets Safely
Short answer: A safe spreadsheet-to-ERP migration requires cleaning the source data first, mapping it to the target system's structure, running test imports before go-live, and validating outputs against the original records. Skipping any of these steps produces a live system with dirty data — which is harder to fix than the spreadsheets you started with.
Why Migration Is Harder Than Import
The common assumption: export data from the spreadsheet, import it into the ERP, done. The reality is that spreadsheets accumulate structural problems that an ERP won't accept or will accept incorrectly.
Typical issues found in spreadsheet data before migration:
- Customer names entered differently across rows ("Tan Brothers Trading", "Tan Bros Trading Sdn Bhd", "TBT")
- Product codes that changed over time, with both old and new codes present
- Stock quantities that don't match across different sheets tracking the same items
- Missing fields that the ERP requires (supplier payment terms, item category codes)
- Date formats inconsistent across rows (DD/MM/YYYY mixed with M/D/YY)
- Formulas in cells rather than values — some evaluating to errors
None of these crash the spreadsheet. All of them cause problems in an ERP that validates data on entry.
The Migration Process
Data migration and cleanup follows a defined sequence:
1. Audit the source data. Inventory all spreadsheets. Document what data exists, in what format, and where the conflicts are. This step reveals the true scope of the migration — which is almost always larger than estimated.
2. Define the target structure. The custom ERP or the target system has a data model. Customer records, item master, opening stock, open orders, supplier records — each has required fields, data types, and relationships. Map source columns to target fields before touching a single row.
3. Clean the source data. Deduplicate. Standardise. Fill mandatory fields. Resolve conflicts between spreadsheets. This is the labour-intensive part. It cannot be automated away — decisions need to be made about which version of a record is correct.
4. Test import in a staging environment. Run the cleaned data through an import into a test instance of the ERP. Review what imported correctly and what failed or imported wrong. Fix and re-run before touching the live system.
5. Validate outputs. After import, run reports in the ERP and compare totals to the original spreadsheet. Stock quantity by item. Outstanding invoices by customer. Supplier balances. Discrepancies here are easier to fix now than after go-live.
6. Go-live with a cutover plan. Define the date after which new transactions happen in the ERP only. Close the spreadsheets. Run parallel for a defined period if the business requires it — but set a hard end date.
What to Migrate vs What to Archive
Not all spreadsheet data needs to go into the ERP. Consider:
| Data Type | Migrate or Archive? |
|---|---|
| Current stock quantities | Migrate (opening balance) |
| Open sales orders | Migrate |
| Open purchase orders | Migrate |
| Completed historical transactions | Archive (reference only) |
| Old customer contact details | Migrate active, archive inactive |
| Internal notes and correspondence | Archive |
Migrating five years of historical transactions into a new ERP is usually not worth the effort. Opening balances as of the go-live date, plus open documents, is sufficient for most SMEs.
The Cost of Getting It Wrong
A go-live with corrupt or incomplete data doesn't announce itself clearly. Stock quantities that are wrong lead to overselling. Customer balances that are off affect collections. The errors surface as operational problems — wrong items shipped, disputed invoices, reconciliation gaps — and the root cause gets traced back to the migration weeks later.
Fixing data in a live system is more expensive than cleaning it before migration. Every transaction entered after go-live becomes entangled with the bad opening data.
FAQ
How long does a spreadsheet-to-ERP migration typically take?
The data cleanup phase is the variable. For a trading company with a few thousand customer records, a product catalogue of several hundred items, and two to three years of open transactions, four to eight weeks from audit to clean data is realistic. Larger datasets or more inconsistent sources take longer.
Do we need to migrate all historical data or just opening balances?
For most operational purposes, opening balances — stock quantities, customer and supplier outstanding balances, open orders — are sufficient. Historical transaction data can be archived in its original format and referenced as needed. Migrating years of completed transactions rarely provides value that justifies the cost.
What if our spreadsheets are built with macros and formulas — does that complicate migration?
Yes. Formulas and macros generate values that are visible in the spreadsheet but aren't stored as data. The migration captures values, not logic. Before migration, run a full calculation pass on all spreadsheets and save the outputs as static values. Document any business logic embedded in macros — that logic may need to be replicated in the ERP as automation rules.
Book a System Audit