ERP

How to Clean Up Financial Data Before an ERP Migration in 2026 (Government Guide)

Data cleanup before ERP migration, step by step for 2026: how cities, counties and districts clean vendors, open items, history and GL balances by fund.

Chris Rodney 17 min read
On this page
  1. Key takeaways
  2. What is ERP data cleanup?
  3. How to clean up financial data before an ERP migration in 2026: 8 steps
  4. Should you clean data in the old system or the new system?
  5. When should data cleanup start?
  6. Data cleanup before ERP migration: 2026 sign-off checklist
  7. FAQ
  8. Final words

Data cleanup before ERP migration means fixing, closing out or retiring records in your legacy system before they are extracted, so only accurate, needed data moves to the new platform. For a city, county, school district or utility, that covers five areas: vendors, customers, open items, history depth and fund-level GL balances. Each one needs a named owner and a signed reconciliation.

Key takeaways

Assign one business owner per data set before anyone writes a conversion script.

Clean in the legacy system wherever you can. Washington’s statewide ERP program made legacy cleanup an agency job that happens before conversion.

Retire dormant vendors and merge duplicates first. Madison, WI found 9,927 of its 19,629 active vendors had no transactions in over four years.

Close or resolve open items (AP, AR, POs, encumbrances, stale checks) instead of converting the mess.

Let your records retention schedule, not habit, decide how much history converts. Keep the rest read-only.

Tie the legacy trial balance by fund before extract, then reconcile every mock conversion to it.

Your ERP vendor will load whatever you hand it. Bad vendor records and ancient open POs will arrive in the new system on schedule, looking brand new.

Washington State’s One Washington data conversion strategy put the responsibility plainly. Before conversion, agencies “will perform legacy system data clean up, reconciliation and any data extraction required for conversion.” Your city or county works the same way, just at a smaller scale.

KCT is a public-sector ERP advisor and implementer, and our team leads government ERP data conversion on multi-module city programs.

Most cleanup guides are written for private companies and skip fund accounting, encumbrances, unclaimed property and retention. Below is an 8-step method built for government finance teams, plus a history depth table and a sign-off checklist.

What is ERP data cleanup?

ERP data cleanup is the work of correcting, completing, de-duplicating and retiring legacy records before extraction, so the data loaded into the new system is accurate, needed and owned. Cleansing fixes the data; conversion maps and loads it. You need both, but cleansing has to lead.

Washington’s statewide program splits ERP data cleansing into two buckets. Required cleansing covers fields the new ERP needs to function. Recommended cleansing covers consistency items such as name case, phone formats and address corrections.

That split tells you what can wait. A vendor with no remit-to address will fail a load, while a vendor name in all caps will not. Sort every known issue into “required” or “recommended,” and let the required items gate the first mock conversion.

Why government data needs more cleanup than most

Public agencies carry decades of records, fund accounting, grants and retention rules, so legacy files run bigger and messier.

Take the City of Madison’s own internal audit of its vendor master file, issued in June 2025. Madison runs Munis.

As of January 31, 2025, the file held 20,880 vendors, and 9,927 of the 19,629 active ones had no recorded transactions for over four years. The auditors also found six duplicate active vendor records.

Management’s response shows what cleanup looks like in practice: 8,308 vendor records were moved to Stop status in April 2025.

Duplicates cost real money. A May 2026 Richmond, VA City Auditor review listed duplicate vendor records among the common causes of duplicate payments. Across FY 2024 and FY 2025, the review identified 50 duplicate payments totalling about $5.76 million, most of it one $5.09 million wire processed twice.

About $5.61 million had already been recovered before the review. The other 27 duplicates, worth $151,730, had not been caught until the auditors found them.

To gauge your own exposure, run three counts this week:

Active vendors with no payments in three or more years (KCT rule of thumb for “idle”).

Customer or utility accounts with zero activity and zero balance.

Open items older than one full fiscal year.

How to clean up financial data before an ERP migration in 2026: 8 steps

To clean up financial data before an ERP migration, follow the 8-step process KCT recommends for a 2026 government ERP data conversion:

1

Name a business owner for every data set and set cleanup rules in writing.

2

Clean the vendor and customer master files.

3

Close out or resolve open items before cutover.

4

Decide how much history to convert, based on your retention schedule and audit needs.

5

Tie out GL balances by fund before you extract.

6

Clean the code values that everything else points to.

7

Reconcile every mock conversion and log every fix.

8

Freeze the legacy system and run the final extract.

