Migrating Data to Odoo from Spreadsheets or a Legacy ERP

Data migration is the part of an ERP project that most often runs late and most often produces unpleasant surprises after go live. It is also the part where the client team has the most influence, because only you know what the data means. This guide sets out the method we use and the decisions you will need to make.

Decide what moves

The first decision is scope. Not everything in the old system belongs in the new one. Divide the data into three groups.

Master data describes the things your business deals with: customers, suppliers, products, the chart of accounts, employees, warehouses and locations. This moves, in cleaned form, and it moves first.

Open transactions are the things that are still in progress at cut over: unpaid invoices and bills, open sales and purchase orders, stock on hand, undelivered orders, running projects, leave balances. These move as opening positions so that work can continue.

History is everything that is complete. Paid invoices from three years ago, closed orders, old stock moves. History is expensive to migrate, because every record must satisfy Odoo's rules and reconcile, and it is rarely queried after the first months. The usual answer is to keep the old system available in read only mode, or to export history to an archive, and to load only summary balances into Odoo.

Ask each department what they genuinely look up from past years and how often. The answers are usually narrower than the initial request to move everything.

Clean the master data

Master data is where the migration effort should go, because every transaction in the new system will reference it. The common problems are the same in every company.

• Duplicate customers and suppliers created over the years by different people with different spellings
• Products that are no longer sold but never deactivated
• Inconsistent codes, units of measure and categories
• Missing tax settings, payment terms and contact details
• A chart of accounts with accounts that were created for a single transaction and never used again

The method is to export each master data set to a spreadsheet, mark every record as keep, merge or drop, and correct the ones you keep. This is work for people who know the business, and it takes longer than anyone expects. Start it in the first week of the project, not the last.

Map to Odoo

Once the data is clean, each field in the source is mapped to a field in Odoo. Some mappings are direct. Others require a decision: which Odoo product type does this item become, which account does this old code correspond to, which payment term applies to customers who had none. Some source fields have no home in Odoo and will be dropped or placed in a custom field with a good reason.

We produce the mapping as a document you review, because the decisions in it are business decisions rather than technical ones.

Rehearse the load

Data is loaded into a test database, checked, corrected and loaded again. Plan for at least two full rehearsals before go live, and expect the first to reveal problems in both the data and the mapping. Each rehearsal produces a list of errors and a list of corrections, and the source data is fixed at its origin rather than patched in the load file, so that the final load is clean.

Reconcile

A migration is finished when the numbers agree. For each area, define the checks before the load, run them after it, and record the result.

• Accounts receivable and payable: the total of open items per customer and supplier matches the old system
• General ledger: trial balance by account matches at the cut over date
• Stock: quantity and value per product per location match the count taken at cut over
• Open orders: count and value match, and each order can be processed in Odoo
• Master data: record counts match the approved keep list, and a sample of records is checked field by field

Where a check fails, the cause is found and fixed. Where a difference is accepted, the reason is written down. The reconciliation report is signed by the person who owns the data, and it is the evidence that the migration is complete.

Plan the cut over

Cut over is the period between the last transaction in the old system and the first in the new one. It should be as short as possible and as calm as possible. A typical plan freezes the old system at close of business, runs the final extraction and load overnight or over a weekend, completes the reconciliation, and opens Odoo to users with the results already checked. A rollback plan exists in case the reconciliation fails, and the old system remains accessible in read only mode for an agreed period.

The roles you need on your side

A data owner per area who decides what stays and approves the reconciliation. People with time to clean spreadsheets, and this is the largest commitment. Someone who knows the old system well enough to extract from it. A tester per department who will run their processes on the migrated data in each rehearsal. Without these, no supplier can complete the migration on time.

Starting now

Cleaning master data can begin before an ERP project is confirmed and is never wasted. If you are considering Odoo and want a view on what your migration would involve, we are glad to look at a sample of your data in a first call.