ERP Data Migration

ERP data migration scope: master vs transactional data

The first decision in any ERP data migration is what to bring across. The answer is rarely "everything". I split the data into categories and agree a rule for each:

• Master data: customers, suppliers, items and services, price lists, bills of materials, chart of accounts, employees, warehouses and locations. This is almost always migrated, after cleansing.
• Open transactions: unpaid invoices and bills, open sales and purchase orders, outstanding quotes, open projects and work orders. These are needed to keep operating from day one.
• Balances: the trial balance, stock on hand and values, bank balances and any open advances or deposits at the cutover date.
• Historical transactions: closed invoices, past orders and old journal entries. These are the most expensive to migrate and often the least used.

For history, there are usually three options: migrate full detail, migrate summarized balances by period, or leave history in the old system and keep it accessible for reference and audit. The right choice depends on reporting needs, legal retention rules and how often people genuinely look things up. I help you decide based on actual use, not on fear of losing something. This decision is often the single biggest factor in migration effort, so I make it early and record it in the scope.

Migrating from Excel, QuickBooks, Tally and legacy systems

Each source system brings its own challenges, and the plan should reflect them.

Excel and shared spreadsheets. Often the hardest source, because there is no enforced structure. The same customer may appear under three spellings, item codes may be inconsistent between files, and important information sits in comments or colors. Cleansing here is mostly business work, and it needs the people who maintain the sheets.

QuickBooks and similar accounting tools. Accounting data is usually well structured, but the chart of accounts, tax codes and item lists may not match how the new ERP is designed. Classes, locations or custom fields may need to map to dimensions, departments or cost centers.

Tally. Ledger groups, stock groups and voucher types need careful mapping to the new chart of accounts and item structure. Tax data and party ledgers often need cleanup before they fit a more structured ERP model.

Legacy ERPs and in-house systems. These can hold years of data in custom tables. The challenge is understanding what each field actually means, which often requires talking to the people who built or used the system for a long time.

Whatever the source, I keep the target design in charge. Data is reshaped to fit the new processes, not the other way round. The ERP migration checklist summarizes the steps for each stage.

Cleansing and mapping templates

Data cleansing is a business task supported by tools, not a technical task the IT team can finish alone. Only the sales team knows which customer records are real, and only the warehouse knows which item codes are still in use. I organize the work so it is achievable alongside normal operations:

• Profile each data set to show duplicates, blanks, invalid values and dormant records.
• Agree cleansing rules, for example how to merge duplicate customers or which suppliers to close.
• Assign each data set to a named owner with a deadline.
• Track progress in a simple cleansing log reviewed in project meetings.

Mapping templates make the migration repeatable. For each object, such as customers, items or open invoices, the template shows:

Templates are reviewed by both the data owner and whoever configures the ERP, so mismatches between data and design surface early. They also feed directly into the ERP solution design when the data reveals a design gap.

Trial loads, reconciliation and opening balances

No migration should go into production without rehearsal. I plan several trial loads into a test environment, usually starting with master data, then open transactions, then balances. After each load, the results are reconciled and the issues fixed in the source data, the templates or the load scripts.

Reconciliation is how a migration is proven. The checks I use include:

• Record counts by object and type, source against target.
• Control totals such as total receivables, total payables, total stock value and quantity by warehouse.
• Sample checks where data owners open specific records in the new ERP and compare them with the source.
• Financial tie-out of the trial balance and subledgers against the closing position in the old system.

Opening balances need close coordination with your finance team and external accountant. We agree the cutover date, confirm the closing trial balance, and decide how to load receivables and payables at invoice level so aging and collections work from day one. Stock quantities and values are tied to a physical count or a confirmed stock report.

Each data owner signs off their reconciliation. Those sign-offs become part of the go-live decision, alongside test results from ERP testing and UAT. Users also test with migrated data, which tends to expose issues that clean test data hides.

Cutover planning and why an independent view helps

The final migration happens during the cutover window, which is usually short and under pressure. A good cutover plan lists every step with an owner, a planned time and a check:

1. Freeze transactions in the old system, or define which ones will be keyed manually afterwards.
2. Run the final extract using the same scripts as the last rehearsal.
3. Load master data, open transactions and balances in the agreed order.
4. Run reconciliation checks and get owner sign-off.
5. Set the old system to read-only and archive it for reference and audit.

The rehearsals tell you how long each step takes, so the cutover can be scheduled around month-end, peak seasons and business holidays. I align the migration cutover with the wider go-live plan and the rollback position.

An independent ERP data migration consultant helps because the incentives are clean. Implementation partners sometimes scope migration narrowly and assume the client will provide clean, formatted data. Internal teams are usually too busy to own it properly. I sit between both, making sure the scope is realistic, owners are clear and reconciliation is done thoroughly, so nobody discovers the problems in the first month-end close. If you are earlier in the process, my ERP implementation service covers how migration fits into the wider plan.