Step 1. Name data owners and set the rules

Cleanup stalls when IT owns it alone. Every data set needs a finance or department owner who decides what “right” looks like and signs off.

The state’s strategy assigns cleansing to “legacy system technical staff and business owners” together, with a functional team keeping a data cleansing log. IT can pull the data. Only the AP supervisor knows which “Acme Supply” record is real.

Build a simple owner table before anything else:

Swipe to see all columns →

Data setBusiness ownerTechnical extractorCleanup ruleSign-off
VendorsAP or procurement leadLegacy system analystMerge duplicates, retire idle vendors, validate TINsAP lead
Customers and utility accountsUtility billing or revenue managerBilling system analystClose final-billed accounts, resolve credits and depositsRevenue manager
GL and fund balancesAccounting manager or controllerFinance system analystTrial balance ties by fundFinance director
Fixed assetsAccounting, with departmentsFinance system analystRegister ties to capital asset schedulesController
GrantsGrants managerFinance system analystConvert active grants with balances onlyGrants manager

Write those roles and history years into your contract. Our guide to the public sector ERP implementation SOW shows where conversion responsibilities belong. If your vendor is Tyler, the “under-scoping data conversion” section of our Tyler Munis implementation mistakes post covers how those duties usually split.

Step 2. Clean the vendor master and customer master files

Master data is the parent of every transaction. Fix it first, so open items attach to the right record.

Madison’s auditors recommended merging the duplicate vendors with the AP Vendor Merge utility in Munis and flagged the idle vendors for status review and cleanup. The same vendor file tracks IRS Form 1099 status, so a bad record does not just risk a duplicate payment. It risks a wrong tax filing too.

Here’s your vendor checklist:

Merge duplicates. Match on TIN, bank account and remit-to address, not just name.

Inactivate idle vendors. KCT rule of thumb: no payment in three years and no open item or active contract means the vendor stays behind.

Validate TINs. The IRS TIN Matching program lets eligible payers check name and TIN combinations before they file information returns. Use it if your agency is eligible.

Confirm 1099 flags on every vendor you convert.

Re-verify bank details and remit-to addresses through your normal controlled process, since conversions invite fraudulent change requests.

Fill required fields the new ERP will reject if blank.

Retiring a vendor in the new system does not mean destroying its paperwork. Washington’s local government retention schedule (CORE, version 5.0) keeps vendor payment information such as W-9s and EFT forms for six years after final payment. Check your own state’s schedule.

Customer and utility accounts need the same treatment. Merge duplicate customers, close zero-balance inactive accounts, refund or apply credit balances, reconcile deposits to the GL liability and confirm every active account has a valid rate code.

Step 3. Close out open items before conversion

Every open item you convert has to be tracked and explained in the new system. Carry fewer across.

The Washington State Auditor’s 2024 One Washington audit said it directly: “Not all data in the old system must be moved to the new system.” An assessment decides what moves and when.

The state’s scope tables show the pattern. Active grants convert with their balances, contracts with outstanding commitments convert with unreleased balances, and open AR converts, including credit balances.

Outstanding checks deserve special attention, because unclaimed property law applies. In Texas, the Comptroller’s 2026 unclaimed property reporting instructions say stale checks written by local officials go to the local treasurer or the Comptroller after a three-year dormancy period.

Texas counties, cities and school districts must also run a local unclaimed property program for small amounts they report locally. Other states set different rules, so check yours.

Build an open item list with an owner and a target date for each line:

Stale POs and encumbrances
Close or cancel, and release the encumbrance with department sign-off.
Aged open AP invoices
Pay them, or void them with documentation.
Outstanding checks
Work the list and process unclaimed property under your state’s rules.
Small AR balances
Collect or write off under your adopted write-off policy and the council or board authority behind it.
Suspense and clearing accounts
Clear them to zero or document every remaining item.

Step 4. Decide how much historical data to migrate

Convert what the new system needs to operate and what staff look up daily. Keep the rest readable in an archive. Retention rules decide how long records are kept, not whether they live in the new ERP.

So what data should be migrated to a new ERP? Washington’s answer: active and current data usually converts, while inactive data depends on retention policy. HR, for example, keeps some data for an employee’s lifetime, so Washington planned to convert that history.

Unconverted legacy data stays available for inquiry for a period set by the system owner and record retention rules.

How many years of historical data should be migrated? There is no single number.

Washington’s CORE schedule keeps general financial transactions, including AP, AR, journals, reconciliations and check registers, for six years after the end of the fiscal year. That is a retention period, not a conversion requirement. A read-only archive can meet it.

