Xero bank statement import: CSV format that actually works

Xero's manual statement import is the most underrated feature in modern accounting software. Bank feeds get the marketing, but feeds break, banks churn integration partners, and historical periods almost always need a CSV upload anyway. The catch: the CSV format Xero expects is undocumented in most of the places people look, and the failure modes don't come with clear error messages. Half the time the file imports "successfully" with wrong amounts.

This is the format spec — exhaustive, with examples, with the failure modes each rule prevents, and with a copy-paste template at the end you can adapt for any bank. We'll cover the required columns, the optional ones, the date rules, the amount rules, the encoding requirements, and the seven ways a technically-valid CSV silently produces a broken reconciliation. If you want the bank-specific workflow on top of this, our bank statement converter for Xero guide picks up where this post leaves off.

The minimum viable Xero CSV

Two columns. Two. That's the floor:

Date,Amount
2026-05-03,-42.30
2026-05-04,3200.00
2026-05-04,-12.75

Xero accepts this. It will import as three statement lines on the chosen bank account with no payee, no description, no reference — but it will reconcile, and that's all the format strictly requires. Every other column is optional and there for human readability, contact matching, and Xero's Bank Rules engine. Practitioners pretty much never upload the bare two-column version, but knowing it's legal is useful for debugging — when an import fails, strip back to two columns and you'll find out whether the problem is in your data or in the optional columns.

The full column schema

The complete set of columns Xero recognises on a statement import:

Anything else in the file gets silently ignored. So a converter that emits an extra column called Running Balance or Reconciliation Hashon top of these doesn't break the import — Xero just drops the unrecognised column.

Date column rules

The Date column is the single most common reason a Xero import goes sideways. Xero accepts these formats:

• DD/MM/YYYY — UK, Irish, Australian, NZ, most EU locales.
• MM/DD/YYYY — US locale only.
• YYYY-MM-DD — ISO 8601. Locale-independent. Recommended.
• D MMM YYYY — e.g. 3 May 2026. Works everywhere.
• DD MMM YYYY — e.g. 03 May 2026. HSBC UK's format. Works.

The trap: Xero picks the date format based on your organisation's regional settings, not on the file. A Xero org in the UK reading a US-style CSV silently parses 03/04/2026 as 3 April. The same file uploaded to a US-based Xero org parses it as 4 March. No warning, no prompt — just two different sets of books from the same file.

Fix: emit ISO dates (2026-04-03) by default. ISO is unambiguous in every locale Xero supports. The only reason not to use ISO is if you're editing the file by hand and want human-readable dates — in which case 3 May 2026 beats 03/05/2026, because the former can't be misread.

Amount column rules

Single signed column. Debits are negative. Credits are positive. That's the core rule. The complications come from how the number itself is formatted:

• Decimal separator.Period in UK/US/Anglo locales. Comma in mainland-EU locales. Xero respects your org's regional setting. A UK Xero rejects 1234,56; a German Xero rejects 1234.56. The cheapest fix when in doubt: omit thousands separators entirely and use period for decimal. 1234.56 is accepted by Xero everywhere.
• Thousands separator. Xero handles 1,234.56in Anglo locales and 1.234,56 in EU locales, but neither in the wrong locale. Skip thousands separators entirely if your file might cross orgs — write 1234.56, not 1,234.56.
• Currency symbols. Don't. £1234.56, €1234.56, $1234.56 all get rejected. The currency is set on the Xero bank account, not on every row.
• Parentheses for negatives.Don't. US accounting convention writes (1234.56) for negative; Xero rejects this as an invalid number. Use a minus sign: -1234.56.
• Trailing CR/DR suffix. Don't. Indian (1234.56 Dr), German (1234,56 Soll), Brazilian (1234,56 D) suffix notations all fail. The sign must be encoded as the leading character of the number.

The Money-In / Money-Out trap (in one paragraph)

Most UK, Irish, Australian, and New Zealand banks emit statements with two amount columns: Money In and Money Out. Xero expects one signed column. If you upload the raw two-column CSV, Xero reads only Money In as positive and silently drops every Money Out row, or — worse — treats both as positives and doubles the apparent balance. Merge them before upload: Money In becomes positive Amount, Money Out becomes negative Amount. We covered the per-bank patterns in AIB, Lloyds, Barclays → Xero: the 2026 workflow; the short version is "a converter that knows your bank merges them automatically".

Header row rules

Exactly one header row at the top of the file. No preamble. No blank rows before it. Column names case-insensitive: Date, date, and DATEall match. Column order doesn't matter — Xero's importer maps by name, not by position.

Banks that emit CSVs often prepend metadata before the data rows: a line like Statement for Account 12-34-56, a period line like Period: 01/05/2026 - 31/05/2026, sometimes a blank row, then the actual column headers. Upload that raw and Xero treats the first line as the header. So Statement for Account 12-34-56 becomes the name of column 1, with one cell of content. Every subsequent row tries to fit a single-column schema. The first three rows of the actual statement get burned as malformed transactions; everything past that fails with column-count errors. Strip the preamble before upload, or use a converter that emits a clean single-header CSV.

File-level rules

