Xero bank statement CSV import: the format that actually works in 2026

Xero's manual statement import is a deceptively simple screen: pick a file, map the columns, click Save. On a clean export it takes ten seconds. On a real-world bank CSV — the kind with two logo rows above the header, semicolon delimiters, and amounts written 1.234,56 — it can eat an afternoon before you realise Xero imported half the debits as zeros and the other half with the sign flipped. This post lays out exactly what the working format looks like, why the common exports break, and how to get a first-time-clean import from any bank.

If you'd rather skip the format-authoring entirely, drop a PDF into our converter and pick Xero CSV as the export target — the schema below is baked in and the amount column is already reconciled to the closing balance. But if you're building a CSV by hand, or fixing one your bank produced, the mechanics below are what matters.

The columns Xero actually accepts

Xero's CSV template has five columns in the recommended order. Only the first two are strictly required; the rest are optional but almost always useful:

That's the entire schema. There is no separate Debit column, no Credit column, no Balance column, no Type column. Xero will accept a file with extra columns present but ignore them at the mapping step — which is fine, except when the extra column is where the actual amount lives (see below).

The Date column: ISO or bust

Xero accepts several date formats — DD/MM/YYYY, MM/DD/YYYY, YYYY-MM-DD, and a few with two-digit years — but the mapping screen picks a default based on your organisation's region, and it gets the choice wrong constantly on exports from banks in a different locale.

The failure mode is silent and specific: any row where the day is 12 or less will parse under whichever format Xero picked, so 03/04/2026 imports as either 3 April or 4 March with no complaint. You only find out when the March/April boundary is wrong on your VAT return.

The fix is to emit ISO dates — 2026-04-03 — before you upload. ISO is unambiguous, Xero recognises it in every region, and the date-format dropdown on the mapping screen becomes irrelevant. If you can control the export shape (a converter, a script, a spreadsheet formula), always emit ISO.

The Amount column: single signed number, no decoration

This is where most manually-authored CSVs go wrong. Xero wants one Amount column with signed numbers. Positive is money in (deposits, credits), negative is money out (payments, debits). None of the following work:

• Money In / Money Out as two columns.The UK/Irish/ Australian bank convention. Xero's mapper only reads one of them, so half your transactions come in as zero.
• Debit / Credit as two columns. Same problem, different labels. Same fix — merge into a single signed Amount.
• Parentheses for negatives. (1,234.56) is US accounting convention. Xero parses it as text, and the mapping row fails silently.
• Currency symbols. €1,234.56 or £1,234.56 parses as zero. Strip the symbol before upload.
• Dr/Cr suffixes. German Soll/Haben, Indian Dr/Cr, Brazilian Débito/Crédito. Xero ignores the suffix and reads the bare number, losing the sign on every debit.
• Trailing minus. 1234.56- is a SAP export convention. Xero reads it as positive.

The canonical amount format is -1234.56 for a debit, 1234.56 for a credit, with no thousands separator, no symbol, and a period as the decimal mark. If your source data uses 1.234,56(Continental European), you must convert it — Xero assumes the anglophone convention regardless of your org's region setting on manual CSV imports.

The header row: exact spelling, single row, first line

Xero's CSV parser expects the header on row 1. It doesn't skip blank lines, it doesn't auto-detect a header two rows down, and it doesn't forgive typos. Two things break here constantly:

• Bank branding preamble. Many bank CSVs open with rows like "Account Statement", "Account: 12345678", "Period: 01/04 to 30/04" before the actual column titles. Xero either errors at row 1 or treats those rows as transactions and burns three cells of the file to failed parses.
• Missing asterisks on required columns.Xero's template shows required columns with a leading * — as in *Date and *Amount. The asterisk is cosmetic (Xero maps by column position, not header text, at the mapping step), but a header row that reads Date, Amount, Description vs *Date, *Amount, Description can trip up scripts that expect the exact template.

Strip preamble rows before upload. If you author CSVs from a template, keep the asterisked names — they document intent and match Xero's own example file.

Encoding and delimiter: UTF-8, comma-separated, no BOM

Xero expects UTF-8 encoded, comma-separated values. Most exports from modern banking portals get this right. The traps are:

• Semicolon delimiters from European Excel exports. German, French, Dutch, and Spanish locales default to ; as the list separator. Xero reads the whole line as a single column and the mapping screen shows one huge Description with everything jammed together. Fix: Save As → CSV UTF-8 (comma delimited), or convert with our CSV column mapper which auto-detects the delimiter and normalises.
• UTF-8 BOM — the three invisible bytes EF BB BF that Excel prepends when saving as CSV UTF-8. Xero's parser sometimes treats the BOM as part of the first column header, so *Date becomes *Date and the mapping fails. Save as plain CSV (comma delimited) from Excel, or strip the BOM in a text editor.
• Windows-1252 with special characters. Payees with accented characters (Café, Zoë, München) come in as garbled sequences like Café if the file is saved as ANSI/Windows-1252 rather than UTF-8. The rows import, but reconcile suggestions and Bank Rules matching break on the mangled text.

The seven silent-failure modes

Every issue above throws a visible symptom — an error message, zero transactions, or a garbled column. The genuinely dangerous imports are the ones that show "Statement lines imported: 142" and quietly contain wrong numbers. In order of frequency:

