Free Xero CSV import template for bank statements (filled and blank)
Need a CSV template to import a bank statement into Xero? Xero has one, but you can only download it from inside an organisation, and it arrives empty. Here's the same layout as a free CSV file with no sign-up, already filled with a fictional month of business checking so you can test an import and know exactly what should come in, plus a blank copy. Below, the template line by line, the date formats Xero accepts, the rules that make an import fail, and how to check the result.
Short answer: download the filled Xero CSV template (*Date,*Amount,Payee,Description,Reference) or the blank one. Put income and expenses in the one Amount column, income positive and expenses negative, use one of Xero's date formats (the template uses YYYY/MM/DD), and leave out balances, account numbers and empty rows. The filled template holds 31 fictional transactions: 11 received totalling 29,597.43, 20 spent totalling 28,575.27, net +1,022.16. Import it from Accounting → Bank accounts → ⋮ → Import a Statement, into a demo or test organisation first.
See a messy bank export turned into this template
Open a fictional bank CSV (a title block on top, separate Debit and Credit columns, a running balance and a totals line) in Utilly's CSV to Xero converter. You see every transaction before you download, and the Xero CSV it writes is the filled template above. Free for the sample, and free for your own files up to 100 rows.
- Does Xero have a CSV import template?
- The templates
- The template, line by line
- Date formats Xero accepts
- Rules that make an import fail
- Filling the template in Excel or Google Sheets
- Importing the file into Xero
- Credit card accounts
- Checking the import against the known totals
- QuickBooks and other software
- From a PDF statement or a messy bank export
- FAQ
1. Does Xero have a CSV import template?
Yes, inside Xero. Xero Central's article on importing a CSV bank statement says that if you can't download a CSV from your bank, you can create your own import file from Xero's template: in the Accounting menu select Bank accounts, click the menu icon (⋮) for the account, select Import a Statement, then click the link to download the Xero CSV template file. That file is empty apart from the headings, and it's regional: Xero picks the file name and column headings from your browser's language and defaults to New Zealand English, so they can differ slightly from the ones here.
The templates on this page use the same statement fields without needing a Xero login, and the filled one comes with a month of transactions whose totals you know, so you can see what a correct import looks like before you put real data through it. They're for bank and credit card statement lines. Bills, invoices and contacts use different import files; for bills, see the Xero bill import CSV template.
2. The templates
The filled template is written by the same code that makes Utilly's Xero CSV for customers, run on the fictional statement explained on sample bank statement PDF, so it's exactly what a real conversion produces.
| File | What's in it | Use it when | Known totals |
|---|---|---|---|
| Filled template | Header row + 31 fictional bank transactions | Testing an import, or comparing a bank export with a file Xero accepts | 31 lines, +29,597.43 / −28,575.27, net +1,022.16 |
| Blank template | The header row only | Typing or pasting your own transactions | None |
| Credit card version | Header row + 21 fictional card transactions | Testing a credit card account import | 21 lines, net −397.76 |
The filled bank file is 2.3 KB of plain ASCII text with Windows line endings. The same month also comes as .QBO, .IIF, QuickBooks Online CSV and more on sample bank statement files.
3. The template, line by line
The start and the end of the filled file as plain text:
*Date,*Amount,Payee,Description,Reference
2026/09/01,-6420.18,ACH DEBIT GUSTO PAYROLL 0912,ACH DEBIT GUSTO PAYROLL 0912,
2026/09/01,3184.27,STRIPE TRANSFER ST-K2M8Q4,STRIPE TRANSFER ST-K2M8Q4,
2026/09/02,-1850.00,CHECK 2041,CHECK 2041,
2026/09/02,-412.66,ACH DEBIT AUSTIN ENERGY UTIL,ACH DEBIT AUSTIN ENERGY UTIL,
…
2026/09/30,-15.00,MONTHLY SERVICE FEE,MONTHLY SERVICE FEE,
2026/09/30,3.21,INTEREST PAYMENT,INTEREST PAYMENT,| Column | What goes in it |
|---|---|
| *Date | Required (the asterisk marks Xero's mandatory fields). One date format for the whole file; here YYYY/MM/DD. |
| *Amount | Required. Income and expenses in this one column: income positive, expenses negative, with a minus sign (−30.00) or brackets ((30.00)). Payroll is −6420.18; the Stripe deposit is 3184.27. |
| Payee | Recommended. If the payee is an existing contact, Xero says the name must match the contact exactly, or you'll end up with duplicate contacts. The template repeats the bank's text (up to 40 characters); replace it with your contact names where you can. |
| Description | Any detail you want on the line. Xero shows it as Particulars on the account's Bank statements tab. |
| Reference | Optional; blank in the template. Anything you enter shows as the line's reference. |
Xero's own template also has a Cheque number column, and Xero lets you add two more: the bank's Analysis code and Transaction Type. All three are optional; add a column with a unique heading if you need one and assign it when you import. The header row must be in row 1, with nothing above it.
4. Date formats Xero accepts
Xero Central lists three main formats and several others. Every row has to use the same one:
| Format | Example (15 January 2023) | Note |
|---|---|---|
| YYYY/MM/DD | 2023/01/15 | Used by the template. A four-digit year first can't be read as a day or a month |
| DD/MM/YYYY | 15/01/2023 | Day first (UK, Australia, New Zealand banks) |
| MM/DD/YYYY | 01/15/2023 | Month first (US banks) |
| Others | 15/01/23, 01/15/23, 15/JAN/23, 15/JAN/2023, 15 01 2023, 15-01-2023, 15.01.2023, 15012023 | Also listed by Xero |
A year-first format with hyphens (2023-01-15) isn't in Xero's list, so the template doesn't use it. When a file's dates could be read more than one way, Xero asks you to confirm the format, and it applies that choice to the whole statement and all future CSV bank statements. That's how a month-first file ends up with days and months swapped after a day-first one was imported earlier: 03/09 becomes 3 September instead of 9 March, and only dates up to the 12th are affected. A year-first file avoids the problem.
5. Rules that make an import fail
From Xero Central's CSV statement article (link at the end):
| Rule | What goes wrong | Fix |
|---|---|---|
| One Amount column | Separate Debit and Credit (or Withdrawals and Deposits) columns from the bank export | Combine them into one signed amount (formula below) |
| No balances | Opening, closing or running balance columns. Xero calculates the bank balance from the transactions | Delete the column |
| No account number | The bank account number in the file | Delete it |
| No empty rows | Blank lines, including ones at the end of the file | Delete them |
| Decimal points, not commas | 1234,56 style amounts | Use a point for decimals: 1234.56 |
| One date format | Mixed formats, text around the date | Set the whole column to one format from the list above |
| Size | More than 100,000 rows | Split the file, or import OFX or QIF instead |
| Saved as .csv | An .xlsx or .numbers file | Save or export as CSV |
Getting "The file you uploaded does not contain valid statement data", or an import that went in with the wrong dates or signs? Xero bank statement import errors covers each message and symptom.
6. Filling the template in Excel or Google Sheets
- Open the template: the blank one, or the filled one with rows 2 to 32 (the fictional transactions) deleted. Keep row 1.
- Paste your transactions under the header: date in A, the signed amount in B, then payee, description and reference.
- Money in and money out in two columns? With money in in C and money out in D of your bank export,
=C2-D2in a new column gives the signed amount; copy it down, paste it as values into the Amount column, then delete the two originals. Blank cells count as zero, so it works on rows that only have one amount. - Fix the dates. Set the date column to one format; in Excel, Format Cells → Custom →
yyyy/mm/dd. - Delete balance columns, totals lines and blank rows. Lines of 0.00 have nothing to reconcile, so leave those out too.
- Save as CSV. Excel: File → Save As → CSV (Comma delimited). Google Sheets: File → Download → Comma-separated values.
Excel can quietly change a CSV when it opens one, turning dates around on a computer set to a different region or long reference numbers into 1.23E+11. Opening a bank CSV in Excel without breaking it covers the safe way to open one.
7. Importing the file into Xero
You need the administrator, or standard + bank accounts, user role. Xero's steps:
- In the Accounting menu, select Bank accounts.
- For the 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 to the matching statement fields. Transaction Date and Transaction Amount must be assigned. Xero remembers the assignment for future CSV imports unless the file has extra columns or different headers.
- If prompted, select the date format (YYYY/MM/DD for the template), then click Next.
- Review the transactions: ready to import, with warnings, or with errors. Clear the checkbox next to any you don't want.
- Click Complete import, then reconcile the statement lines.
Transactions that match statement lines already in Xero are treated as duplicates and aren't imported. If your bank offers OFX, QFX or QuickBooks (.QBO) downloads Xero recommends using those instead of a CSV. Every format, including PDF import in the US and Canada, is compared in how to import a bank statement into Xero.
8. Credit card accounts
The same template and import steps work for a credit card account. In the Amount column, Xero wants payments to the card as positive amounts and purchases as negative. The sample credit card Xero CSV has 21 lines: 19 purchases totalling 1,939.94 and 2 credits (a 1,500.00 payment and a 42.18 return) totalling 1,542.18, a net of −397.76. If your card's own export shows purchases as positive numbers, that's the card company's side of the account; flip the signs before importing. The credit card statement Excel template explains why.
9. Checking the import against the known totals
The point of a filled-in template is that you know what should come in. Import it into a test organisation, then compare with this:
| Check | Should be | If it's wrong |
|---|---|---|
| Statement lines | 31 | Fewer means lines were unticked at the review step or had errors |
| Received | 29,597.43 (11) | A different total means lines were skipped or an amount was misread |
| Spent | 28,575.27 (20) | Same as above |
| Net change | +1,022.16 | −1,022.16 means every sign is flipped: income was entered as negative |
| Dates | 1–30 Sep 2026 | Lines dated in other months mean day and month were swapped: the wrong date format was chosen or remembered |
An import brings in statement lines, not an opening balance. The statement these rows come from runs from 12,418.36 to 13,440.52, and 12,418.36 + 1,022.16 = 13,440.52. If the account in Xero doesn't land on the statement's closing balance after reconciling, finding bank reconciliation discrepancies walks through the usual causes, and deleting an imported statement undoes a bad import.
The transactions are fictional (Example Community Bank and Sample Customer LLC don't exist; the statement they come from says SAMPLE STATEMENT, FICTIONAL DATA FOR DEMONSTRATION ONLY). Import them into Xero's Demo Company, which Xero resets every 28 days, or another test organisation, never into books you file from, and replace every row before using the template for real.
Setting up a new Xero organisation? Import the accounts before the bank lines: our chart of accounts template includes a Xero chart of accounts CSV.
10. QuickBooks and other software
- FreeAgent takes no header row: dd/mm/yyyy date, one signed amount, description, in that order: FreeAgent bank statement CSV format and templates.
- Odoo maps Date, Label, Partner, Amount with YYYY-MM-DD dates: Odoo bank statement import template.
- NetSuite wants Date (MM/DD/YYYY), Payor/Payee Name, Transaction ID, Transaction Type, Amount and four optional columns: NetSuite bank statement import template.
- QuickBooks Online takes a different CSV layout (Date, Description, Amount with MM/DD/YYYY dates, or a 4-column version); both are on QuickBooks Online CSV import template.
- QuickBooks Desktop takes a .QBO Web Connect file or an .IIF; a ready-made one is on sample IIF file.
- Wave takes Date, Description, Amount, with card purchases positive; see bank statement to Wave.
11. From a PDF statement or a messy bank export
Have a bank CSV or Excel file with extra columns, a title block on top or Debit/Credit columns? CSV to Xero reads it, shows a preview, combines Debit and Credit into one signed Amount, leaves out balances, totals and 0.00 lines, and writes this template; it's free up to 100 rows. Only have PDF statements, or months your bank no longer lets you download? Upload the PDF to bank statement to Xero: the preview is free with no sign-up and checks that the rows add up to the statement's ending balance before you download the Xero CSV; downloading uses 1 credit ($9 for 5, no subscription) or Pro at $19 a month. Xero in the US and Canada can also import some PDF statements itself.
Get your own statement into this template
PDF statements, downloaded or scanned, get a free preview with the balance check, no sign-up. CSV and Excel exports are free up to 100 rows. Files are processed in memory and not stored.
Sources: Xero Central, Import a bank statement in CSV format, central.xero.com; Xero Central, Teach Xero using free trial organisations (Demo Company), central.xero.com. Checked November 1, 2026. Xero updates these pages; if a screen differs, follow the current article.
FAQ
Does Xero have a CSV template for bank statements?
Yes, inside Xero: go to Accounting, Bank accounts, click the account's menu icon, select Import a Statement and click the link to download the CSV template. It's empty and regional, so its headings follow your browser's language. The templates on this page use the same fields, and the filled one has a test month with known totals.
What columns does a Xero bank statement CSV need?
Only Date and Amount are required, marked with an asterisk in the template. Payee, Description and Reference are optional but make reconciling easier, and Xero's own template also has Cheque number. You can add the bank's Analysis code and Transaction Type as extra columns.
How should amounts be shown in a Xero CSV?
Income and expenses go in one Amount column, income positive and expenses negative, with a minus sign or brackets, for example -30.00 or (30.00). Use a point for decimals, not a comma. Separate Debit and Credit columns from a bank export need combining into one signed amount first.
What date format does Xero use for CSV imports?
Xero lists three main formats, DD/MM/YYYY, MM/DD/YYYY and YYYY/MM/DD, plus several others such as 15/JAN/2023 and 15-01-2023. Use one format for the whole file. Xero remembers the format you confirm for future CSV statements, so a year-first date such as 2026/09/01 is the safest because it can't be read as a day or a month.
How do I import a credit card statement into Xero from a CSV?
Use the same template and Import a Statement on the credit card account. Xero wants payments to the card positive and purchases negative. Many card exports show purchases as positive, so flip the signs before importing.
How do I know the import worked?
Import the filled template into a demo or test organisation. It should bring in 31 statement lines: 11 received totalling 29,597.43 and 20 spent totalling 28,575.27, a net change of +1,022.16, all dated September 2026. A net of -1,022.16 means the signs were flipped; dates in other months mean the day and month were swapped.
Related guides
- How to import a bank statement into Xero (OFX, CSV or PDF)
- Xero bank statement import errors (and how to fix them)
- Bank statement to Xero converters compared (2026): Xero's PDF import, Hubdoc and 4 converters
- Free sample bank statement files: PDF, CSV, QBO, IIF, QIF and Xero CSV
- Free QuickBooks Online CSV import template for bank transactions (3- and 4-column)
- Free sample bank statement CSV (fictional, with known totals)
Related tools
- Bank reconciliation checker: compare a bank statement to your books and list missing, duplicate and mismatched transactions.
- Receipt to CSV: turn PDF or photo receipts and invoices into CSV, Excel, or QuickBooks and Xero bill imports.
