The ERP data migration checklist: zero-loss in practice

Ask anyone whose ERP project overran and the story usually turns to data: the customer master that existed in four versions, the item codes nobody could explain, the opening balances that would not reconcile. Migration is not a technical afterthought; it is a project inside the project, and it starts on day one.

Phase 1: scope and ownership

1. List every data object: customers, suppliers, items, BOMs, price lists, open orders, open invoices, balances, history.
2. For each, decide: migrate live, load as opening balance, or archive for reference.
3. Name one business owner per object. Not IT: the person who can say 'this customer list is correct'.
4. Agree the cutoff strategy: what happens to transactions created between extract and go-live.

Phase 2: clean at the source

1. Deduplicate customers and suppliers; agree naming conventions and coding schemes.
2. Standardise TRNs, phone formats and addresses; the 2027 e-invoicing mandate will punish dirty tax records, as our readiness page explains.
3. Kill zombie items: anything with no stock and no movement in 24 months gets archived.
4. Resolve negative stock and unmatched receipts before extraction, not after.

Moving off Excel: when the source is a set of spreadsheets

Many UAE businesses are not leaving another system; they are leaving a folder. The customer list lives in one workbook, stock in another, price lists in whoever last emailed one, and the real receivables position in the accountant's head. Excel migrations fail in different ways from system-to-system migrations, so they get their own steps.

1. Inventory the sheets. List every workbook that holds master data or open balances, who edits it, and when it was last trusted. Expect more copies than anyone admits to.
2. Pick one master per object. Where three versions of the customer list disagree, the business owner decides which wins and the others are retired, not merged by formula.
3. Strip the formatting logic. Merged cells, colour-coded status, notes in comments and totals rows mean something to a person and nothing to an import template. Turn each into a real column or drop it.
4. Fix types before you load. Dates typed as text, TRNs stored as numbers that lose leading digits, AED values with currency symbols inside the cell: each one becomes a rejected row.
5. Rebuild what the spreadsheet was calculating. Reorder levels, price tiers and credit limits often exist only as formulas. They become configuration in the new system, so write them down before the workbook is archived.
6. Freeze the sheets at cutoff. From the extract date, the spreadsheets go read-only. Two live sources of truth during trial loads is how reconciliations never close.

The payoff is real. At a multi-branch UAE food retailer we implemented, replacing branch-by-branch stock records with one live stock position cut order processing time by 60 percent. That number came from the operating change, but it was only possible because the stock data loaded clean on day one.

Phase 3: map and trial

1. Build a field-by-field mapping document; every target field has a source or an explicit default.
2. Trial load one: expect failures, log every rejection, fix at source, re-extract.
3. Trial load two: full volume, timed, with the reconciliation report drafted.
4. Freeze the mapping. Late mapping changes are how 'one more fix' becomes three more weeks.

Phase 4: reconcile and sign

1. Counts match: customers, suppliers, items, open documents, to the row.
2. Values match: AR, AP, stock valuation and trial balance against the source, to the fils.
3. Spot-check depth: ten real records traced end to end by the business owner.
4. Finance signs the reconciliation. That signature is the UAT gate; nothing goes live without it.

This is unglamorous work, which is exactly why it separates implementations that land from those that limp. It is also why we put validated migration inside the fixed price rather than selling it as a change request later.