The smart move is to decide per data set, in writing, early. When Mercer Island, WA was approving its Tyler Munis purchase in February 2022, staff told council the potential conversions had been scoped in the RFP and prioritized, each with a recommended number of years. Staff also said they would prioritize converting the data used for routine work.

Use this table as a starting point:

Swipe to see all columns →

Data setTypical conversion choiceKeep read-only or archiveBasis
GL balancesOpening balances by fund and account at cutover, plus budget and actuals for the current and prior fiscal year (KCT rule of thumb)Detailed journal historyBudget comparatives and reporting
Accounts payableOpen invoices and active vendorsPaid invoice and check historyState retention schedule (WA: 6 years after fiscal year end)
AR and utility billingOpen balances, deposits, credit balances and the consumption history billing needsOlder bill historyBilling rules and customer service
Purchasing and contractsOpen POs, encumbrances and active contracts with remaining balancesClosed POsOpen commitments (One Washington also converted completed POs within retention)
Grants and projectsActive grants with life-to-date balancesClosed grantsGrantor retention and closeout terms
Fixed assetsFull register: cost, accumulated depreciation, in-service datesDisposed asset historyCapital asset reporting
Payroll and HRActive and recently terminated employees, year-to-date totals, leave balancesOlder pay historyHR retention (some records kept for an employee’s lifetime)

Put two decisions in your SOW: whether GL history converts as summary or detail, and how long the read-only legacy system or archive stays available. Cover the full retention period.

Cleanup work lands on the same finance staff who still have to pay vendors, bill customers and close the month. KCT’s Full-spectrum ERP Implementation team can set up the data owner model, run the profiling and cleansing log, and reconcile each mock conversion alongside your software vendor.

Step 5. Tie out GL opening balances by fund before you extract

Your opening trial balance is only as good as the legacy one behind it. Reconcile it by fund before you extract anything.

Washington’s finance office planned to test its account mapping “using the balances from previously completed financial statements,” the State Auditor reported, because staff know what that data should look like. Audited numbers are the best answer key you have.

Work through this GL checklist with your accounting owner:

Every subledger ties to its GL control account by fund: AP, AR, utility AR, fixed assets, payroll liabilities and inventory.

Due-to and due-from balances net to zero across funds.

Suspense and clearing accounts are cleared or fully documented.

All audit adjustments from the last audited year are posted in the legacy system.

The cutover date is set. KCT rule of thumb: a fiscal year start or a quarter end, after the prior period has closed.

The old chart of accounts is mapped to the new one, and the mapping is tested against the last audited statements.

Clean opening balances make your first close far easier. See our guide to surviving your first month-end close on a new ERP.

Step 6. Clean the code values everything points to

Bad codes break loads even when the records are fine: inactive accounts, retired departments and obsolete object or rate codes.

The state’s strategy names the core cleansing tasks: make sure parent records exist, correct references to codes that no longer exist, eliminate nonsensical data and fill required data where legacy sets are incomplete.

List every code table you have: funds, departments, accounts, projects, rate codes, pay codes and asset classes. Mark each value keep, map or retire. Then confirm no open item still points at a retired code.

Clean security roles too. Madison’s audit found overlapping and dormant Munis access, including an AP user with both Superuser and AP Admin rights.

Step 7. Reconcile every mock conversion and log every fix

Cleanup is iterative. Each mock conversion exposes new errors, and you fix them at the source before the next cycle runs.

Washington’s statewide program planned four load cycles into a prototype, then a fifth and final cycle, with a higher success target each time. The first cycle aimed for 70 percent, or 700 of every 1,000 records.

After each cycle, the team tests the data, analyzes errors and fixes them in the legacy system, according to the State Auditor’s August 2024 report. Its strategy says cleansing should start before the first mock and continue through the final production conversion.

Data conversion reconciliation is where cleanup gets proven. For every mock, compare record counts and control totals, the trial balance by fund and each subledger aging against legacy, and have each owner review a sample. Log every defect with an owner, a fix location and a retest date.

That discipline is how KCT runs cutovers. On the City of Oxnard program, KCT led data conversion and split go-lives into Finance, then HCM, then EPL, with each cutover proofed and rehearsed before the next began.

Step 8. Freeze the legacy system and run the final extract

The state’s strategy also warns that last-minute legacy changes can surface errors your mocks never saw. Freeze, then copy.

The State Auditor described Washington’s plan to freeze payments and deposits in the legacy system at launch and to copy data rather than move it, leaving the original as a backup.

