The 30-second version
UK and Irish bank statements break the generic "PDF to Xero" tutorials for three reasons: Money-In/Money-Out columns, comma-decimal amounts on Irish statements, and ambiguous DD/MM dates that Xero re-parses as MM/DD. The fix is a converter that emits a Xero-native CSV with the columns merged, the amounts locale-corrected, and the dates explicitly DD/MM/YYYY — bypassing the whole column-mapper friction.
If you've ever tried to import a UK or Irish bank statement into Xero via CSV, you know the experience: half the rows date-parse wrong, the amounts look right but reconciliation is off, and somehow Money In and Money Out columns ended up as twoAmount fields Xero doesn't know what to do with.
Every generic "how to convert PDF bank statement to Xero" tutorial online is written from a US perspective. They don't mention any of the things that actually break UK/IE imports. This post is the EU/UK-specific workflow that works — for AIB, Bank of Ireland, Barclays, Lloyds, NatWest, HSBC UK, Halifax, Santander UK, Monzo, Starling, and Wise. We'll cover the three universal locale traps, the per-bank quirks, the actual Xero CSV format spec, and what to do when Xero throws one of its three common import errors.
The three things that break the generic tutorials
1. Comma decimals vs period decimals
Irish, German, French, and many other EU statements format amounts as €1.234,56 — period thousands, comma decimal. Xero parses €1.234,56 as one thousand two hundred and thirty four point fifty-six only if the CSV is converted; left as-is, Xero often reads it as 1,234.56, or 1.23456, or just rejects the row.
UK statements are friendlier on this — they use period decimals like the US. But Irish AIB and Bank of Ireland are EU-format. Don't assume because it's in English it's in US locale.
2. The Money In / Money Out trap
Barclays, Lloyds, NatWest, HSBC UK, Halifax, AIB, Bank of Ireland, Permanent TSB, and Australian banks (Commonwealth, ANZ, Westpac NAB) all use two amount columns: Money In (credits) and Money Out (debits). Export the bank's own CSV and you get those two columns side by side. Xero's import wizard expects ONE signed Amount column where debits are negative.
Manually merging the two columns is error-prone. Spreadsheet formulas to combine them introduce off-by-one bugs that surface as missing transactions later. The right move is to collapse them to a single signed amount during conversion, before the CSV ever hits Xero.
The Excel merge formula that quietly drops your transactions
A common DIY pattern: =IF(MoneyOut>0, -MoneyOut, MoneyIn). Fine until you hit a row with a positive entry in both columns — rare but legal on some Irish statements where a same-day debit+refund pair appears unmerged. The formula picks the wrong one silently. Net effect: one transaction gets the wrong sign and your books drift by twice the original amount.
3. DD/MM/YYYY dates
Xero accepts DD/MM/YYYY in its UK locale settings and MM/DD/YYYYin US locale settings. If your Xero org is set to UK but you've imported a CSV with ISO dates, Xero often re-parses them as MM/DD/YYYY for ambiguous days (anything where the day ≤ 12). All your January 2nd entries become February 1st. No error message; the dates just silently change.
Fix: either (a) configure Xero's locale correctly, OR (b) export the CSV with DD/MM/YYYY dates explicitly. Our Xero export option does the second by default so it works regardless of org locale.
If you only fix one thing on your CSV before importing to Xero, fix the date column. Locale-mismatched dates are the most common cause of bank rules misfiring later.
The actual workflow
- Drop your PDF on statementedge.com. Our locale detector handles the comma decimal + DD/MM/YYYY + Money In/Out parsing automatically — no per-bank configuration.
- Wait 20–40 seconds for the conversion. Check the reconcile badge — if it's green or amber (with reviewable flags), you're ready to export. (Red means bookend totals don't match; see what "auto-reconciled" actually means.)
- Click Accounting → Xeroin the download menu. The output is a CSV with Xero's expected columns:
*Date,*Amount(signed),Payee,Description,Reference,Cheque Number. - In Xero: Banking → Manage Account → Import a statement. Pick the CSV. The mapper auto-detects the asterisked columns — no manual mapping needed.
Bank-by-bank quirks worth knowing
AIB (Allied Irish Banks)
Issues consolidated PDFs covering multiple months in one document. The default export has section headers between months that look like transactions to weaker parsers. Our pipeline ignores the header rows and treats the multi-month run as a single statement, with the reconcile check applied per-period and overall. Joint-account statements also append the second account-holder name to the Description column — preserved as-is on export.
Bank of Ireland
Same Money In / Money Out layout as AIB but with currency suffix appended to each amount (€450.00 EUR). The suffix is harmless if your converter strips it; problematic if it doesn't. Watch out for credit-card-style statements from BOI that flip the column meanings (their card products use Debits/Credits inverted vs current accounts).
Permanent TSB
Uses a single signed-amount column rather than Money-In/Out — easier to extract but the sign convention is the opposite of what Xero expects on some account types. A reconciled converter spots the inversion via the chain check.
Lloyds Bank
Long statements use Money In / Money Out + a running balance column. Watch out for the "Balance carried forward" line at page breaks — some parsers treat it as a transaction and inflate the row count by one per page. On business statements, the "Bank Reference" column often contains free-form text that breaks naïve CSV parsers; needs proper escaping on export.
Barclays
Business statements have additional reference fields below each transaction (sort code, beneficiary name) that aren't part of the amount table. Those should land in the Description / Reference fields rather than getting promoted to their own row. Personal Barclays statements are cleaner — single Money-In / Money-Out layout with no per-row reference rows.
NatWest
Issues both PDF and CSV exports. The PDF has cleaner formatting; the CSV often has hidden encoding issues (Windows-1252 vs UTF-8) which can break pound-sign rendering and special characters in payee names. Always start from the PDF where you can.
HSBC UK
Uses DD MMM YYYY dates (e.g. 02 May 2026). Most Xero CSV tutorials assume DD/MM/YYYY; HSBC's format needs explicit handling. Multi-currency HSBC accounts split per-currency sections on the same PDF — every currency needs its own Xero account in your chart, not one combined feed.
Halifax
Sibling brand to Lloyds; layout is similar. Their app-export PDF differs from the web-export PDF in subtle ways (extra header on web export). Either works once normalised.
Santander UK
Money In / Money Out layout, GBP, DD/MM/YYYY. Their PDF embeds a sort code on every page header; harmless if stripped before extraction.
Monzo and Starling
App-first banks. Both issue app-generated PDFs with a single signed Amount column (cleaner than the legacy two-column layouts). Date format is DD MMM YYYY. Reconciliation is straightforward because the running balance is printed on every row.
Wise (TransferWise)
Multi-currency by default. The statement PDF reports each currency in its own section. Treat each currency as a separate Xero bank account — don't try to combine them in one feed (Xero exchange rates won't match historical transaction rates).
The Xero CSV format, spelled out
Xero's import wizard expects a header row with column names. The asterisked columns are required:
| Column | Required | Format / notes |
|---|---|---|
*Date | Yes | DD/MM/YYYY in UK/IE org; ISO works if org locale is set explicitly |
*Amount | Yes | Single signed value; debit = negative |
Payee | No | Free text; what Xero shows as the "who" |
Description | No | Free text; longer detail |
Reference | No | Bank reference number or sort code |
Cheque Number | No | If applicable |
Analysis Code | No | For chart-of-accounts tagging |
Xero accepts any other extra columns silently — they're just ignored. So attaching the bank-statement reconciliation badge as an extra header line is safe; Xero won't complain, and you have an audit trail.
The three Xero import errors and how to fix them
- "Some rows could not be imported." Usually a date in the wrong format. Check that every row matches the org-locale date format exactly. Xero stops on the first bad row; fix one and retry.
- "Amounts are not valid numbers." Locale mismatch on the Amount column — commas where Xero expected periods. Re-emit the CSV with dot-decimal amounts.
- "Duplicate transactions detected."You imported an overlapping period twice. Xero deduplicates by Date + Amount + Description; if all three match a previous row, it's flagged. Trim your CSV to the non-overlapping period or accept the duplicates flag.
Bank rules > re-importing every month
Once you have one clean Xero CSV imported for a bank, build Bank Rules for your common payees (rent, utilities, regular suppliers). Xero auto-applies the rules on every subsequent import, so reconciliation goes from 30 minutes to 30 seconds. Rules attach to Description matches — which is why getting clean Description fields out of the converter matters more than "just the numbers".
Per-statement upload vs bank feeds — which to use when
Xero offers two paths: live bank feeds (Open Banking via Yodlee/Plaid/etc.) and CSV statement upload. Both are valid; they serve different jobs.
- Bank feeds for live operational accounts where you reconcile daily or weekly. Real-time, but only works for banks Xero has a feed for, and breaks regularly on connection re-auth.
- Statement uploadfor monthly batch reconciliation, for accounts on banks without a Xero feed, for clients who can't connect their credentials, and for historical periods (going back beyond what the feed covers).
For accountants doing bookkeeping for many small clients, statement upload tends to be the workhorse — feeds are great when they work but the maintenance overhead per client is real. A reliable PDF-to-CSV pipeline beats reconnecting Open Banking on five clients every Monday morning.
One-sentence summary
Use a converter that knows your bank's locale and emits a Xero-native CSV directly. The manual middle steps — mapping columns, merging Money In/Out, fixing dates — are where accuracy gets lost.
Try it on one of your own statements: drop it here. The Xero export is two clicks away.
Further reading
- What "auto-reconciled" actually means — the verdict you should see on every conversion.
- Why your QuickBooks import keeps failing — same locale traps apply to QBO.
- How to choose a bank statement converter — broader evaluation checklist.
- All UK bank pages · All Irish bank pages.