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.

Where Xero hides the import button in 2026

Half the support questions we see about Xero imports aren't about the file at all — they're people who can't find where to upload it. The path has moved twice since 2023 and the 2026 layout buries it behind two more clicks than the older blog tutorials show. Here's the current route, in the new navigation:

1. Accounting in the top menu → Bank accounts. (Older guides say Dashboard; on the 2026 nav, the dashboard tile is a shortcut to the same screen but doesn't expose the import on its own.)
2. Click the bank account you're importing into. You land on the reconciliation screen, not the import wizard.
3. From the reconciliation screen, click Manage account in the top-right corner. A dropdown appears.
4. Choose Import a statement. This opens the upload wizard. Browse to your CSV. Click Import.

If you're using Xero Cashbook or Ledger (the accountant-only editions), the labels are the same but the icons differ. The wizard itself is identical across editions. If you're an advisor working in a client file via Xero HQ, you still see the import in the same place — Xero HQ is a launcher, not a substitute UI.

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.

Manual import vs bank feed: which to use when

Xero pushes bank feeds in every onboarding flow, and for steady-state monthly reconciliation they're the right tool. But manual CSV import isn't a backup — it's the better path in five specific situations, and knowing which is which prevents weeks wasted on a feed that was always going to fail:

• Historical periods. Bank feeds typically backfill 90 days. A new client with two years of unreconciled history needs a manual CSV upload per statement period — feeds physically cannot reach that data.
• Closed accounts. The account is closed but the books still need the final statement period reconciled. No feed can connect; CSV is the only option.
• Banks without a direct feed.Yodlee and Plaid cover most of the developed-world banks, but EU mittelstand banks, Latin American challenger banks, most Indian banks, and a long tail of credit unions don't have working feeds. Manual import is the steady-state workflow, not an interim fix.
• Feed broken or stale.A bank changes its login flow, the feed silently breaks, three weeks of transactions are missing. Reconnecting takes hours or days; uploading the period's CSV takes minutes.
• Audit-period lockdown.Audit periods sometimes require freezing the books with a known-good source-of-truth file. CSV imports leave an attachable artefact in a way feeds don't.

For all five, the workflow is the same: PDF in, Xero-template CSV out, manual import. Which is where the rest of this post matters.

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.

A second worked example: the UK Money-In / Money-Out merge

The EUR example above is the destination format — clean signed Amount column. Most UK bank CSV exports look very different. Here's what a typical Barclays Business CSV gives you out of the box:

Number,Date,Account,Amount,Subcategory,Memo
1,03/05/2026,'12345678,3200.00,Salary,May payroll
2,03/05/2026,'12345678,-1450.00,Rent,Office lease
3,04/05/2026,'12345678,-12.75,Groceries,Tesco
4,05/05/2026,'12345678,-89.95,Telecoms,Mobile bill

Barclays Business helpfully emits a signed Amount column already, so the merge job is the easy case. Lloyds, HSBC, and most building societies do not. Here's a representative Lloyds-style statement before any conversion:

Transaction Date,Transaction Type,Sort Code,Account Number,Transaction Description,Debit Amount,Credit Amount,Balance
03/05/2026,FPI,30-12-34,12345678,SALARY HOMECORP MAY,,3200.00,14250.50
03/05/2026,DD,30-12-34,12345678,RENT METROPOLE LTD,1450.00,,12800.50
04/05/2026,POS,30-12-34,12345678,TESCO STORES 2417,12.75,,12787.75
05/05/2026,DD,30-12-34,12345678,O2 UK LIMITED,89.95,,12697.80

Upload that file to Xero unchanged and one of two things happens. Best case: Xero auto-maps Credit Amount → positive Amount and ignores the Debit column, so all your debits silently disappear. Worst case: it sums both columns into a positive Amount, and your statement shows twice the credit activity and zero debits. Either way the numbers are wrong, the import looks "successful", and the reconciliation screen lights up with thousands of pounds of phantom income.

After the merge, the file Xero actually wants is this:

Date,Amount,Payee,Description,Reference
2026-05-03,3200.00,HomeCorp,Salary May,FPI-202605
2026-05-03,-1450.00,Metropole Ltd,Office rent,DD-RENT-202605
2026-05-04,-12.75,Tesco,Groceries,POS-2417
2026-05-05,-89.95,O2 UK,Mobile bill,DD-O2-202605

Three differences from the Lloyds source: dates re-emitted as ISO, Debit and Credit collapsed into a single signed Amount, and the raw bank descriptions split into Payee (the merchant) and Description (the context). The sort code, account number, and running balance columns are dropped — Xero ignores them anyway, and dropping them keeps the file readable. This is the file format the converter emits when you pick the Xero CSV export.

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.

Designing the Payee column so Bank Rules actually fire

Xero's Bank Rules engine is what turns an imported statement from "750 lines of raw data" into "750 lines of auto-coded transactions ready to publish". Rules trigger on contains-style matches against the Payee, Description, and Reference columns. The Payee column you ship in the CSV directly determines whether the engine can fire — or, worse, whether it misfires.

Three principles for a Payee column that Bank Rules can work with:

1. One canonical name per merchant. A bank statement might emit STRIPE PAYMENTS UK LIMITED, STRIPE PAYMENT 2026-05-03, and Stripe Payments *123ABC for three rows from the same processor. A converter should normalise to a single Payee (Stripe Payments) and shunt the variable detail — batch reference, transaction ID — into the Reference column. One Bank Rule (Payee contains "Stripe") then catches all three.
2. Drop the noise. Acquirer prefixes (POS*, CARD*, SEPA*), country suffixes, and reference numbers belong in the Description or Reference column, not jammed into the Payee. A Payee of POS DEBIT 7724 EDEKA HAMBURGwon't match a rule for Edeka; Edeka as the Payee will.
3. Keep card-acquirer rows separate from merchant rows. If you process Stripe payouts, your bank statement has Stripe Payments credits (incoming payouts) and might have Stripe Feesdebits (separate billing). Same processor, two different chart-of-accounts targets. Don't normalise both to Stripe — leave them distinguishable so Bank Rules can route the credit to revenue and the debit to payment-processor fees.

The pattern that hurts: a converter that "helpfully" merges Payee and Description into a single field for readability. It looks cleaner in the CSV preview. It also breaks every Bank Rule because the parser can't tell where the merchant ends and the variable reference begins.

Multi-currency imports — the four edge cases

Xero handles multi-currency well in concept. In practice, four situations break the import in ways that the basic format guide above doesn't cover:

1. Account-currency mismatch.Your Xero bank account is set to EUR; the statement is for a USD account. The import works but every amount is interpreted as EUR. Total loss of accuracy. Fix: set the bank account's currency in Xero (Settings → Chart of Accounts → edit the bank account) before importing, and the CSV file's amounts will be read in the right currency.
2. FX-converted statements. Some EU banks show a USD transaction on a EUR account as the original USD amount plus an FX-converted EUR amount, often in different columns. Xero wants the booked-currency amount only (the EUR figure). Strip the USD column, or use a converter that knows the difference.
3. Multi-currency in one statement.Indian and Latin American banks sometimes ship a mixed-currency statement covering a multi-currency account, with a Currency column per row. Xero can't import this directly — the bank account is single- currency. Split the CSV into per-currency files first and import each into its corresponding Xero bank account.
4. Crypto and gold-pegged accounts.Stablecoin balances and gold-pegged accounts aren't fiat. Xero will import them but the FX rate logic is wrong. Use a dedicated crypto accounting integration or hand-import as cash transactions in the base currency.

How Xero's duplicate detection actually works

Every time you upload a statement, Xero runs the rows against the bank account's existing statement lines and flags anything that looks like a duplicate. The mechanism is undocumented in any depth, but the behaviour is reliable enough that you can plan around it. The detection key is a three-part fingerprint:

• Date — exact match.
• Amount — exact match including sign.
• Description— substring match against an existing row's Description or Payee.

Two rows hit the same fingerprint, Xero only imports the new one marked as "duplicate" — visible on the reconciliation screen with a yellow flag and excluded from the period's opening + sum + closing chain. It does not silently skip duplicates; it imports them and marks them. The practical implication: you can always see what Xero thought was a duplicate, and you can override the flag manually if it was wrong.

The scope of detection is the bank account's entire history, not a rolling window. So if you re-imported a statement from two years ago and one row matches a row from last month, Xero will flag it — even though the dates don't overlap conceptually. This catches the easy "identical rent payment, same amount, same payee" case across months: a £1,450 rent debit on the 3rd of every month genuinely repeats, and Xero flags every recurring one as a duplicate against the first occurrence. You have to un-flag each one manually, or — better — let the first import of each period stand on its own and never overlap.

Strategies for safe re-imports across overlapping periods

1. Trim the CSV to non-overlap. If you imported 1–15 May yesterday and now have 1–31 May from the bank, trim rows dated 1–15 May out of the new file before uploading. The 16–31 May rows merge cleanly with no duplicate flags.
2. Use the delete-and-replace recovery instead. If trimming is awkward (e.g. you only have the full statement and can't easily split it), delete the previous partial import via the Statements tab, then re-import the full one. Single source of truth, no duplicates to triage.
3. Differentiate recurring payments at the Reference level. A genuinely recurring payment like rent or a subscription should have a unique Reference per occurrence ( RENT-202605, RENT-202606 rather than RENTrepeated). Xero's detection key uses Description, not Reference, but a meaningful Reference makes the manual un-flagging fast — you can see at a glance which row is which.
4. Embrace the flag for low-volume cases.If you're only re-importing a few rows of overlap, accepting the duplicate flag and un-flagging the legitimate repeats by hand is faster than splitting the file. Five clicks per duplicate, no risk of trimming the wrong row.

Testing imports in your Xero Demo Company first

Every Xero subscription comes with a Demo Company — a sandbox organisation with fake data, ten bank accounts pre-configured, and the same import wizard as your live orgs. It's the right place to do two things: validate that an unfamiliar bank's CSV format imports cleanly before you risk a real reconciliation, and train new team members on the import flow without giving them write access to client data.

To switch in: from the org switcher in the top-left, scroll to the bottom of the list and click My Xero. From My Xero, scroll past your live orgs to the Try the demo company link. The Demo Company opens in a new session. Import your test CSV against any of the sample bank accounts, check the reconciliation screen, and if anything looks wrong — misparsed dates, wrong-sign amounts, missing transactions — fix the source file before you touch the real org.

The Demo Company resets every 28 days, so anything you import is ephemeral. Use it as a format scratch space, not as a record. And if you're evaluating a new converter or workflow for a practice, the Demo Company is the right place to run the end-to-end test before you point it at a client's real books.

Recovering from a bad import: the delete-and-replace workflow

Suppose you uploaded a CSV, the numbers don't add up, and you only spot it after Bank Rules have already coded half the transactions. There is a clean recovery path — and a messy one. The messy one is to manually delete lines from the reconciliation screen, which fights you when Bank Rules have already created spend or receive transactions linked to those statement lines. The clean one:

1. Accounting → Bank accounts → [your account] → Statements tab.This is the list of every statement you've imported, with date ranges and balances.
2. Find the bad import. Click Delete on the right. Xero asks for confirmation and warns you that any matched transactions will be unmatched. Confirm.
3. Xero unwinds the statement lines and unmatches any reconciled transactions. Bank Rules created from those lines (Spend Money, Receive Money entries) survive — they don't get deleted with the statement. If you want those gone too, head to Account transactions and remove them manually.
4. Re-import the corrected CSV. Xero won't flag it as a duplicate because the previous statement is gone.
5. Re-reconcile. If Bank Rules survived step 3, the matching is one-click for recurring payees.

Two important constraints. First, you can only delete a statement if no transaction in it has been reconciled against an invoice or bill in another part of Xero. If it has, you have to undo those reconciliations first (the Account transactions → Remove & Redo action). Second, deleting a statement does not delete transactions that exist in Xero for other reasons — invoices, bills, manual journals all stay. Only the bank-statement-line layer goes.

The Analysis Code column — chart-of-accounts tagging at import

Of the seven columns in the schema, Analysis Codeis the one practitioners most often ignore — partly because it's only useful if your Xero org has tracking categories configured, and partly because the column header is named after an older accountant-edition concept that doesn't carry the meaning the rest of the schema does. Worth understanding even if you don't use it: it's the one column in the schema that does meaningful work past the reconciliation step.

Tracking categories in Xero are a two-axis tagging system you set on the org — Department × Region, say, or Project × Cost Centre. Every transaction in Xero can carry an option from each category. On the reconciliation screen, the option is set when the statement line gets matched to a Spend Money or Receive Money entry. If you pre-populate the Analysis Code column in the CSV, Xero offers the code as the default when the row gets reconciled — saving a click per line on high-volume imports.

The trap: the Analysis Code column only matches against the firsttracking category configured on the org. There's no second column for the second axis. If your org has two tracking categories and you want both populated, the column gets you one of them and the second still needs a manual pick on each row at reconciliation time. Two-axis tagging is most efficient via Bank Rules (where you can hard-code both options on a per-payee basis), not via the import CSV.

Practical pattern for practitioners: skip Analysis Code on the import CSV, configure Bank Rules with both tracking-category options set, and let the rules apply during reconciliation. The import file stays simple, the tagging stays consistent, and a rule change retroactively re-codes future imports without you having to touch the source data.

Bank Rules vs Contact suggestions vs Cash coding — pick the right tool

Xero gives you three overlapping mechanisms for turning a raw statement line into a coded transaction. They look interchangeable on the reconciliation screen but they behave very differently downstream. Understanding which to use when prevents the common practice-level mess of three half-configured systems fighting each other.

• Contact suggestions.Xero notices that a Payee string matches a contact you've transacted with before and surfaces the contact + last-used account code as a one-click suggestion. It's pattern-matching with no rules to maintain. Right for low-volume reconciliation against a stable contact list where you want the human in the loop on every transaction.
• Bank Rules.Explicit rules — "Payee contains Stripe → Spend Money, account 477, Tracking option SaaS" — that fire automatically and require no human approval. Right for high-volume recurring payees where the coding is unambiguous and you want the reconciliation screen to be one-click acceptance. The cost is the maintenance: a renamed contact, a new variation of the payee string, a chart-of-accounts re-org all require rule updates.
• Cash coding. The bulk-edit grid at Bank accounts → [account] → Cash coding. Useful when you have a backlog and want to sort by Payee, code thirty rows at once, and ship them through. Doesn't persist as a rule, doesn't fire on future imports, but it's the fastest way to drain a backlog of unreconciled lines. Practitioners often pair it with rule creation: cash-code the backlog, then for any repeating payee that appeared 5+ times, retrofit a Bank Rule so the same coding fires automatically next month.

The reason this matters at import time: the Payee column you ship in the CSV is the input for all three. A clean, canonicalised Payee column (one name per merchant, no acquirer noise) makes contact-matching work, makes Bank Rules fire reliably, and makes Cash Coding's sort-by-payee view actually group what you expect. A messy Payee column — raw bank descriptions, acquirer prefixes, variable reference numbers — degrades all three at once. Worth doing the work in the converter step rather than spreading the cleanup across three Xero workflows.

Edition matrix — what changes by subscription level

Statement import behaviour is broadly identical across editions, but a few things differ enough to catch a practitioner off-guard when a workflow works in their own org and fails on a client's. The matrix below is for the 2026 plan structure:

Two implications worth flagging. First, the multi-currency edge cases earlier in this post only apply if the org is on Premium or equivalent — Starter and Standard orgs can't hold a foreign- currency bank account at all, so a USD statement on a Starter org triggers a hard error at the "set account currency" step rather than the silent mis-import we warned about. Second, Cashbook and Ledger are the "owned by the practice" SKUs that accountants use for client files the client doesn't log into. The import wizard is identical, but the org switcher behaviour in Xero HQ surfaces them differently — they show up in the Clients tab rather than the Organisations tab.

Diagnosing a "successful" import that's actually wrong

The hardest failure mode is the one where Xero reports success, the row count looks right, Bank Rules fire, and only days later does the reconciliation come up off. A short triage checklist for when you suspect an import landed wrong but you can't see where:

1. Run an opening + sum = closing chain check.Pull the bank account's opening balance for the imported period (Reports → Bank Reconciliation Summary at the start of the period), pull the closing balance (same report at end of period), and sum every imported statement line. If the chain doesn't balance, a row is wrong. If it does, the row count is right and the issue is downstream (rules, contacts, coding).
2. Sort the statement lines by date and look for gaps. A header-preamble failure usually drops the first 3–5 rows; if the imported lines start on the 4th of the month when the source statement starts on the 1st, you've found it.
3. Sort by amount and look for sign flips. Group all positive amounts and all negative amounts. If you expect the period to be roughly cashflow-neutral but the positives are 3× the negatives, the Money-In-only failure or a suffix-amount sign flip is the cause.
4. Pick five known transactions from the source PDF and search Xero for the exact amount.If they all land on the right date with the right sign, the import is structurally correct. If any one is off, you've narrowed the failure mode and can sample more aggressively.
5. Check the duplicate-flag count.An unusually high number of duplicate flags often indicates a re-import of an overlapping period — the rows aren't lost but they're excluded from the active reconciliation totals, which can produce a balance discrepancy that looks like data loss but isn't.
6. If all five steps come up clean and the books still don't reconcile, the issue isn't the import. It's a pre-existing imbalance, a manually entered transaction that doesn't belong, or an FX revaluation that fires at period-end. Stop chasing the CSV and look at the Account Transactions report for the period.

Statement Lines vs Bank Statements — the distinction that controls re-imports

Xero stores two things every time you import a CSV: a bank statement (the file record — name, date range, line count, who uploaded it, when) and the statement linesthemselves (the individual transactions that show on the reconciliation screen). The two are linked, but they're deletable independently — and that has consequences for re-imports that catch people out the first time they need to roll back a bad upload.

The bank statement record lives at Bank accounts → [account] → Manage account → Bank Statements. From there you can delete a statement, which removes every line it created — but only the lines that haven't been reconciled yet. Reconciled lines stay; they're bound to actual ledger transactions and Xero won't orphan them. If you delete a statement and the count of removed lines is lower than the count of lines the original import created, that delta is your already-reconciled subset, still in place. That is normally fine. But if you're trying to delete because the sign was wrong on every row, those reconciled-but-wrong lines need the Remove & Redopath on the reconciliation screen one at a time — there's no batch un-reconcile in the UI. For practices, the API endpoint BankTransactions/DELETE handles this in bulk, but it is not exposed to the wizard.

Statement lines without a parent statement also exist — the bank feed creates them when you don't have a statement file backing them. Switching from a bank feed to CSV imports (or vice versa) leaves a window of orphan lines that the new import doesn't replace because the dedupe check (Date + Amount + Description) sees the existing feed-created lines and excludes the CSV duplicates. The fix is the reverse of the equivalent QuickBooks bank-feed-to-file migration: disconnect the feed first, mark every pending feed-line as excluded or transferred, thenimport the CSV. The order matters. Import first and you get the ghost-duplicate pattern — the CSV rows are silently skipped because Xero thinks they're already there, but the feed rows are unreconciled placeholders with no statement parent. You end up with a book that imports "0 new lines" on a CSV that contained 400.

The questions this section answers cleanly:

• Can I delete a Xero bank statement after import? Yes — Manage account → Bank Statements, then Delete. Removes unreconciled lines, leaves reconciled ones in place.
• Can I undo a Xero bank statement import?Yes by deleting the statement, but reconciled lines persist and need Remove & Redo individually.
• Does Xero import duplicate transactions on re-upload? No, when the Date + Amount + Description triple matches exactly — but exact match is more fragile than most people assume, especially when the source CSV reformats the Description column. Re-import only the rows that genuinely changed.
• Does Xero accept Excel for bank statement import? No. CSV only. The wizard rejects .xlsx and .xls at the file picker — re-save as CSV (UTF-8) first.

Per-region cheatsheet — what to watch for

Lock Dates, MTD digital links, and the audit-trail rules that bite at import time

Once a period is closed — VAT return filed, tax return submitted, books signed off — best practice (and in the UK under Making Tax Digital, regulatory practice) is to set a Lock Date. Xero supports two: the org-wide Lock Date (Settings → Advanced → Financial Settings) that blocks any user from editing transactions before that date, and the per-user Lock Date that still lets the practice owner make controlled adjustments. Both interact with CSV imports in a way that catches teams off-guard, because the Lock Date check fires at import time rather than at reconcile time.

Importing a CSV that contains rows dated before the Lock Date fails with the message "Cannot create a transaction dated before the lock date". Xero doesn't partially import the post-lock rows and skip the pre-lock ones — the whole file is rejected, even if 99% of rows are in-range. The two workflows that work, in order of how much audit trail they preserve:

• Split the CSV at the lock boundary. Trim the file to post-lock rows only, import that, then handle the pre-lock rows as individual adjustment entries with appropriate sign-off. Slower than the alternative, but it leaves the lock intact and the audit trail clean. This is the default for regulated practices and any client with statutory audits.
• Move the Lock Date temporarily.The practice owner rolls the lock back, imports the CSV, reconciles, then rolls the lock forward again. Document the reason — under MTD the audit trail needs to show why a locked period was unlocked, and who authorised it. Xero's History & Notes log captures the lock change but not the business justification; that has to live in the practice's own procedure note.

Under the UK's 2026 MTD digital-links rules, the path from source document to Xero needs to be unbroken — no copy-paste, no manual re-keying between the bank statement and the accounting system. A CSV import counts as a valid digital link as long as the CSV was generated programmatically from the source statement (a converted PDF, a bank download, an exported feed). A CSV that was hand-typed from a paper statement does notcount; HMRC's technical guidance treats manual transcription as breaking the chain, and that breakage shows up on a Making Tax Digital compliance review. The StatementEdge converterproduces a digital-link-compliant CSV with the source PDF's hash embedded in the audit log — which is precisely the documentation HMRC expects to see on review.

EU equivalents — Italy's SDI e-invoicing platform, France's 2026 mandatory B2B e-invoicing rollout, Germany's incoming e-invoicing mandate, Spain's VeriFactu — all impose similar digital-trail requirements on the accounting record. Australia's STP Phase 2 and New Zealand's GST returns rely on transaction-level detail that the CSV import populates directly. None of these mandate a specific file format, but all of them require the import path to be reproducible from the source. The practical implication: keep the source PDF (or original CSV from the bank) alongside every Xero import, not just the converter output. The pair is what regulators want to see, not either one alone. Our data-residency and retention policy documents how StatementEdge stores the source-file hash and the converted CSV side by side for as long as the retention window allows.

What about the "Plain CSV" option vs the "Xero template"?

Xero's import wizard has two paths: the auto-detected upload (what we've been describing) and a longer flow where you map columns manually. The auto-detected path only works if your column headers exactly match the names in the schema above (Date, Amount, Payee, etc.). Get the headers right and the import is two clicks. Get them wrong and Xero drops you into the column-mapper, where you have to point at each column and tell Xero what it is.

The column-mapper is fine when you're uploading a one-off file with weird headers. It's painful when you're reconciling five clients a week. The Xero CSV export from our converteruses the exact header names that skip the mapper — saving a couple of minutes per import and ruling out the entire class of "mapped to wrong column" mistakes.

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.

The practitioner batch pattern

If you're reconciling a single client account once a month, the workflow above is fine to do by hand. If you're a bookkeeping practice with twenty or fifty clients, the per-client click-through adds up to a real chunk of month-end. Two patterns scale it down:

• Batch convert, sequential import.Drop every client's PDF into a batch on StatementEdge, let the converter process them in parallel, then download a folder of Xero CSVs named per client. The conversion stage parallelises; the upload stage is still per-client because Xero's wizard is one file at a time. You save the OCR time, not the click-through time. Good for practices where converting was the bottleneck.
• API-driven, no clicks. The StatementEdge REST API and MCP server returns the same Xero CSV programmatically. Pair it with the Xero API's BankTransactionsendpoint and you can push imported lines directly into Xero, bypassing the wizard entirely. The Xero API doesn't accept CSV uploads, but it does accept structured transaction objects — the converter's output is easy to map. Worth the integration effort once your practice is at 25+ active clients.

For practitioners working between these two scales, the accountants and bookkeepers workflow guide covers seat sharing, retention policies, and the team-account configuration that keeps client data segregated.

Conversion balances vs opening balance vs the first imported row — the new-Xero-org trap

Every Xero file fresh out of setup hides a three-number puzzle that catches new bookkeepers and migrating clients in roughly equal measure. The first CSV import lands cleanly, every line reconciles, the Account Transactions report ties out — and yet the Bank Reconciliation panel insists the Statement Balance and the Bank Balance disagree by a stubborn amount that nobody can locate. The cause is almost never the CSV. It's the gap between three balances Xero treats as independent inputs and that the import wizard never reconciles for you.

• Conversion balance — set at Settings → Advanced → Conversion Balances. This is the cumulative position of the bank account on the conversion date, i.e. day one of your Xero life. It anchors the opening trial balance for the entire ledger and feeds the comparison column on every report. Get this wrong and the Balance Sheet is wrong from the first day onwards.
• Bank account opening balance — set at Bank accounts → [account] → Manage account → Edit account details → Opening balance. Xero pre-fills this from the conversion balance, but it's editable, and editing it decouples the bank account from the conversion record without warning you.
• First imported row balance— the implicit balance Xero infers from your CSV at the date of the first statement line. There's no UI field for this; Xero computes it as (opening balance + first row amount).

The reconciliation panel checks that Statement Balance = opening balance + sum of imported lines, and that Bank Balance = sum of reconciled transactions in Xero. Those two should match. They will, eventually. The third number — the conversion balance — sits one layer underneath, and a mismatch between it and the opening balance produces a difference on the Balance Sheet's bank-account line that the reconciliation screen will never flag, because the reconciliation screen doesn't look at the conversion record.

The clean pattern for a new Xero org reconciling its first statement is: set the conversion date to the day immediately before the first statement starts, set the conversion balance to that statement's opening balance, leave the bank account's opening balance untouched (Xero will pre-fill it from the conversion record), and import the CSV straight in. Three numbers, one source of truth, no drift. The first-imported-row balance falls out correctly because the arithmetic chains forward from a single anchor.

The drift pattern that breaks reconciliation later — and is the single most common cause of the "everything looks right but the Balance Sheet is off" complaint — runs the other way. The conversion date is set arbitrarily (e.g. January 1 of the tax year) without checking what statement period that lands inside, the conversion balance is keyed in from the prior accountant's signing-off file, and the first imported CSV starts mid-period without a chained earlier statement to bridge the gap. Xero accepts everything and silently carries the gap as a permanent Balance Sheet difference. The fix once you're in this state is either (a) backfill the missing statements between the conversion date and the first imported one — usually the right answer if the gap is short — or (b) post a single adjusting journal at the conversion date that flushes the difference into whichever opening-balance account the prior accountant used to get the trial balance to zero. Pick the latter only when the client signs off the journal, because it survives in the audit trail as a manually-placed adjusting entry forever.

Bank Rules: conditional matching, percentage splits, and the priority order

The introductory Bank Rule — "if Payee contains 'STRIPE' then code to Card processing fees" — is the entry-level case and the only one most Xero training material covers. The 2026 Bank Rules engine supports four patterns the standard documentation glosses over. Each one removes a category of manual reconciliation from your monthly routine, and the order in which you stack rules determines whether the import lands cleanly or buries you in misallocations.

Conditional matching with AND / OR groups. A Bank Rule can chain multiple conditions joined by AND, and inside any individual condition Xero supports an OR alternative. The match logic reads: every AND group must match, and within each group anyof the OR alternatives counts. So "Payee contains 'AMAZON' OR Payee contains 'AMZN' AND Amount less than 50" fires on every small Amazon transaction regardless of whether the payee string comes through as AMAZON.COM, AMAZON UK, or the compressed AMZN MKTPLACE form some banks truncate to. Read the rule top-to-bottom in the editor to verify the parenthesisation — Xero indents the OR alternatives under their parent AND, so a misplaced condition is visually obvious before you save.

Percentage splits across the chart of accounts.A single statement line can split into multiple coded entries by percentage. The classic mixed-use case is a director's phone bill at 80% business / 20% personal — set up one rule allocating 80% to Telecoms and 20% to Director's loan, and every transaction matching the rule splits at import. The same pattern works for the office-cleaning bill where 8% of the cost is the kitty for milk and tea (non-deductible), the broadband line where a known share is a flatmate's contribution, and any rebate the bank books gross with a separate fee deduction. The split applies at reconcile time, not at import time, so the underlying statement line still shows in the reconciliation panel; Xero just pre-fills the split when you accept.

Reference-driven contact matching.If the bank populates the Reference column with a unique per-payee identifier — most SEPA Credit Transfers, most BACS Direct Debits, and every Faster Payment that respects the end-to-end ID field — a Bank Rule can match on Reference instead of Payee. This is the most reliable match available because bank-provided references don't drift the way payee strings drift across statement cycles. The trade-off: the converter has to populate the Reference column from the right source field. The Xero CSV our converter emits places the bank end-to-end ID into Reference precisely so reference-driven rules survive re-runs.

Priority order.When multiple rules could match the same statement line, Xero applies them top-to-bottom from the Bank Rules list. Drag specific rules above general ones. The recurring failure mode: a "Stripe fees" rule sits below a generic "Card processing" rule, and every Stripe transaction lands in the wrong account because the general rule fires first. The fix is a thirty-second drag, but the underlying discipline is to keep the rule list ordered most-specific to least-specific and to use the Run Rule action on historic transactions before saving any change — it shows you which lines the rule would have applied to, so you catch over-broad conditions before they reach a live import.

Find & Recode + Cash Coding — the post-import cleanup tools that close the loop

Even with a perfectly-formed CSV, imports land lines that need re-coding after the fact. A Bank Rule was misconfigured, the chart of accounts moved, a VAT treatment changed mid-period, a client added a new revenue stream that nobody's rule set knows about yet. Xero has two purpose-built tools for fixing post-import lines at scale, and they map onto different points in the reconciliation lifecycle. Both are underused by people who reflexively re-import the whole CSV every time something looks off.

Cash Coding lives at Bank accounts → [account] → Cash codingand presents a grid view of unreconciled statement lines. You can sort by Payee, Date, or Amount; filter to a subset; bulk-select; and apply a Contact + Account + Tax Rate to every selected line in a single save. It's the right tool for the morning after a fresh CSV import, when 200 lines are sitting in unreconciled state and 60 of them are obviously the same recurring payee. The constraint: Cash Coding only operates on statement lines that haven't been reconciled yet. The moment a line is matched and saved, it leaves the Cash Coding grid for good. So the cadence matters — open Cash Coding after the import and before you start reconciling individual lines. Reverse that order and the grid is empty.

Find & Recode is the heavier tool: Adviser menu → Find & Recode, available on Cashbook, Ledger, Standard, Premium, and the practice-owned SKUs — not on Starter / Ignite. It searches across already- reconciled transactions and lets you bulk-update the account code, tax rate, tracking categories, or contact on the matches. Use it when a coding error that you missed at import time has been compounded by a Bank Rule firing across three months — Find & Recode fixes them all in one operation, with an audit-log entry per affected transaction, no un-reconcile / re-reconcile cycle required. It's the safest way to retro-fix coding without disturbing the bank reconciliation state.

The combined practitioner pattern that takes a CSV from import to clean reconciliation in one sitting:

1. Import the CSV.
2. Open Cash Coding immediately, sort by Payee, and bulk-code the obvious groups (same payee, same coding, same VAT treatment).
3. Reconcile the trickier lines individually on the reconciliation screen, letting Bank Rules and Contact suggestions handle the recurring ones. Save the genuinely-unique ones with a Spend Money or Receive Money entry.
4. A week later, audit the reconciled set with the Account Transactions report. If a coding pattern was mis-applied across dozens of lines, use Find & Recode to fix them in one operation rather than per-line.

The whole loop closes inside Xero. The converter's job is to make step 1 produce a CSV that doesn't need the loop in the first place — but at scale, even a perfect CSV sometimes does. Cash Coding and Find & Recode are the answers, not a re-import that risks deduplication misses, lock-date conflicts, or a deleted-statement audit-trail gap.

Payment-processor "bank" accounts — Stripe, PayPal, Wise, GoCardless

Most Xero CSV-import pain in 2026 isn't the real bank account. It's the Stripe, PayPal, Wise, and GoCardless "bank" accounts that live alongside it. These are technically clearing accounts — money sits there for a few days while the processor takes its cut — but Xero models them as bank accounts and most practitioners import them as CSVs because the native feeds are partial, inconsistent across regions, or unavailable on the client's plan. Each processor has a specific failure mode that the generic CSV rules above don't catch.

Stripe.The Stripe-issued CSV you'll find in the Payouts → Reconciliation report contains gross charges andfeerows as separate lines. The payout to your real bank account is the net. If you import the gross lines into a Stripe Xero bank account, then also import the net payout into your real bank account, you double-count revenue. The correct shape is: Stripe-account CSV gets gross charges as Money-In and Stripe fees as Money-Out (both signed Amount column), with the payout to the real account as a transfer (Money-Out from Stripe, Money-In to the real account). That gives you a Stripe balance that clears to zero after each payout cycle and a fee total that lands in the right P&L bucket. The reconciliation checkerwill flag any cycle where the fees-plus-payout doesn't equal the gross charges — the classic sign that one of the three feeds dropped a row.

PayPal. The exported activity CSV contains transactions in allcurrencies on one tab — your home currency, every non-home currency you've received in, plus the auto-conversion legs. Xero needs one CSV per currency per bank account (see the multi-currency section above). The processing step is brutal: filter by Currency column, then split. PayPal's "Balance Affecting" column is the one Xero needs — not the gross sale amount. Get that wrong and you import the customer-paid number while PayPal keeps the fee, which means your Xero PayPal balance drifts further from the real PayPal balance every month.

Wise.Wise statements are clean — but Wise transfers between currencies produce two rows in two different account CSVs, and if you import both as standalone bank transactions instead of as a transfer, you book the same money twice. The discipline: every Wise FX conversion row pairs to exactly one row in the receiving currency's Wise account on the same date. Match them, code them as transfers, not as income or expense.

GoCardless.The two-leg pattern: customer mandate collects on date X (Money-In to GoCardless), GoCardless pays out on date X+2 (Money-Out from GoCardless, Money-In to real account). If your CSV only contains the payout side, you lose the per-customer attribution and Bank Rules can't suggest the right Contact. Export the full activity, not just the payouts.

If you're converting processor statements (the PDF version some processors still email out, or a screenshot exported from a partner portal), the same StatementEdge converter handles them as long as the document has a transaction table — the output already separates gross, fee, and net columns when the source distinguishes them, which removes the manual unpicking step.

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.