Your freeze checklist:

Set a freeze date for master file changes.

Schedule the final AP run, check cutoff, bill cycle and legacy payroll.

Keep the legacy system read-only and archive the original extracts.

Have each data owner sign the final reconciliation.

Once your data is clean and reconciled, prove the calculations with a parallel test before go-live.

Should you clean data in the old system or the new system?

Default to fixing data in the legacy system. Move a fix into the conversion program only for large, rule-based patterns. Fix in the new ERP only when conversion can safely go ahead without it.

One Washington’s strategy gives four timing options that translate well to any agency:

Before conversion
When legacy staff and lead time are available and daily business is not at risk.
During conversion
When an automated program can apply a clear rule.
Immediately after
When critical data must be fixed in the ERP before use.
Sometime after
When there is no urgency.

When should data cleanup start?

Data cleanup should start at contract signing at the latest, and ideally during selection. It then runs alongside design and through every mock conversion until the final extract.

Washington’s strategy has cleansing begin before the first mock and continue to final cutover. Mercer Island went earlier still, scoping conversion years in its RFP before a vendor was chosen.

In practice, it runs in five phases:

Selection
Profile data and decide history depth.
Contract
Write conversion roles into the SOW.
Design
Set cleanup rules and map codes.
Mock conversions
Fix at the source and reconcile.
Cutover
Freeze the legacy system and extract.

Cleanup sits on the critical path, as our ERP implementation timeline guide shows. If you are weighing outside help, scope depends on your modules, departments and stakeholders, so start with an assessment.

Data cleanup before ERP migration: 2026 sign-off checklist

Before the final extract, every data owner should be able to sign one line of this ERP data migration checklist:

Vendors
Duplicates merged, idle vendors retired, TINs validated, 1099 flags confirmed.
Customers and utility accounts
Duplicates merged, credits and deposits reconciled, rate codes valid.
Open items
AP and AR agings tie to the GL, stale POs closed, outstanding checks handled under state rules.
GL
Legacy trial balance by fund ties to the last audited statements plus current-year activity.
Fixed assets
Register ties to the capital asset schedule.
Grants
Only active grants with balances converting; life-to-date totals confirmed.
History
Years per data set approved and archive access confirmed for the retention period.

FAQ

How do you clean data before an ERP migration?

Cleaning data before an ERP migration starts with naming a business owner for each data set. Owners merge duplicates, retire inactive records, close open items, decide history depth, tie GL balances by fund and fix codes, then reconcile every mock conversion.

What data should be migrated to a new ERP?

The data migrated to a new ERP should be what the system needs to run and what staff use daily: active vendors and customers, open items, opening GL balances by fund, the fixed asset register, active grants and current employees. Inactive and historical records can usually stay read-only in the legacy system or an archive.

How many years of historical data should be migrated to a new ERP?

The years of history migrated to a new ERP depend on each data set and your state’s retention schedule. Washington’s local government schedule keeps general financial transactions for six years after fiscal year end, but a read-only archive can meet that. KCT rule of thumb: convert current and prior year GL budget and actuals.

Who is responsible for data cleansing in an ERP implementation?

The agency is responsible for data cleansing in an ERP implementation. Business owners decide what is correct, legacy technical staff extract and fix records, and the software vendor typically maps and loads the data. Your SOW should spell out who extracts, cleans, loads, reconciles and signs off on each data set.

When should data cleanup start in an ERP project?

Data cleanup should start no later than contract signing, ideally during selection, and run through every mock conversion until the final extract.

What is the difference between data cleansing and data conversion?

Data cleansing fixes the content of legacy records: duplicates, missing fields, invalid codes and stale items. Data conversion maps that cleaned data to the new ERP’s structure, loads it and reconciles the results.

How do you migrate GL balances to a new ERP?

GL balances migrate to a new ERP as opening balances by fund and account at a chosen cutover date. First reconcile the legacy trial balance by fund, post audit adjustments and clear suspense accounts. Then map the old chart of accounts to the new one and test the mapping against your last audited financial statements.

Final words

The cleanest conversion moves less, and is owned by people who know what right looks like.

Before your next project meeting, run the three profiling counts above.

If you want a team that has done this on real government programs, KCT’s Full-spectrum ERP Implementation service can own the cleanup plan and the mock reconciliations with your staff. Already mid-project and worried about your data? Start with an ERP health check, or contact our team to talk it through.

Ready when you are

Let's talk before it slips.