1. Sign flip on Money-In/Money-Out merges. A script that subtracts one column from the other rather than combining them correctly. Half the rows come in with the wrong sign; the total balances but the debit/credit ledger is inverted.
2. Decimal comma read as thousands. A €12,50 espresso becomes a €1,250 espresso when the parser assumes anglophone format. Every amount is off by a factor of 100 and reconciliation looks catastrophically wrong.
3. Date column MM/DD vs DD/MM ambiguity. As above — every row with day ≤ 12 lands in the wrong month.
4. Truncated Description on long rows. Xero silently truncates the Description at 4000 characters. Some bank exports pack entire counterparty addresses into the memo; the truncation strips the reference number that Bank Rules would match on.
5. OCR digit misreads on scanned PDFs. A 7 read as 1, a 0 read as O, a 6 read as 5. The row imports cleanly but the amount is wrong. Only a reconciled converter catches this.
6. Multi-currency amounts on a single-currency account. A statement with mixed EUR and GBP lines mapped to a EUR bank account — Xero imports the GBP numbers as if they were EUR.
7. Duplicate detection collision.Xero dedupes on Date + Amount + Description. Two identical £4.50 coffees on the same day from the same shop merge into one — and the P&L is missing a row.

The defence against all seven is the same discipline as any other bank import: opening balance + Σ(transactions) must equal the closing balance printed on the statement. This is what auto-reconciliation means — and it's the one check Xero itself cannot perform, because it never sees the statement's opening or closing balance.

Worked example: a UK bank CSV that doesn't import

Here's a shape you'll recognise if you bank with any UK high street bank — Lloyds, Barclays, HSBC, NatWest all emit variants of this:

Transaction Date,Transaction Type,Sort Code,Account Number,Description,Money Out,Money In,Balance
03/04/2026,FPI,30-96-00,12345678,ACME COFFEE LTD,4.50,,1234.56
03/04/2026,DD,30-96-00,12345678,BRITISH GAS,,,1225.06
04/04/2026,BAC,30-96-00,12345678,CUSTOMER INV 2041,,2500.00,3725.06

Four problems in one file, in order of appearance to Xero:

1. Header column names don't match Xero's expected fields — you'll have to map each one manually every import.
2. Dates are DD/MM/YYYY. Rows on the 4th of the month parse fine; anything ≤ 12 is ambiguous.
3. Money Out and Money Inare two separate columns. Xero's mapper only lets you pick one as the Amount, so you either lose all your debits or all your credits.
4. The second row (British Gas) has both Money Out and Money In empty because of a parsing artefact — the CSV writer emitted a trailing comma for the DD row instead of the amount. That row imports as a zero.

The reshaped file that imports cleanly:

*Date,*Amount,Payee,Description,Reference
2026-04-03,-4.50,ACME COFFEE LTD,Faster Payment,FPI
2026-04-03,-19.94,BRITISH GAS,Direct Debit,DD
2026-04-04,2500.00,CUSTOMER INV 2041,BACS credit,BAC

ISO dates, single signed Amount column, Payee split from Description, no empty rows. The BRITISH GAS amount was recovered from the Balance column delta (1234.56 − 1225.06 = 19.94) — the exact kind of reconstruction our bank CSV transformation checklist walks through end to end.

Where the import button actually lives

Xero's 2026 UI puts manual statement uploads three clicks deep:

1. Accounting → Bank accounts from the main nav.
2. Click the tile for the account you're importing into.
3. On the account page, click Manage account → Import a statement. Not Statements, not Bank statements — the option you want is titled Import a statement.

The next screen accepts .csv, .ofx, .qif, and Xero's own .qbo-adjacent formats. CSV is the one with the column-mapping step; the others read their schema from the file header directly.

The pre-upload checklist

Before you click Save on the mapping screen, verify:

• Row count on screen matches row count in the source file.
• Date range on screen matches statement period.
• Sum of the Amount column (positive − negative) matches the statement's net change.
• Sample three rows: pick a large debit, a large credit, and a mid-month row. Verify the sign and value against the PDF.
• The date-format dropdown in the mapping screen matches the shape of your data.

Sixty seconds of checking, on the mapping screen, catches every silent failure mode above. This is the discipline that separates a five-minute Xero import from a two-hour retro-cleanup after month-end.

When you shouldn't use manual CSV at all

Manual CSV import is the fallback path. If your bank supports Xero's direct Bank feeds (Open Banking in the UK/EU, Yodlee/Plaid elsewhere), use that instead — the feed pushes transactions in continuously with proper deduplication and no format work. Manual CSV is what you fall back to when:

• Your bank isn't on Xero's feed list.
• You're back-filling months of history before the feed connected.
• The feed has a coverage gap and Xero's support can't backfill it.
• You're importing a closed account or a currency the feed doesn't support.
• You only have PDF statements from your accountant or the client.

For the PDF-only case, converting to a Xero-shaped CSV is the whole point of a converter — and it's where getting the format right matters most, because there's no source-of-truth digital export to fall back on if the CSV is wrong.

Further reading

• Xero CSV import errors decoded — every error message the mapping screen throws and the real cause.
• Bank CSV → Xero transformation checklist — the eight transforms every bank export needs before it's Xero-clean.
• AIB, Lloyds, Barclays → Xero: the 2026 workflow — the same Money-In/Money-Out fixes applied per bank.
• What "auto-reconciled" actually means — the check that catches the silent-failure imports before they hit your books.
• Bank statement converter for Xero — the full converter workflow, formats supported, and edge cases.
• REST API + MCP server — if you're running Xero imports weekly, automate the PDF → CSV step and skip the mapping screen entirely.