• Encoding: UTF-8. UTF-8 BOM is fine — Xero strips it. Latin-1 / Windows-1252 sometimes works but breaks on currency symbols (£ in particular), accented characters in payee names, and any non-ASCII merchant name. If your CSV opens in Notepad and shows Cafá instead of Café, your encoding is wrong.
• Delimiter: comma.Despite the name "CSV", many European Excel exports use a semicolon delimiter because comma is a decimal separator in those locales. Xero rejects semicolon-delimited files even on EU-locale orgs. Convert to comma-delimited before upload, quoting any field that contains a comma.
• Line endings: LF or CRLF.Both work. Old Mac CR-only line endings (rare) don't.
• Quoting: standard. Wrap fields containing commas, newlines, or double quotes in double quotes. Escape literal double quotes by doubling them: "He said ""yes""".
• File size: practical limit ~10 MB.Xero technically accepts larger but the import wizard times out on big files. Split across statements if you're importing a multi-year history.

A worked example: clean Xero CSV for a EUR account

This file imports into a UK-based Xero org with a EUR bank account, no manual column mapping required:

Date,Amount,Payee,Description,Reference
2026-05-01,-1450.00,Hauptstadt Immobilien,Monthly office rent,SEPA-RENT-202605
2026-05-03,3200.00,Acme GmbH,Salary May 2026,SAL-2026-05
2026-05-04,-42.30,Stripe Payments,Subscription billing,STR-20260503-A1
2026-05-05,-12.75,Edeka,Card payment groceries,POS-7724
2026-05-08,825.50,Beta Studios GmbH,Invoice 2026-104,INV-104-PAID
2026-05-12,-89.95,Telekom,Mobile bill,TEL-202605
2026-05-15,-1200.00,Finanzamt Munich,VAT Q1 2026,TAX-Q1-2026
2026-05-20,5000.00,Gamma Corp,Project advance,PROJ-GAMMA-01
2026-05-25,-340.00,Vereinigte Versicherung,Liability insurance,INS-LIA-202605
2026-05-30,-15.00,Deutsche Bank,Account fees,FEE-202605

Note what isn't there: no preamble, no currency symbols, no thousands separators, no parens for negatives, no Money-In/Money-Out columns, no running balance, no second header. Dates are ISO. The Reference column carries enough structure for Bank Rules to match recurring payments. Ten rows in, the file is unambiguous.

The seven silent-failure modes

Every one of these produces a file Xero accepts "successfully" with wrong numbers:

1. Date locale flip. File has 03/04/2026, Xero org is US-locale. Imports as 4 March instead of 3 April. Three months of transactions land in the wrong month.
2. Decimal-separator flip. File has 1.234,56, Xero org is UK-locale. Either rejects the row, parses as 1.23456, or imports as zero.
3. Money-In-only. Two-column UK statement uploaded raw. Money-Out rows silently dropped. Books balance off by every debit in the period.
4. Suffix amount. 1234.56 Dr parses as 1234.56 (positive). Every debit becomes a credit.
5. Header preamble. First three rows of statement burned as malformed. Missing transactions go unnoticed because the row count looks close to right.
6. Encoding corruption.Payee "Café Müller" becomes "Cafá Müller". Imports fine, but Bank Rules built on the correctly-spelled payee don't match.
7. Misread amount on one row. A 7 read as a 1, a missing minus sign, a comma swapped with a period. The file imports, books balance approximately, auditor catches it three months later.

The first six can be fixed once at the file-generation step — emit ISO dates, period decimals, merged Amount, no preamble, UTF-8. The seventh is harder because it's a per-row OCR or parse error. The only defence is reconciliation: if the opening balance plus the sum of transactions equals the closing balance, no row was misread. If they don't, the converter flags the row that broke the chain instead of silently shipping a bad file.

The mapping table — error to cause to fix

A copy-paste template you can adapt

Save this as statement.csv in UTF-8, fill in your rows, and upload directly to Xero via Accounting → Bank accounts → [your account] → Manage account → Import a statement:

Date,Amount,Payee,Description,Reference
YYYY-MM-DD,-123.45,Payee name,Description text,Reference text
YYYY-MM-DD,678.90,Another payee,Another description,Another ref

That's it. ISO dates, signed amount, optional payee/description/reference. UTF-8 encoding, comma delimiter, one header row. The Xero import wizard auto-detects this layout and imports it without prompting for a column map.

The end-to-end workflow

1. Download the bank-statement PDF from your bank's online banking portal. If the PDF is password-protected (common in EU corporate accounts, all Indian banks), unlock it via our free PDF unlock tool.
2. Convert the PDF on StatementEdge. The conversion handles every format rule in this post automatically — ISO dates, signed Amount, merged Money-In/Money-Out, UTF-8 encoding, no preamble, one header row.
3. Check the reconciliation badge. Green = opening + sum = closing. Amber = balanced within rounding tolerance. Red = something didn't extract correctly; review the flagged rows before continuing.
4. Download as Xero CSV. The file is ready to upload unchanged.
5. In Xero: Accounting → Bank accounts → [your account] → Manage account → Import a statement. Pick the CSV. Confirm the auto-detected mapping. Click Import.
6. Reconcile from the bank reconciliation screen. Bank Rules auto-apply to recurring payees. Statement lines match your existing invoices and bills.

Further reading

• Bank statement converter for Xero — the full workflow guide, including multi-currency and batch import patterns.
• AIB, Lloyds, Barclays → Xero: the 2026 workflow — per-bank quirks for UK and Irish statements.
• What "auto-reconciled" actually means — the defence against silent extraction errors.
• Why your QuickBooks import keeps failing — the same locale traps, in QBO form.
• The complete guide to bank statement converters in 2026 — the pillar piece.
• Free reconciliation checker · PDF unlock · Statement validator.
• REST API + MCP server · Pricing · Security.