Xero CSV import failed: fixing column mapping errors

What Xero expects

A statement CSV for Xero needs a date, a description, and an amount. Payee, reference and cheque number are optional. The mapping screen lets you point Xero at whichever columns hold them, which is why a rejected file is usually rejected for the shape of a column rather than for the columns present.

The amount column defeats most files

Bank statements typically print two amount columns — debit and credit — and leave one blank per row. Xero wants either one signed column, or a debit/credit pair it has been told about explicitly. Handed two columns it was not told about, it maps one and silently drops the other, and half the statement vanishes.

• One signed column: negative for money out, positive for money in. Simplest, and the least ambiguous.
• A debit/credit pair, mapped as such on the import screen. Works, provided the blank cells really are blank rather than zeros — a column of zeros maps as real transactions of nothing.
• Amounts wrapped in parentheses for negatives, as accounting layouts do. Xero reads (1,234.00) as text, not as a negative number.

The date setting that shifts everything by months

A CSV date carries no indication of its order. 03/04/2026 is 3 April to half the world and 4 March to the other half, and Xero resolves it using the region set on the organisation rather than anything in the file.

Set the date format explicitly on the import screen. Do not rely on the default, and do not assume last month’s successful import proves anything about this one.

Other things Xero rejects

• A header row that is not the first row. Bank exports often carry an account summary above the table; delete everything above the headers.
• Blank rows in the middle, usually page breaks from a converted PDF.
• Thousands separators inside the amount, where the file is comma-delimited. The number is split across two columns.
• Duplicate dates with identical amounts and descriptions. Xero flags these as possible duplicates rather than rejecting them; read the warning before dismissing it.

When to give up on CSV

OFX is self-describing: it states its dates unambiguously and its amounts are signed by definition. There is no mapping step because there is nothing to map. If a statement has fought you through two attempts at CSV, the third attempt should be a different format.

Statement lines are not bank transactions

Xero draws a distinction worth understanding before troubleshooting an import. What you upload becomes statement lines — the bank’s side of the story, sitting in the reconciliation screen waiting to be matched. They are not accounting transactions until you reconcile them against a bill, an invoice or a spend-money entry.

So an import that "worked" but shows nothing in your reports has not failed. The lines are there; they have not been reconciled yet.

The duplicate warning is worth reading

Xero checks an incoming file against statement lines already on the account and warns when it finds matches on date, amount and description together. Unlike QuickBooks it does not have transaction IDs to work from, so the check is heuristic.

That cuts both ways. Two genuine transactions on the same day, for the same amount, to the same merchant — a pair of identical fees, say — are flagged as duplicates and one gets dropped if you dismiss the warning carelessly. Read what it lists before continuing.

Practical limits

• Very large files are better split by month. It also makes the mapping screen readable.
• Xero will not accept a statement line dated in the future.
• The account must have a currency matching the file’s. Converting a statement does not convert its currency.
• Delete any account-summary block above the header row. Xero expects the headers first.