The conversation usually starts the same way. Finance wants to stop reconciling by hand, applying cash one payment at a time, or building the treasury report by pasting together five spreadsheets. Someone, whether a vendor, IT, or an outside consultant, replies with the sentence that freezes the project: "We'd need to replace the ERP first."
Sometimes that's true. But in many mid-sized companies, what blocks automation isn't the accounting system. It's the state of what goes into it and comes out of it: exports that change depending on who runs them, the same customer set up under three IDs, dates in four formats, and rules that live only in the head of your longest-tenured analyst. A new ERP fixes none of that. It migrates it, at a higher price.
This article argues for a different sequence: get the data ready first. It's work that pays off whether or not you ultimately migrate, and it lets you start automating in weeks rather than years.
1. Is It the System or the Data?
The first job is to separate two diagnoses that tend to get blended together. An older ERP can be clunky, slow, or dated and still deliver everything an automated process needs. The useful question isn't whether the system is modern; it's whether it can produce complete, stable, traceable data on a repeatable basis. As we argued in why "it connects to everything" is the wrong first question, the number of connectors matters far less than the quality of what travels through them.
- There's no way to get data out: no exportable reports, no queryable database, no API.
- You can't store a data point the process needs (say, a payment reference or a second tax ID), not even in a custom field.
- The version no longer receives support, security patches, or regulatory updates.
- It can't handle the entities, currencies, or locations the business already operates.
- The same report comes out with different columns depending on who exports it or which filters they use.
- A customer or vendor shows up under several IDs or with its legal name spelled different ways.
- Matching depends on someone "just knowing" what each reference means.
- Tolerances, approvals, and exceptions live in email threads or in people's memory.
"Migrate a messy vendor master and you get the same messy vendor master in a more expensive system, with fewer people who remember why it looks that way."
2. Exports: Stable, Complete, and Repeatable
Every automated process built on an existing ERP starts with an export. If the export isn't predictable, neither is the process. Four rules take care of most of the problems:
- A saved report layout, not a hand-built one: most ERPs let you save variants, views, or saved searches. Define one per process, with fixed columns in a fixed order, and ban ad hoc filters on the production export.
- IDs, not just descriptions: export the customer number, internal document ID, and GL account alongside the name. "Northern Distribution" is useless for matching; an ID isn't.
- Clean, tabular format: CSV or XLSX with a single header row, no merged cells, subtotals, or headers repeated on every page. PDF reports are a last resort. They can be read, as we covered in the real role of automated data extraction, but they add a validation layer you don't need if the system can export a table.
- Fixed timing and location: same time, same folder, same file naming convention. That's what turns a manual chore into a schedulable input.
The practical way to lock these rules in is a one-page spec for each export that works as a contract between whoever produces it and whatever consumes it:
3. Naming and Identifiers: One Way to Refer to Each Thing
Automation matches records on keys. When keys are ambiguous, an automated process does exactly what a new hire would: it gets it wrong or asks for help on every line. Three areas account for nearly all of the work:
Master data with one key per entity
Every customer, vendor, bank account, and cost center should have a single active ID and a validated tax ID (EIN in the US, RFC in Mexico, and so on). Duplicates aren't a cosmetic issue: as we showed in why duplicate payments are a data problem, a vendor set up twice is the most common doorway to a repeat payment. You don't need to scrub the whole master; start with the active records used by the process you plan to automate.
Document references in a canonical format
The same invoice can show up as "4182", "INV 4182", "0004-4182", or "Inv. #4182" depending on whether it comes from the ERP, the bank statement, or the customer's remittance advice. Define a canonical format and an explicit normalization rule, for example: strip text prefixes, zero-pad to eight digits, and prepend document type and series. That one rule often explains a large share of the items that sit unmatched today, as we laid out in where the time in a bank reconciliation actually goes.
Picklists instead of free text
Bank transaction codes, credit memo reasons, expense types, and exception categories should be chosen from a list, not typed. "Price diff", "price difference", and "agreed price diff" are three different values to any process trying to group them or apply a rule.
4. Formats: Dates, Amounts, Signs, and Currencies
Plenty of automation errors don't come from the logic at all; they come from regional settings. A CSV exported with comma decimals from a Latin American subsidiary and opened on a US-configured machine turns 1.234,56 into a number a thousand times larger, or into text. Set these conventions once and apply them to every export:
- Dates:
YYYY-MM-DD. It ends the 03/11 versus 11/03 debate, which matters the moment you consolidate a non-US entity. - Amounts: period decimal, no thousands separator, no currency symbol.
- Signs: choose either negative amounts or a debit/credit type column, but don't mix the two.
- Currency and FX rate: always in their own columns, with the source and date of the rate used.
- Encoding: UTF-8, so accented names and special characters don't break lookups.
5. Business Rules: Get Them Out of People's Heads and Onto Paper
Clean data is half the job. The other half is the decisions your team makes today without ever writing them down: what variance is acceptable in a reconciliation, when a short payment gets applied versus disputed, who approves an adjustment above a certain amount. A rule is ready to automate when you can write it in four parts:
- Condition: "if the amount received differs from the invoice by less than $50 or 0.5%…"
- Action: "…apply it to the invoice and post the difference to the rounding account."
- Exception: "unless the customer has more than three variances this month."
- Owner: "exceptions are reviewed by the AR lead before the weekly close."
Keep these rules in a simple catalog, with an ID per rule, its threshold, its owner, and the date it was last reviewed. Doing this surfaces a key distinction: some decisions are deterministic rules that can be fully automated; others call for judgment and should stay as human review points. Tax withholdings in customer accounts are a good example: validating withholding rates is a rule; deciding whether to release an order past the grace window is a decision.
6. A Four-Week Plan on Your Current ERP
You don't need to clean up all of the company's data before you start. You need to clean up the data for one process. Following the logic in what teams get wrong about "starting small", the scope should be narrow but complete, end to end:
- Week 1 — Pick the process and its export: a repetitive, high-volume process with a clear owner. Identify which reports feed it and who produces them today.
- Week 2 — Lock the export and clean the affected master data: save the layout, write the export spec, and remove duplicates only in the records the process touches.
- Week 3 — Document the rules and measure exceptions: write the rule catalog and run it in parallel against a closed month to see how many items it resolves and how many it escalates.
- Week 4 — Pilot on real data: run the process on the day's exports, with human review of every exception, and compare the output to the manual result.
With that groundwork in place, automation can run custom logic and scripts on your existing files and reports and hand back batches ready to import into the ERP, without touching its configuration. When we work this way, we start with a Discovery to map exports and rules, move to a pilot on real data, and deliver documentation for every rule and format, with cloud or on-premise hosting depending on where the data needs to live. If you decide to migrate later, that documentation becomes the spec for the migration.
The Migration Can Wait. Cleaning Up the Data Can't.
"Do we need a new ERP to improve our processes?" rarely has an immediate yes or no, but it does have a first step that doesn't depend on the answer. If, after locking exports, unifying identifiers, normalizing formats, and writing down the rules, the system still can't deliver or store what the process needs, you'll have a solid migration case, with concrete requirements and clean data to move. And if the system holds up, you'll have started automating without waiting on a two-year project. If you'd like to figure out which case applies to your operation, reach out and we'll look at it using one of your actual exports.