Xero CSV template: columns, signs and date formats
The fields a Xero bank statement CSV needs, how to sign amounts and write dates, the 100,000-row limit, and how to import the file into Xero.
On this page
A Xero bank statement CSV needs two fields, Date and Amount, with income as positive amounts and expenses as negative amounts in one column (Xero Central (opens in a new tab)). Payee, Description, Reference and Cheque number are optional. You can start from StatementLoom's Xero CSV template, or turn a PDF bank statement into a Xero CSV with the free bank statement converter.
The fields Xero's CSV import uses
Xero's CSV article lists these fields (Xero Central (opens in a new tab)). Mandatory fields are marked with an asterisk in Xero's import template.
Date: mandatory.Amount: mandatory.Payee: recommended. If the payee is an existing contact in Xero, make the name match the contact name exactly, to avoid creating duplicates.Description: text you want as the transaction's description.Reference: text you want as the transaction's reference.Cheque number: where it applies.
Xero puts it this way: "You only need the Date and Amount fields to create bank statement lines, but reconciling your transactions is easier if you include more detail."
Before importing, Xero says to delete columns containing opening and closing balances (Xero calculates the bank balance from the transactions), the bank account number if it shows in the file, and empty rows.
Convert a PDF statement for free
Upload a text PDF bank or credit-card statement and see whether its transactions add up to its own balances before you download Excel or CSV. Up to 10 pages a day, no account needed.
Amount signs and decimals
Income and expenses go in the same Amount column (Xero Central (opens in a new tab)). Show income as positive and expenses as negative, with a minus sign or brackets: -30.00 or (30.00). Don't use commas as the decimal separator.
Credit card accounts use the same import. For a card, payments are positive and purchases are negative.
If your bank's CSV uses comma decimals or the opposite sign convention, the bank CSV cleaner can reshape it into the Xero layout. It detects comma decimals and can flip signs.
Date formats
Xero's article lists three main date formats (Xero Central (opens in a new tab)):
DD/MM/YYYY, for example15/01/2023MM/DD/YYYY, for example01/15/2023YYYY/MM/DD, for example2023/01/15
It also accepts DD/MM/YY, MM/DD/YY, DD/MMM/YY (15/JAN/23), DD/MMM/YYYY, dates with spaces, hyphens or periods (15 01 2023, 15-01-2023, 15.01.2023) and DDMMYYYY without separators.
When a date could be read more than one way, Xero asks you to confirm the format. It then applies that format to the whole statement and to all future CSV bank statements.
Xero's template and StatementLoom's template
Xero's own import file is regional (Xero Central (opens in a new tab)). The region sets the file name and column headings, and it follows the language of your web browser. If Xero can't tell, the file defaults to New Zealand English.
The template on this site is StatementLoom's, not Xero's. It uses the same field names: Date, Amount, Payee, Description, Reference and Cheque number, with two example rows. Its headings may differ from the regional file Xero gives you, and the dates in the example rows are day-first.
How to import the CSV into Xero
Xero's steps (Xero Central (opens in a new tab)):
- From the Accounting menu, select Bank accounts.
- For the bank account you want to import into, click the menu icon, then select Import a Statement.
- In File to upload, drag and drop the file or click Select file, then click Next.
- If prompted, assign the columns in your file to the matching bank statement fields, then click Next. You do this the first time you import a CSV; Xero keeps the assignment for later imports unless the new file has extra columns or different headers. The Transaction Date and Transaction Amount fields must be assigned.
- If prompted, select the date format used in the file, then click Next.
- Review the transactions, clear the checkbox next to any you don't want to import, then click Complete import.
Xero treats transactions that match ones already in Xero as duplicates and does not import them.
You can't import a CSV file with more than 100,000 rows. Xero suggests deleting empty rows, or using another file type such as OFX or QIF. Where your bank offers OFX, Xero recommends it over QIF, QFX, CSV or QuickBooks files (Xero Central (opens in a new tab)).
If an import goes wrong, see Xero bank statement import problems.
Making a Xero CSV from a PDF statement
Xero imports PDF bank statements only in organisations in the United States and Canada (Xero Central (opens in a new tab)). Elsewhere, a PDF statement has to become a file Xero imports first.
StatementLoom reads text PDF bank and credit card statements, not scans. It checks that the opening balance plus every transaction equals the closing balance before any export. Its Xero CSV has the columns Date, Amount, Payee, Description and Reference, with money in positive and money out negative. Dates are MM/DD/YYYY for USD statements and DD/MM/YYYY for every other currency.
The free converter downloads Xero CSV without an account, up to 10 pages a day. For a plain spreadsheet instead, see bank statement to CSV.
Sources
Tools for this
-
Bank CSV cleaner
Reshape a bank CSV into the columns QuickBooks Online or Xero imports, with a preview first.
-
Free bank statement converter
Turn a text PDF statement into Excel or CSV after a balance check. No account needed.
Frequently asked questions
Which columns does a Xero bank statement CSV need?
Only Date and Amount are needed to create bank statement lines. Payee, Description, Reference and Cheque number are optional and make reconciling easier.
Should expenses be negative in a Xero CSV?
Yes. Income goes in as positive amounts and expenses as negative amounts, in the same column, written with a minus sign or in brackets.
How many rows can a Xero CSV import have?
Xero does not import a CSV file with more than 100,000 rows. Delete empty rows or use another file type such as OFX or QIF.
Is the StatementLoom template the official Xero template?
No. It is StatementLoom's own file with the same field names. Xero's own import file is regional: its file name and column headings follow the language set in your web browser.
Convert a PDF statement for free
Upload a text PDF bank or credit-card statement and see whether its transactions add up to its own balances before you download Excel or CSV. Up to 10 pages a day, no account needed.