Data migration is where accounting system projects most often go wrong, and dirty data is the usual reason. This guide gives you a 5-phase framework, from data audit to go-live, with field mapping, a cleaning checklist, validation steps and realistic timelines. Whether you are moving from Sage 50 to Xero, QuickBooks to a mid-market ERP, or spreadsheets to your first proper system, the migration process is the same. Get it right and the rest of the project falls into place. Get it wrong and you spend months reconciling a system nobody trusts.
Written by Stuart Wilson, ACMA CGMA, a CIMA chartered management accountant whose migrations include moving fund accounting off manual spreadsheets onto the Linedata and Thomson Reuters platforms.
If you have been through a system change, you will recognise the problems. If you are about to, these are the ones to plan for.
The trial balance does not agree after cutover. Aged debtors in the new system do not match the old one, and nobody can explain the £27,000 difference. Supplier records were duplicated during the import, so there are now three versions of the same stationery supplier, each with different payment terms. Customer names came across with stray characters, so half the invoice history sits under one spelling and half under another. The finance team is running both systems side by side and reconciling every night, and the month-end close has stretched from 8 days to 22.
The root cause is nearly always the same: the migration was treated as a technical task instead of an accounting project.
Data migration is a financial controls problem, not an IT problem. The person who should own it is not the software implementation partner; it is your finance director or financial controller. Someone who understands what the data means, not just how to move it.
1. Why Migrations Fail
The ways migrations fail are consistent. Three cause most of the damage:
Dirty Data Is Migrated As It Is
Illustration: the old system holds 4,200 supplier records. 1,100 are duplicates, 600 have not been used in three years and 200 have wrong VAT numbers or bank details. Nobody cleaned them because "we'll fix it in the new system". You will not. You will have 4,200 dirty supplier records in a newer screen, and the purchase ledger team will spend months untangling them while real invoices pile up.
This is the most common failure. Teams export from the old system, change the data just enough to fit the new system's import template, and load it. They never ask: should this data exist at all?
Audit and clean your data before you touch the new system. Merge duplicate suppliers and customers. Remove inactive records. Check VAT numbers, Companies House numbers and addresses. Correct account coding errors. This is Phase 1 of the framework below, and it should take around a third of the total migration time.
No Field Mapping Plan
Every system slices data differently. QuickBooks has classes and locations; Xero has tracking categories; Sage has departments and analysis codes; a mid-market ERP may add subsidiaries and several custom dimensions. Your old system's "Project" field will not map one-to-one to anything in the new one. Without a complete field-by-field mapping document before migration starts, you find the gaps during testing, when they are expensive to fix, or after go-live, when the reports do not show the data the way your board or lender expects.
Build a complete field mapping spreadsheet: every field in the old system, its matching field in the new one, the transformation rule, and a default for any gap. Review it with your finance lead, your implementation partner and at least one person who uses the data every day.
Going Live Without a Parallel Run
The implementation partner says the system is ready. The directors want to stop paying for two systems. Someone picks a go-live date to suit a board meeting, not readiness. The team cuts over without running both systems side by side. Day one: the balance sheet does not agree. Day five: payroll journals post to the wrong accounts. Day ten: the accountant preparing the year-end accounts asks for a report the new system cannot produce because nobody mapped that data.
Run both systems in parallel for at least one full month-end close, ideally two. Process every transaction in both. Reconcile every account. If the numbers do not agree, you are not ready. Set the go-live date on reconciliation results, not calendar pressure.
2. The 5-Phase Migration Framework
The framework works whether you are moving from Sage to Xero, QuickBooks to a mid-market ERP, or a set of spreadsheets to your first real system. The software changes. The process does not.
Phase 1: Audit and Clean
Duration: 2 to 4 weeks
Before you migrate anything, find out exactly what you have. Export the full chart of accounts, customer and supplier lists, open purchase and sales ledger items, and a sample of historical transactions. Then audit them hard:
- How many duplicate customer and supplier records are there?
- How many nominal accounts are inactive or redundant?
- Are VAT numbers and bank details complete and correct?
- Do recorded payment terms match what actually happens?
- How many open items are stale (over 90 days with no activity)?
This phase produces a data quality report: a short document showing how much cleaning is needed and where the risk sits. It is the cheapest point in the project to find problems.
Phase 2: Map and Transform
Duration: 2 to 3 weeks
Build the field mapping document: every source field to its target field, with transformation rules. This is also where you redesign the chart of accounts to suit the new system. Typical decisions at this stage:
- Consolidating several hundred nominal accounts into a cleaner, smaller structure
- Mapping QuickBooks classes or Sage departments to the new system's tracking or dimensions
- Deciding the exchange rate for foreign currency balances at the migration date
- Setting defaults for required fields that do not exist in the old system
- Converting formats (dates in DD/MM/YYYY, address fields, postcodes, phone numbers)
Phase 3: Parallel Run
Duration: 2 to 4 weeks
Load the cleaned data into a test company or sandbox. Run real transactions through both systems for at least one full accounting period. This is not optional. You are checking:
- The balance sheet agrees between old and new systems
- Aged debtors and aged creditors agree
- Income and costs are recognised in the same periods
- Bank reconciliations agree
- VAT return figures for the period agree
- Reports come out in the format your board pack uses
If anything does not match, stop. Trace the difference. Fix the mapping or the data. Do not move on until the numbers agree.
Phase 4: Validate and Reconcile
Duration: 1 to 2 weeks
This is the sign-off phase. The finance lead reviews every reconciliation and formally approves the migration. Key checks:
- The trial balance in the new system matches the old one to the penny
- Sales ledger, purchase ledger and fixed asset totals agree with their control accounts
- Customer and supplier record counts match (after planned de-duplication)
- Open item ageing matches between systems
- Key reports (profit and loss account, balance sheet, cash flow) produce correctly
Phase 5: Go-Live and Monitor
Duration: 2 to 4 weeks after cutover
Cut over at a period end, ideally the end of a VAT quarter, so one VAT return comes from each system rather than one return from two. Have your finance lead, an experienced user and your implementation partner available for the first two weeks. Monitor:
- Daily bank reconciliation for the first two weeks
- Weekly trial balance comparison against the old system
- Transaction volumes (are people actually using it?)
- Error logs: catch import failures and posting errors early
- User feedback: find training gaps before they become habits
Keep read-only access to the old system, or a complete export you can actually open. HMRC expects a company to keep its records for six years from the end of the financial year they relate to.
3. Common Migration Paths
The platforms change the tools, but the 5-phase framework applies to all of them. The common routes for UK businesses:
Desktop Sage → Cloud Accounting
Businesses on desktop Sage 50 moving to Xero, QuickBooks Online or Sage's own cloud products. The work is in exporting cleanly: ledgers, open items, VAT history and any custom fields. Check how VAT on open items will be handled and make sure the new package is set up for MTD for VAT before the first return is due. Pay attention to stock records, job costing history and any analysis codes that have no equivalent in the target system.
Xero or QuickBooks → Mid-Market ERP
Growing groups with several companies, currencies or complex stock that have outgrown entry-level software and are moving to a system such as NetSuite or Sage Intacct. Import tools handle the basic loads; the real work is redesigning the chart of accounts to use the new system's multi-company, multi-currency and dimensional reporting. Expect the data work to be a few weeks inside a much longer implementation. See our guide to why switching software will not fix your bookkeeping before you commit.
Excel / Spreadsheets → First Accounting System
Oddly, the hardest migration. When there is no structured source system, the data lives in dozens of spreadsheets on several people's laptops and there is no single source of truth. The first step is always the same: build the chart of accounts and customer and supplier lists from scratch, then enter opening balances at a clean cutover date. Do not try to import the spreadsheet history; enter opening balances and go forward clean. This was the shape of the spreadsheet-to-platform work I did in fund accounting.
Older On-Premise or Sector Systems → Cloud
Businesses on older desktop packages or sector-specific systems. These take longest because the data formats are often undocumented, the person who set the system up has left, and custom fields may only be reachable through the database. Use someone who has worked with that particular system before; general consultants tend to underestimate how hard the data is to extract.
4. What Data to Migrate (and What to Leave Behind)
The instinct is to migrate everything. Do not. Think about it like this:
- Chart of accounts: redesigned for the new system, not copied line for line
- Open purchase ledger: unpaid supplier invoices and credit notes
- Open sales ledger: unpaid customer invoices and unallocated receipts
- Customer records: active customers with correct contacts, payment terms and VAT status
- Supplier records: active suppliers with VAT number, payment terms and verified bank details
- Fixed asset register: current assets with cost, accumulated depreciation and remaining useful life
- Current-year transactions: nominal ledger detail for the current financial year
- Historical transactions (1 to 3 years): summary journals by month, not individual transactions
- Job costing data: open jobs only; closed jobs as summary records
- Payroll history: year-to-date figures for the current tax year, which you need for Real Time Information (RTI) submissions and P60s; earlier years usually stay in the payroll software
- Stock: current quantities and costs; historical movements stay in the old system
- Transactions older than 3 years: keep read-only access or an export instead
- Inactive customers and suppliers: if they have not traded with you in 2 years or more, leave them behind
- Voided or deleted transactions: they serve no purpose in the new system
- Duplicate records: clean them out; do not migrate the mess
- Custom reports: rebuild them in the new system with its own tools
A manufacturer moving from desktop accounting software to a cloud system might migrate 180 nominal accounts (consolidated from 340), 1,200 active customers (out of 3,400 on file), 450 active suppliers (out of 1,800), all open purchase and sales ledger items, current-year transactions and monthly summary balances for the prior two years. That is a small fraction of the records in the old system. Loading everything would take far longer and bring thousands of data problems with it.
5. Data Cleaning Checklist
Print this out. Do not start loading data into the new system until every high-priority item is done:
| Item | Action | Priority |
|---|---|---|
| Duplicate suppliers | Merge duplicates and standardise names (e.g. "Smith & Sons Ltd", not "SMITH AND SONS LIMITED") | 🔴 High |
| Duplicate customers | Merge duplicates, pick one correct name, combine transaction history | 🔴 High |
| Inactive nominal accounts | Mark inactive or merge into parent accounts; do not migrate zero-balance accounts with no activity in 2 years or more | 🔴 High |
| VAT numbers and bank details | Check supplier VAT numbers and confirm bank details through a known contact before they go into the new system; a migration is a common moment for payment fraud | 🔴 High |
| Stale open invoices | Review sales and purchase ledger items older than 90 days; write off debts you will not collect and settle disputed supplier items | 🟡 Medium |
| Payment terms | Standardise across suppliers (30 days, 45 days and so on); correct terms that do not match what actually happens | 🟡 Medium |
| Address data | Check and standardise customer and supplier addresses; fix postcodes and country formats | 🟡 Medium |
| Account coding errors | Run a trial balance and investigate anything odd; correct miscoded entries before migration | 🟡 Medium |
| Intercompany balances | Reconcile both sides; start the new system with agreed intercompany positions | 🔴 High |
| Bank reconciliation | Reconcile every bank account up to the cutover date; nothing unexplained should carry over | 🔴 High |
6. Timeline Expectations
These are rough planning ranges for the data migration part only, not the full system implementation, which takes longer. Set expectations with your board accordingly:
| Complexity | Description | Data Migration Timeline |
|---|---|---|
| Simple | One company, one currency, clean books, fewer than 500 active customers and suppliers | 4 to 6 weeks |
| Moderate | Several departments, moderate transaction volumes, some data problems, basic job costing | 8 to 12 weeks |
| Complex | Several companies or currencies, an old system with poor documentation, heavy customisation, over 1,000 active records | 3 to 6 months |
The biggest timeline risk is rarely technical; it is data cleaning. Teams regularly underestimate how long it takes to de-duplicate 2,000 supplier records or reconcile three years of intercompany balances. Allow 30 to 40% of the data migration timeline for Phase 1 (Audit and Clean). If you skip it, you spend that time, and more, fixing problems after go-live.
7. Where the Cost Goes
Data migration costs are often hidden inside the implementation fee. When you plan the budget, account for each of these:
| Cost Category | Simple | Moderate | Complex |
|---|---|---|---|
| Internal team time (rough planning range) | 80 to 120 hours | 200 to 400 hours | 500 to 1,000+ hours |
| Paying for both systems at once | 1 to 2 months | 2 to 4 months | 4 to 6 months |
| Implementation partner or migration specialist | Get a fixed quote for the data work separately from configuration and training | ||
| Conversion or data clean-up tools | Often needed when exporting from desktop or older systems; check before you sign | ||
| Training | Budget for it explicitly: untrained users are the main cause of post-go-live errors | ||
Internal time is the cost most businesses underestimate. Your finance lead will spend a large part of their week on the migration for weeks at a time, and the people on the sales and purchase ledgers will be pulled into checking data. If you do not cover their other work, something else suffers, usually the monthly close.
8. When to Bring in Outside Finance Help
- You do not have a finance director or experienced financial controller: someone has to own the chart of accounts redesign, check the field mapping and sign off the reconciliations. That is not a bookkeeping task and the implementation partner will not do it. It is a finance decision, not an IT one.
- You are migrating while the business changes: if you are raising finance, preparing for a sale or growing fast, the new system has to serve the reporting you will need, not just copy the old structure.
- Your implementation partner is making decisions they should not: implementation partners are good at configuring software. They are not the right people to decide whether your chart of accounts should support profit and loss accounts by site, or how the board wants overheads allocated. That is a finance decision.
- A previous attempt failed: if you have tried once and the numbers do not agree, bring in someone who has done it before. BlackpeakCFO runs accounting system migrations as a fixed-price project from £995, which is small next to the cost of a second failed migration.
- Several companies need consolidating: intercompany eliminations, recharges between companies and group reporting need finance knowledge most implementation teams do not have in enough depth.
If you are not yet sure whether you need a new system at all, start with the fixed-price Finance Systems Audit (£1,995). It looks at how your finance work gets done, the spreadsheets you depend on and the software you pay for, and gives you written findings and a plan, which may well be to get more out of the system you have.