Why Xero exports come out messy
Xero is excellent at holding your financial data and — like every accounting system — indifferent about how it hands the data over. Four things cause most of the pain:
- Date locale drift. Exports reflect the organisation's regional settings, and whatever opens the file next often reinterprets them.
- Contact duplication. Years of imports, auto-created contacts, and manual entries mean the same customer exists three ways.
- Code-only fields. Account codes, tax types, and tracking categories export as codes only the source system understands.
- Template mismatch. The columns Xero produces are never quite the columns the next system wants.
The manual cleanup checklist
These six steps work in any spreadsheet tool. They're also exactly the steps worth automating once you've done them twice.
- Export and inspect before touching anything. Open the raw CSV and scan the first 50 rows: note the date format, empty columns, duplicated rows, and any code-only fields. Knowing what's wrong first stops you from fixing the same file twice.
- Normalise the dates. Pick the format the destination expects (for example ISO 8601, YYYY-MM-DD) and convert every date column to it. Watch for mixed formats in the same column — a giveaway that rows came from different sources.
- Remove duplicates. Sort by the natural key (contact name + email, or invoice number) and remove exact repeats. Then look for near-duplicates: 'Acme Ltd' vs 'ACME LTD' vs 'Acme Limited' — standardise casing and suffixes before de-duplicating again.
- Translate code fields to readable values. Account codes, tax type codes, and tracking category codes mean nothing to the next system. Build a two-column mapping (code → label) and apply it consistently.
- Align columns to the destination's template. Rename headers to exactly what the target expects, reorder to its template, split or merge fields (one 'Name' column into first/last, or the reverse), and drop columns it doesn't accept.
- Validate, then keep the recipe. Spot-check totals and row counts against the source, fix stragglers, and — most importantly — write down every step you did. Next month's export will have the same problems.
How to automate it
Everything above is rule-shaped work — which is why doing it by hand every month hurts. In Morph's Xero integration you build each step once as a no-code rule: date normalisation, de-duplication, code mapping, column alignment. Then:
- Run it on demand on any plan, or on a schedule — daily on Starter, hourly on Pro and up — with runs firing server-side, 24/7.
- Point the same rule set at any client, file, or project; every export gets the identical treatment.
- Deliver the clean result to Google Drive, write it back into Xero, or download it as CSV.
- Only successful runs count against your plan — failed runs are always free.
Preparing data for an import into Xero rather than out of it? See Bulk import data into Xero without a developer.