Talk about a new system usually centers on screens and modules, while one important question is put off until the final weeks: what do we do with the data that sits in Excel files today? Customer lists, warehouse items, and receivable balances are all scattered across files written by different people over the years.
A new system is only useful on its first day if it starts with correct data. If the data is migrated as it is, the errors and duplicates move with it, and the team loses its trust in the system before getting used to it.
In the article “Six signs your organization needs a custom ERP system,” we noted that data migration needs early planning. Here we set out a practical plan for that migration, one that applies to any custom system built module by module.
Why data migration is a project in its own right
Migration may look like a copy-and-paste job at the end of the project. In reality, it is a small project with its own deliverables, owners, and schedule, and it needs decisions the technical team cannot make on its own.
Migration also needs time from the data owners themselves, who are usually the employees busiest with day-to-day work. If their time isn’t planned for early, the review gets pushed back until the launch date is pressing on everyone.
The difficulty comes from the fact that Excel files are more flexible than any system can allow. A cell accepts any value, and the same column may hold a number in one row and a note in another. A system, on the other hand, enforces business rules: every customer has a code that is never repeated, every item has a unit of measure, and every entry is posted to an account that exists.
That is why we recommend treating migration as a track that runs alongside development and starts with the discovery session, not as a task added a few days before launch. Every module that is built needs its data ready when it goes into operation.
What gets migrated and who owns it
Listing the files and their owners
Start with a single list of every file holding data that will move to the system: the file’s name, where it is kept, who updates it, when it was last updated, and what it contains. You will often find several copies of the same file, each with an owner who considers it the correct one.
Each type of data has a single owner who decides what is correct: sales for customers, the warehouse for items, and accounting for accounts and balances. That person, not the technical team, is the one who reviews the data after cleaning and approves it.
Master data and historical records
Master data is what the system relies on in every transaction: customers, suppliers, items, and the chart of accounts. It is migrated complete and clean, because every invoice and every stock movement will refer to it.
You also need to decide what happens to documents that are still open on the go-live date: orders not yet delivered, invoices not yet collected, and pending purchase orders. These are migrated as open documents, not just as balances, because work on them will continue in the new system.
Historical records, meaning past years’ invoices and transactions, are a different matter: whether to migrate them needs discussion. In many cases it is enough to migrate the opening balances and keep the old files as a reference to consult.
Suppose a trading company keeps many years of sales history in scattered files. Migrating all of it means cleaning a great many rows that no one will edit from now on. The simpler alternative is to migrate customer and inventory balances as of the go-live date and keep the old files available to read.
Cleaning the data and mapping columns to system fields
Cleaning the data
Cleaning is where most of the time goes, and it needs written rules before it starts. Four problems come up most often:
- Duplicates: the same customer recorded twice under two slightly different names, or with two different phone numbers.
- Inconsistent names: the same item written in more than one form, or in Arabic in one file and in Turkish in another.
- Inconsistent units: an item recorded by the piece in one file and by the box in another, with no clear conversion factor between them.
- Empty fields: customers with no tax number or address, and items with no cost or no unit of measure.
Each of these problems calls for a decision by the data owner: which of two duplicate records stays, what the approved form of a name is, what the base unit of each item is, and what to do with an empty field: fill it in, leave it, or exclude the whole record.
Suppose a warehouse buys an item by the carton and sells it by the piece, and the old files recorded quantities in both units without telling them apart. Before migration, a single base unit has to be chosen and every balance converted to it; otherwise the system will show more or less stock than there really is.
Mapping columns to system fields
After cleaning, prepare a simple mapping document: each column in the old file next to the field it will go to in the system, with the conversion rule if there is one. Columns with no match are discussed one by one: is this information the business really needs, or an old habit that can be dropped?
Suppose the customer file has a column called “Notes” where sales reps write the payment terms, the credit limit, and the contact person’s name all together. That one column may turn into three fields in the system, and it needs a manual read-through that no automatic conversion can replace.
The trial migration and go-live day
A trial migration on a copy
Nothing is migrated into the live system before a full trial on a copy of it. The cleaned data is loaded, then its owners compare the totals: the number of customers, total debit and credit balances, and stock quantities in each warehouse along with their value.
If the totals don’t match, the cause is investigated before any other step. The trial may be repeated more than once until the numbers match and the data owners are confident in the result.
Alongside the totals, the team tries real transactions on the migrated data: issuing an invoice to a migrated customer, releasing an item from the warehouse, and pulling an account statement for a supplier. These tests reveal what the totals don’t, such as a wrong unit of measure or an account linked in the wrong place.
Go-live day
A freeze date is set, after which nothing more is added to the old files, and the opening balances are calculated as of that date: customer and supplier balances, cash boxes, and inventory by quantity and cost.
The period between the freeze and the start of operation must be short and known to everyone. Any sales or receipts during that period are recorded on an agreed temporary form, then entered into the system on its first day of operation, so that no transaction gets lost between the two systems.
After the move to the new system, the old files stay read-only in a known location, so that no one goes back to recording in them out of habit. Each data owner signs off that the migrated balances in their area are correct, and that sign-off is what allows operation to begin.
Migration ends when the data owners sign off on the balances, not when the files finish loading.
The migration plan, step by step
- Assign an owner to each type of data, and one person responsible for the whole migration.
- List the files and data sources, and identify the approved version of each file.
- Decide which master data is migrated in full, and where opening balances are enough.
- Write the cleaning rules and apply them, and have the data owners review the results.
- Prepare the document that maps columns to system fields, with the conversion rules.
- Run a trial migration on a copy of the system, reconcile the totals, and repeat it until they match.
- Set the freeze date and announce it, and calculate the opening balances as of that date.
- Run the final migration, and make the old files read-only.
- Get sign-off from each data owner, then begin day-to-day operation.
A checklist before go-live day
- Every source file has a known owner and a single approved version.
- No duplicate customers, suppliers, or items remain after cleaning.
- Every item has a base unit of measure and clear conversion factors.
- Every column has a matching field in the system or a written decision to exclude it.
- The trial migration totals match the totals in the original files.
- The freeze date has been announced, and the team knows where to record transactions after it.
- The old files are kept read-only in a known location.
- The data owners have signed off on the opening balances.
Migration isn’t the part of the project that draws attention, but it is the part that decides whether the team trusts the system in its first week. The earlier it starts, the fewer surprises there are before launch.
You can discuss your situation with the ITS team, so that together we can review your current files and decide what moves into the system and what stays as a reference.
Related serviceCustom ERP systems