From Spreadsheets to ERP: Migrating Data Without Losing It

Most companies moving from spreadsheets to ERP expect the software installation to be the hard part. It is not. The hard part is turning years of accumulated data into something the new system will accept — and that work usually consumes more than half the project. Skip it, and your new system produces wrong numbers from day one.

This guide approaches the migration from the data side. The order matters; swapping steps creates errors that are difficult to reverse later.

Step One: Decide What Moves

You do not have to migrate everything, and you should not. Sort into three groups:

• Migrate: Active products, active customers, current stock quantities, open orders, open balances.
  
• Archive: Transaction detail from closed years. Keep a read-only copy rather than loading it into ERP.
  
• Leave behind: Products with no movement in two years, duplicate customer records, test entries.
  

Migrating old transaction history is tempting but expensive. If data quality is poor, historical reports will be wrong too, and trust in the system disappears in the first week. Keeping history in an archive and starting ERP clean is healthier.

Step Two: Clean Product and Customer Records

Spreadsheet-grown lists share the same ailments:

• Duplicates: "ABC Ltd.", "ABC Limited" and "abc ltd" sit as three separate customers. Merge on tax number.
  
• No coding standard: If product codes were created under different logics, search and grouping will not work. Agree one coding rule before migration.
  
• Unit confusion: If the same product is recorded sometimes in pieces and sometimes in cases, stock quantity becomes meaningless. Pick one base unit per product and define conversion factors separately.
  
• Empty mandatory fields: Missing tax rate, unit or category will stop the load.
  

Step Three: Choose the Cut-Over Date

• Best: the start of a fiscal year. Opening balances form naturally and comparisons stay clean.
  
• Second best: the first day of a month. Mid-month cut-over makes reconciliation painful.
  
• Never during peak season. A team cannot learn a new system and run your busiest weeks at the same time.
  

Step Four: Build Opening Balances

• Stock: Quantities verified by a physical count, with unit costs. Opening stock entered without a count corrupts your first cost report.
  
• Customer and supplier balances: Ideally broken down by invoice. A single lump-sum balance makes later payment matching impossible.
  
• Cash and bank: Real balances at cut-over, reconciled against statements.
  
• Open orders: Sales orders not yet shipped and purchase orders not yet received.
  

Step Five: The Parallel Run

Running critical transactions in both systems for a month lets you catch errors under control rather than in production. The rules:

• Keep it to one month. Longer, and the team starts using both systems carelessly.
  
• Compare three numbers weekly: total sales, total stock value, total customer balance.
  
• When a difference appears, find its source before continuing.
  
[*]Announce the end date upfront and make the old system read-only on that date.

Common Mistakes

Carrying old habits into the system. Insisting "we have always done it this way" and using ERP like a spreadsheet cancels the benefit of migrating.

Moving everyone at once. Starting with one department and carrying the lessons to the others is far less risky.

Loading without a backup. Take a database backup before every bulk load. One mismapped column can corrupt thousands of records.

In Short

Moving from spreadsheets to ERP is a data project, not a software installation. Deciding what not to migrate matters as much as deciding what to migrate. Pick the cut-over by the accounting calendar, verify opening balances with a physical count, and keep the parallel run short but disciplined.

We can review your current spreadsheets and scope the work needed to make them migration-ready. Get in touch to discuss your processes.