What does Xero actually require?

Two things: a Date column and an Amount column. Everything else — Payee, Description, Reference, and two extra columns for a bank’s analysis code and transaction type — is optional. The optional ones are what make reconciliation quick rather than tedious, so include them if you have them.

Why did my import create duplicate contacts?

Because a Payee name in your file did not match an existing Xero contact character for character. Xero creates a second contact rather than matching the first. Across a year of statements this leaves a contact list full of near-duplicates for someone to merge by hand, so it is worth reviewing the Payee column before you upload rather than after.

My file has more than 100,000 rows. What now?

Xero will not import it at all. Its own guidance is to delete empty rows first, and if the file is genuinely that long, to use OFX or QIF instead of CSV. Both are formats we export, so that route does not cost you a re-conversion.

Should I use CSV or OFX for Xero?

CSV is the usual choice and gives you the most control over what maps where. OFX is the better answer for a very large file, or when you would rather not think about column mapping at all. One conversion gives you both, and taking one does not use up the other.

The Xero CSV bank statement import format

Q: How should expenses be written?

Income and expenses share a single Amount column. Income is positive; expenses are negative, written either as -30.00 or as (30.00). Do not split them into two columns, and do not use a comma to mark decimal places.

Xero’s CSV rules are shorter than QuickBooks Online’s, and less rigid in the place that matters most: Xero does not care how many columns your file has, because it asks you to map them. What it does care about, it cares about absolutely.

The two required fields

Date and Amount. That is the whole mandatory list.

Everything else is optional: Payee, Description, Reference, and two further columns for a bank’s own analysis code and transaction type. Optional does not mean unimportant — Payee drives Xero’s contact matching and its bank rules, and Description is what you actually see on the reconciliation screen — but a file with only two columns will import.

The sign convention

Income and expenses go in one Amount column. Income is positive. Expenses are negative, written either as -30.00 or as (30.00).

This is where a bank statement converter’s output most often needs work, because plenty of banks print debits and credits as two separate columns and plenty of converters reproduce that faithfully. Merging two columns into one signed column across several hundred rows is exactly the kind of manual step that turns a two-minute job into an afternoon.

Our CSV export writes that column already signed — money in positive, money out negative, in a single column, merged for you when the statement printed them apart. Pick Xero as the export destination and you get a Xero-shaped file outright: Date, Amount, Description and nothing else, the two fields Xero requires plus the one that makes the reconciliation screen readable. Take the plain export instead and you get all thirteen of our columns and map them at the Xero end. Either way you are not rewriting signs.

Why the Xero file has no Payee column. Xero matches Payee against your contact list character for character and creates a new contact when it does not match — the trap described further down this page. All we have to put there is the wording the bank printed, which is precisely the input that produces near-duplicates. So we leave the column out and let Xero’s own bank rules do the matching, which they do better than a bank’s raw description string ever could.

Decimals, and the ceiling

Commas must not be used to mark decimal places. A file written for a European locale, where 1.234,56 is ordinary, will not import correctly.

More than 100,000 rows and Xero refuses the file entirely. Xero’s own guidance is to delete empty rows first — a spreadsheet that looks like 400 rows can carry tens of thousands of empty ones — and, if the file really is that long, to use OFX or QIF instead. Both are exports you already have.

The Payee trap

This is the rule that costs people time weeks after the import, not during it.

If the Payee name in your file does not match an existing Xero contact character for character, Xero creates a second contact instead of matching the first. AMAZON MKTPLACE and Amazon Marketplace are two different suppliers as far as the import is concerned. Do this across twelve months of statements and you inherit a contact list full of near-duplicates that someone has to merge by hand, long after anyone remembers where they came from.

The cheap defence is to look at the Payee column before you upload rather than after. If your descriptions are raw bank strings, consider leaving Payee out of the mapping entirely and letting Xero’s bank rules do the matching from the Description instead.

Source: Xero Central, import a bank statement in CSV format.

One practical note about our file

Our plain CSV carries the unread-content notice inside the file itself — a line at the top and a list at the bottom, each beginning with a hash. That is deliberate: the person who opens your spreadsheet is often your accountant rather than you, and a warning that lives only on a website does not travel.

Those lines are not transactions. If you upload the plain export, delete them first, along with any columns you are not mapping. The Xero-shaped export has no such lines — it is three clean columns — and when that conversion has open questions the file arrives named …-CHECK-BEFORE-IMPORT.csv instead, with the details on the results page and in the plain CSV. What every column in the CSV means.

Check the file before you import it, not after

Xero will happily accept a file that is internally consistent and wrong. Before you map a single column, add the opening balance to every credit, subtract every debit, and hold the result against the closing balance printed on the statement.

If they do not meet, a row was dropped, duplicated or read with the wrong sign — and a broken month inside Xero is considerably more work to unpick than a broken file on your desktop. We run that check for you on every upload, per account, before you pay anything, and separately report any part of the document that never made it into the table at all.