A Xero bank statement import CSV is a plain text file with one transaction per row and a header row naming the columns. Xero looks for a date, a signed amount, a payee, a description and a reference. Get the date order and the minus signs right, and the file loads without errors.
Xero's CSV template, column by column
Xero's CSV template is the list of columns the statement import expects, one transaction per row. The exact template is offered on the import screen, and it is the authority if it differs from this page. The five columns below are the ones this guide uses, and they cover what most small businesses need.
The five columns
| Column | What it holds | Example value |
|---|---|---|
| Date | The day the bank posted the transaction | 03/02/2026 |
| Amount | One number, negative for money out | -86.42 |
| Payee | Who was paid or who paid you | Pinecrest Office Supply |
| Description | What the transaction was for | Office supplies |
| Reference | A number that identifies it to you | INV 1042 |
All values are examples. Treat the first two columns as the ones Xero cannot do without, since a transaction with no date or no amount is meaningless. Payee, description and reference add detail that helps when you match bank lines to bills and invoices later.
Why payee and reference deserve their own columns
If you cram everything into one description cell, Xero shows one long string and you lose the chance to search by payee or match by reference. A bank statement usually prints one run of text. Splitting it before the import costs a little time once and saves time on every reconciliation afterward. The PDF-specific version of that splitting is described in importing a PDF bank statement into Xero.
A sample file you can copy
This file has six rows. Copy it into a text editor, save it with a .csv ending, and change the values to your own.
Date,Amount,Payee,Description,Reference
03/02/2026,1250.00,Brightwater Cafe,Client payment,INV 1042
03/04/2026,-86.42,Pinecrest Office Supply,Office supplies,
03/09/2026,-24.00,Cloudnest Web Services,Website hosting,
03/12/2026,2400.00,Hollis & Grant,Client payment,INV 1045
03/18/2026,-310.00,Northfield Coworking,Desk membership,
03/27/2026,-12.00,Fernhill Community Bank,Monthly service fee,
The business, bank and payees are fictional. Notice that rows without a reference still keep the final comma, so every row has the same number of columns.
Three ways the same rows go wrong
The same six rows can fail in at least three common ways. Each one below changes the file in a small way.
Broken one: the header is missing or renamed.
03/02/2026,1250.00,Brightwater Cafe,Client payment,INV 1042
03/04/2026,-86.42,Pinecrest Office Supply,Office supplies,
Without a header row, the first transaction is treated as column names, or the file is refused. Always keep the header as row one.
Broken two: separate debit and credit columns.
Date,Debit,Credit,Payee
03/04/2026,86.42,,Pinecrest Office Supply
03/12/2026,,2400.00,Hollis & Grant
This is how many bank exports look, and it forces you to choose a mapping. The fix is one Amount column, covered in the section on signed amounts.
Broken three: a comma inside a name, with no quotes.
Date,Amount,Payee,Description,Reference
03/12/2026,2400.00,Hollis & Grant, LLC,Client payment,INV 1045
The extra comma makes six values in a row built for five. Quotes around the name fix it, as shown in the last section.
Which date format does Xero's CSV import read?
Xero reads dates in the order your organisation's region uses, so a file can load without an error and still put transactions in the wrong month. The date format is the setting most likely to cause a quiet mistake. Check it with a test: find a row whose day is above 12 and see where it lands.
Why Xero swaps day and month
A date such as 03/04/2026 means March 4 to a US reader and April 3 to many readers elsewhere. When both numbers are 12 or below, nothing in the text says which is which. Software must assume an order, and an assumption that differs from yours moves the transaction by up to eleven months, or by a few days within a month. Because the result is a valid date either way, there is no warning.
A test you can run in two minutes
- Add a row dated on the 25th, such as 03/25/2026.
- Upload the file and open the preview.
- If the 25th appears as an error or as an empty date, Xero reads day first and your file is month first.
- Switch the file to the other order, or use the unambiguous form year-month-day if your Xero accepts it.
The product homepage says its Xero file is written with dates "in the order your Xero region expects", which is the behaviour to copy by hand: write every date in the same order, with no month names and no two-digit years.
Excel and dates
Excel stores a date as a number and shows it using your computer's regional setting. Saving as CSV writes the display format. If your computer is set to day-first and your Xero organisation is month-first, the file will be wrong even though it looks fine on screen. Format the date column as text in the right order before you save, or use a formula such as a text function to write each date out in a fixed pattern.
One signed amount or separate Xero debit and credit columns?
For the import file in this guide, use one Amount column where money out is negative and money in is positive. Many bank exports instead give a Debit column and a Credit column. If your file has those, merge them into one number before uploading, because that is the simplest layout to check.
How to merge two columns into one
In a spreadsheet, put the credit amount minus the debit amount in a new column. A deposit of 2,400.00 has a credit and an empty debit, so it stays positive. A payment of 86.42 has a debit and an empty credit, so it becomes negative. Then copy the new column and paste it as values, so the file holds numbers and not formulas.
Signs, symbols and thousands separators
- Use a plain minus sign. Parentheses, a trailing minus or the letters DR and CR are not safe.
- Leave out currency symbols such as $.
- Leave out thousands separators. Write 2400.00, not 2,400.00, because the comma looks like a column break.
- Use a full stop as the decimal mark unless your organisation says otherwise.
Check the sign by adding up
Total the Amount column. Add it to the opening balance, and the answer should be the closing balance on the statement. If it is off by exactly twice a payment, that payment was entered with the wrong sign. A figure of 8,650.00 against a printed 8,410.00 is a 240.00 gap, which is a different problem: one 240.00 row is missing. These figures are illustrations.
Mapping CSV columns on Xero's import screen
After you pick the file, Xero asks you to match each column in your file to a Xero field. The screen lists your file's columns with sample values, and you choose a Xero field for each. Leave the columns you do not need unmatched.
What to do on the screen
- Upload the file and wait for the preview.
- Match your Date column to the date field, and check that the sample dates read the way you meant.
- Match Amount to the amount field.
- Match Payee, Description and Reference to their fields.
- Look at the first and last rows of the preview before continuing.
If your header names match Xero's wording, the screen may pre-select them. Do not rely on that without looking. A column wrongly matched to Description when it should be Reference still imports, and the mistake appears only later.
Reuse the same layout every month
Keep one file layout for each bank account and use it every month. A stable layout means you map the columns the same way each time and notice quickly when something changes. If your bank adds a column, delete it from your CSV rather than changing the order of the others.
When the preview looks wrong
Stop at the preview. Cancel the import and fix the file. Cancelling costs nothing, while saving a bad import means finding and removing its lines. For the full route through Xero's screens, see importing a bank statement into Xero step by step.
Commas inside payee names that break a Xero CSV
A comma is the character that separates columns in a CSV, so a comma inside a payee name looks like the start of a new column. The row then has too many values and its fields shift. The fix is to wrap any value that contains a comma in double quotation marks.
How the quoting works
Compare two rows. In the broken one, the name is Hollis & Grant, LLC with no quotes, which produces six values. In the fixed one, the name is in quotes:
03/12/2026,2400.00,"Hollis & Grant, LLC",Client payment,INV 1045
Now there are five values again. Most spreadsheet programs add quotes for you when you save as CSV from a cell that holds a comma. Hand-built files and text-editor changes are where the quotes go missing.
Other characters that cause trouble
- Double quotes inside a value. Double them: a name with a quote becomes two quotes in a row, inside quotes.
- Line breaks in a cell. A wrapped description pasted from a PDF can hold a hidden line break, which starts a new row. Remove them.
- Leading zeros in references. A reference such as 00417 may lose its zeros when a spreadsheet treats it as a number. Format the column as text.
Spotting the problem fast
Open the file in a plain text editor, not a spreadsheet. Count the commas in a few rows. Every row should have four commas for five columns, apart from quoted values. A row that has more is the one that split. Doing this takes a minute and beats hunting through a preview.
If you only have a PDF or a QFX
A CSV is often the second step, not the first. If the statement is a PDF, SimpleBankConvert is being built to turn it into a Xero-ready CSV, and the get started page lets you join the list for access. If the bank gave you a QFX download, QFX fields worth keeping as CSV columns shows what to carry over. Some programs want OFX and not CSV, which is the topic of PDF to OFX and of whether to convert CSV to OFX at all. QuickBooks users have a parallel guide in importing CSV transactions into QuickBooks.