Data cleansing before a migration means getting your records right before anything moves. Find duplicate customers, suppliers and items, merge them into one record each, and archive or exclude anything inactive. Then standardise item codes, map your chart of accounts to the new structure, and have the right person sign off each clean list. Do this in your old system or a staging copy before you migrate. If you skip it, the new system starts with the same mess. It's also harder to fix once transactions have been posted against the wrong record.
Whether you're moving from MYOB, Reckon, an Access database or a set of spreadsheets into Xero, MYOB Advanced, NetSuite or a CRM, the method is the same. Here's how we approach it.
Why duplicate records build up in older systems
Duplicates rarely come from one big mistake. They build up over years of normal work:
- Someone can't find a customer quickly, so they create a new one.
- A business changes its trading name, registers a company or moves address, and a new card gets created instead of the old one being updated.
- Two people enter the same supplier with different spelling or punctuation.
- Records are imported from a spreadsheet or an old CRM without checking what already exists.
- A product is re-ordered from a new supplier and given a new item code, even though it's the same thing.
For example, if your file has "ABC Plumbing", "A.B.C. Plumbing Pty Ltd" and "ABC Plumbing (do not use)", there's a fair chance they're all one business. Each one may still have invoices, payments or credit notes sitting against it. That history is why you can't simply delete the extras.
How to find duplicate customers and suppliers
Matching on names alone will miss a lot and flag things that aren't really duplicates. Match on the fields that identify the business, then use names as a secondary check.
- Export everything. Pull customers, suppliers and items into a spreadsheet or staging database, including inactive records and the record ID from the old system.
- Normalise the names. Make a working copy of the name field. Remove punctuation, convert to lower case, and strip common words like "pty", "ltd", "the" and "trading as". "A.B.C. Plumbing Pty Ltd" and "ABC Plumbing" then become the same string.
- Match on ABN. The ABN is the strongest identifier for Australian businesses. Remove the spaces so every ABN is a plain 11-digit number before comparing. Two records with the same ABN are almost always the same entity. Branches and divisions of one company are the main exception.
- Match on email and phone. Compare email domains as well as full addresses, and strip spaces, brackets and country codes from phone numbers before matching.
- Review the shortlist. Software, including AI tools, can do the first pass and suggest likely matches. A person who knows the customers should make the final call on each one.
The output should be a simple list with a "keep" record, the records to merge into it, and a note on why.
Merge, don't delete, records that have history
If a duplicate has transactions against it, deleting it either won't be allowed or will damage your history. Merge it into the record you're keeping instead. Most accounting systems and CRMs have a merge or combine function. Xero, for example, lets you merge contacts. When you merge, the transactions move to the surviving record and your reports still add up.
Merging in the old system before migration is usually cleaner than merging in the new one. Your history then arrives already attached to the right customer or supplier.
Before you merge, decide which record "wins" for each field. The newest address and the correct legal name usually win. Payment terms and bank details deserve a careful check, because getting supplier bank details wrong is an expensive mistake. Confirm them against a recent invoice or remittance rather than trusting whichever record looks newest.
Deal with inactive records before you move them
Most older files carry customers who haven't bought in years, suppliers you stopped using long ago, and items you no longer stock. Decide what happens to them before migration day:
- Not migrated: inactive with no open balance and no recent transactions. They stay in the archived old file.
- Migrated as inactive or archived: still needed for history in the new system.
- Migrated as active: anything with an open balance, open orders or recent activity.
Agree on a cut-off with your accountant, such as no activity since a particular date. Applying the rule consistently matters more than the exact date chosen.
Clean up item codes and the chart of accounts
Items cause just as many problems as contacts. Look for the same product under two codes, codes that changed format over the years, and inconsistent units of measure (each, box, carton). Pick one coding convention, map old codes to new ones in a spreadsheet, and keep that mapping. You'll need it for reconciling stock on hand and for anyone searching old records later.
The chart of accounts needs the same treatment. Map every old account to an account in the new structure. Combine accounts that were only ever used once or twice, and remove ones with no balance and no purpose. Your opening balances depend on this mapping being right. Our guide to MYOB to Xero trial balance conversion covers that step in detail.
Who signs off the clean list
Data cleansing fails when nobody owns the decisions. Assign a person to each data set:
- Customers: whoever runs accounts receivable or the sales team
- Suppliers: accounts payable
- Items: operations, purchasing or the warehouse
- Chart of accounts and opening balances: your accountant or bookkeeper
Each owner reviews the merge list and the inactive list for their area and signs off. After that, freeze the old file. New customers, suppliers and items are created only through one person until migration is complete, so the clean list doesn't drift.
This is the kind of work we do as part of database integration and migration projects, and it's built into our accounting data migration work.
Frequently asked questions
Should we clean our data before or after migrating?
Before, wherever possible. Cleaning in the old system or a staging copy means history arrives attached to the right records. Fixing duplicates after go-live means untangling transactions in two places.
Can software find duplicates automatically?
Software can find likely matches quickly, especially on ABN, email and phone. It can't know that two similar names are actually different businesses, or that a renamed company is the same customer. The practical approach is for tools to do the first pass and a person to approve each merge.
How much history should we bring across?
That depends on your reporting needs and what your accountant recommends. Some businesses bring opening balances and open transactions only. Others bring several years of detail. Either way, keep the old file archived and accessible, as the ATO generally expects business records to be kept for five years.
How long does data cleansing take?
It depends on how many records you have, how many systems they're spread across, and how quickly your team can review and sign off. The review and sign-off step often takes longer than the technical matching, so plan time for your people, not just the tools.
What does data cleansing cost?
Costs vary widely depending on the size of the business, how many systems are involved and how much needs to be custom-built. That's why we scope every project first and give you a fixed quote after a free initial chat. If you want a full written picture of your processes and systems before committing, our $5,000 Process & Systems Audit maps everything and produces a written report.
If you're planning a move to Xero, MYOB Advanced, NetSuite or a new CRM and you're not sure how clean your data really is, we're happy to talk it through. Book a free initial chat and we'll look at where your records are now and what needs fixing before anything moves.