Xero CSV Import and Export: Bank, Invoices, and Contacts

Xero Runs on Clean CSV Bridges

Xero is the default ledger for many UK, Australian, and New Zealand businesses, and it is widely used in Canada and the US as well. Bank lines, invoices, bills, and contacts often move through CSV — either from the bank, from a previous system, or from a spreadsheet the accountant maintains.

Xero’s importers are template-driven. Headers must match. Tax rates must match names in the org. Dates must match the organisation’s locale. This guide walks through the CSV workflows that fail most often across those markets.

Which Xero CSV Are You Building?

Do not reuse one generic sheet for every import type.

Step-by-Step: Bank CSV into Xero

Bank reconciliation is where most Xero CSV traffic comes from.

• Export CSV from your bank portal (UK/AU banks often use DD/MM/YYYY).
• If you only have OFX/QIF/PDF, convert to CSV first and reconcile totals.
• Open the file in the Online CSV Editor; delete balance-only rows and ad headers.
• Ensure Date, Amount, and a payee/description column are present.
• In Xero: Accounting → Bank accounts → manage account → Import statement.
• Map columns; check the preview for flipped signs and wrong dates.
• Complete the import and reconcile against the bank ending balance.

Date,Amount,Payee,Description,Reference
03/05/2026,-42.18,AWS,Cloud hosting,INV-8891
04/05/2026,1500.00,Acme Ltd,Invoice payment,INV-2201

Date,Amount,Payee,Description,Reference
05/03/2026,-42.18,AWS,Cloud hosting,INV-8891
05/04/2026,1500.00,Acme LLC,Invoice payment,INV-2201

UK, Australian, and US Date Rules

Xero organisations inherit regional defaults. A file that works for a Sydney org can fail or mis-post in a California org.

• Ask which locale the Xero org uses before reshaping dates.
• When collaborating across AU and US offices, standardize on ISO YYYY-MM-DD in the shared CSV if Xero accepts it for that import.
• After Excel opens a CSV, verify a known date (e.g., 13 March) still shows day 13 — if Excel shifted it, the serial was misread.

GST, VAT, and TaxType Columns

Invoice and bill CSVs fail most often on tax names, not math.

• TaxType values must match Xero’s tax rates exactly (including spacing and case where required).
• Australian files often need GST on Income / GST on Expenses style rates.
• UK files need correct VAT rates configured in the org before import.
• Do not put “20%” in TaxType unless that is literally the rate name in Xero.
• UnitAmount is usually exclusive of tax unless your template documents inclusive amounts.

ContactName,InvoiceNumber,InvoiceDate,DueDate,Description,Quantity,UnitAmount,AccountCode,TaxType
Acme Ltd,INV-1001,2026-05-01,2026-05-31,Design services,1,1200.00,200,VAT on Income

Contacts CSV Without Duplicates

Contact Name is the key Xero uses to match. Slight differences create duplicates that pollute reporting.

• Export existing contacts first; match names exactly when updating.
• Keep AccountNumber and phone fields as text.
• Preserve UK/AU postcodes and Canadian postal codes with spaces as intended.
• Split bill and ship addresses only if the template provides those fields.
• UTF-8 CSV for names with accents (common in Canadian and European supplier lists).

Excel and Encoding Pitfalls for Xero

Accountants often prep Xero files in Excel on Windows and Mac.

• Save as CSV UTF-8 so £, €, and accented contact names survive.
• German or EU Excel may export semicolons; convert to comma CSV before Xero if the importer expects commas.
• Remove formula cells — paste values only.
• Do not leave blank template columns that shift AccountCode under TaxType.
• Re-download Xero’s sample after product updates; old header names silently fail.

Migrating from QuickBooks or Another Ledger

Migrations are CSV-heavy. Treat them as several small imports, not one giant file.

• Export chart of accounts and recreate tax rates in Xero first.
• Import contacts before invoices so ContactName resolves.
• Import historical invoices in date batches; reconcile AR/AP subtotals each batch.
• Convert QuickBooks IIF/QFX leftovers to CSV for review — do not paste Desktop formats into Xero.
• Keep a mapping sheet: old account code → Xero AccountCode.

Common Mistakes

Frequent rejects across UK, AU, CA, and US Xero orgs.

• Using QuickBooks column names in a Xero invoice template.
• Wrong TaxType strings copied from an invoice PDF instead of Xero settings.
• Uploading semicolon-delimited EU Excel CSV without conversion.
• Flipped bank signs (payments appearing as deposits).
• Merged cells and title banners above the header row.
• Editing a CSV in Excel and letting it rewrite dates to US order on a UK machine (or the reverse).

Best Practices

Make Xero’s template the source of truth.

• Download a fresh sample CSV for each import type.
• Validate row counts and amount sums before and after import.
• Store each uploaded file with the import date in the filename.
• Train the team on the org’s date locale once; put it in the bookkeeping SOP.
• Clean files in the browser when handling client bank data.

Why Use Convert CSV Online?

Convert CSV Online is free, browser-based, and requires no account for everyday conversions. Use the Online CSV Editor to fix Xero bank and invoice tables, convert OFX/QIF bank downloads via the Finance File Converter, and switch semicolon exports to comma CSV with the Delimited converter. Works in Windows, macOS, and Linux browsers — fitting mixed UK, Australian, Canadian, and US teams.

Conclusion

Xero CSV imports succeed when templates, TaxType names, and regional dates align with the organisation. Clean the file first, import small batches, and reconcile — especially when moving data between UK/AU day-first dates and US month-first habits.