Xero Bank Statement CSV Mapping: Date, Amount, Debit/Credit, and Skipped Rows

To import a bank statement into Xero manually, your CSV needs at minimum a Date column and an Amount column, and at the mapping step you tell Xero which of your columns is which. Most failed imports come down to three things: the date format doesn't match your Xero organization's region setting, the amount column contains currency symbols or thousands separators, or there are blank rows and report footers that Xero can't parse. Fix those before you finish the mapping step and the import goes clean.

This guide covers only the manual bank statement import in Xero (Accounting > Bank accounts > your account > Import a statement). It does not cover invoices or contacts. If you also import sales invoices or a contact list, those use different templates and different rules. Here we go deep on the one thing that trips people up every month: getting a bank export mapped to Date and Amount without losing rows.

What columns does Xero need in a bank statement CSV?

Xero is deliberately minimal here. You only need two fields to create statement lines, and everything else is optional detail that makes reconciliation easier later.

Your header names do not have to match these labels. Xero does not read your header text to decide what each column is — you assign each column to a Xero field yourself in the mapping dropdowns after you upload. So a bank that exports a column called "Transaction Amount" or "Value" is fine; you just map it to Amount. What matters is that the data in each column is clean and in a format Xero can parse.

Single amount column vs separate debit and credit columns

There are two valid amount layouts, and you pick one based on how your bank exports. Do not try to use both at once.

Single Amount column: one column holds every transaction. Money received is a positive number, money spent is negative. Xero documents both a leading minus sign and brackets for negatives (-30.00 or (30.00)), but a plain leading minus sign is the safest choice: a minus sign is never ambiguous, whereas bracket-wrapped amounts get misread or rejected in some exports. Use -30.00 for money out.

Single-amount layout (map the last column to Amount)

Date,Description,Amount
15/03/2026,ACME SUPPLIES,-249.00
16/03/2026,CUSTOMER DEPOSIT,1450.00
17/03/2026,BANK FEE,-12.50

Separate Debit and Credit columns: your bank splits money out and money in into two columns, and in most raw exports both are positive numbers. Xero can handle this — you map one column to Debit and one to Credit at the mapping step. The common failure here is that a row has a value in both columns, or a blank where a zero should be, which produces an ambiguous or empty amount.

Debit/Credit layout (map two columns separately)

Date,Description,Debit,Credit
15/03/2026,ACME SUPPLIES,249.00,
16/03/2026,CUSTOMER DEPOSIT,,1450.00
17/03/2026,BANK FEE,12.50,

Why do my dates fail or import to the wrong day in Xero?

This is the single biggest source of bank import problems. Xero validates every date against your organization's region date setting, not your computer's locale and not whatever your bank used. If your Xero org is set to DD/MM/YYYY and your file has 03/15/2026, the day value 15 is invalid as a month, so the import fails outright.

The more dangerous version is silent: when both the day and the month are 12 or under, an MM/DD date and a DD/MM date are both valid but mean different days. 05/03/2026 imports as either March 5 or May 3 depending on the setting, with no error — you just get transactions posted to the wrong day, which breaks reconciliation weeks later. Check your format first under Settings > General Settings (Organisation date format), then make the whole date column match it exactly.

Why does Xero skip rows or say there's no valid statement data?

When Xero reports that a file "does not contain valid statement data," or quietly imports fewer lines than your file has, the cause is almost always structural junk around your data rather than the transactions themselves. Bank and accounting-portal exports are full of it.

The frustrating part is that Xero doesn't hand you a per-row error report. It parses what it can and skips the rest, so you find out by comparing the imported line count to your file. That's why cleaning the file before you upload beats fixing it after.

The mapping step: preview before you finish

After you upload the CSV, Xero shows a mapping screen with a dropdown over each of your columns. You point Date at your date column, Amount at your amount column (or set Debit and Credit if you're using the two-column layout), and optionally map Payee, Description, Reference, and Check Number. Xero shows a preview of how the first rows will import.

Actually read that preview. It's your last checkpoint before the data lands in the bank account. Confirm the dates show the day you expect (not a flipped month), that amounts have the right sign — negatives for money out — and that the first previewed row is a real transaction and not a leftover header or title line. If anything looks off, cancel, fix the file, and re-upload rather than pushing through.

Xero remembers your mapping. Once you've mapped a given bank's export layout, it applies the same import settings the next time you bring in a statement with the same structure, so the mapping step gets faster month over month. You can adjust the saved behavior under the option to change how Xero imports CSV bank statements. The catch: a saved mapping only helps if your bank keeps exporting the same column order. If a column moves, re-check the dropdowns.

Before and after: a real bank export fixed for Xero

Here's a typical raw download from an online banking portal. It has a title row, a summary line, currency symbols, comma thousands separators, US-style dates going into a UK org, and a footer.

BEFORE — as exported (fails or skips rows)

Account 12345678 - Statement
Date,Details,Debit,Credit
03/15/2026,ACME SUPPLIES LTD,"$1,249.00",
03/16/2026,CUSTOMER DEPOSIT,,"$2,450.00"
03/17/2026,MONTHLY FEE,$12.50,
Closing balance,,,"$1,188.50"

AFTER — cleaned for a UK org, single Amount column

Date,Description,Amount
15/03/2026,ACME SUPPLIES LTD,-1249.00
16/03/2026,CUSTOMER DEPOSIT,2450.00
17/03/2026,MONTHLY FEE,-12.50

What changed: the title row and closing-balance footer are gone; the header is now row 1; dates are DD/MM/YYYY to match the org; the dollar signs and thousands commas are stripped; and the Debit/Credit split is collapsed into one signed Amount column (debits negative). That file maps to Date and Amount in seconds and skips nothing.

Your pre-import checklist

If you're fixing the same messy bank export every month, doing it by hand in Excel is slow and risks re-introducing the date and leading-zero problems Excel is famous for. A cleaning pass that trims whitespace, standardizes the date column, strips currency symbols, and removes empty rows and footer lines gets you a Xero-ready file consistently. PipeSheets does exactly that kind of prep — you upload the raw export, run a saved cleanup, preview the before-and-after with detected column types, and download a CSV that maps straight to Date and Amount. It won't map columns inside Xero for you, but it hands Xero a file that maps without a fight.

Get the structure right once — clean header, clean dates, one clear amount layout, no footers — and the Xero mapping step stops being the monthly headache. The transactions were never the problem; the wrapper around them was.