Import a precoded CSV bank statement file into Xero
You can precode and import a CSV bank statement file into Xero. Xero then creates bank transactions to match the precoded bank statement lines, and reconciles these automatically.
Only import precoded bank statement lines for transactions not entered into Xero.
You can’t automatically apply bank statement lines to invoices or bills by importing. You can still import bank statement lines for these payments, but you’ll need to reconcile them manually.
Download a CSV file from your bank or create one using our template
If you don't have a CSV file from your bank, you can create one from scratch using our template.
Coded bank statement template (CSV, 1KB)
If the file contains more that 1,000 bank transactions, you will need to split up the file.
Prepare data in the file
Include necessary columns
|Date||The date column must be in the format DD/MM/YYYY or MM/DD/YYYY.|
The amount column must be a single column. Combine income and expense columns into one column, if your file contains 2 amount columns. Positive numbers are income, negative numbers are expenses.
The amount column must not contain currency symbols, or commas instead of decimal places.
Data in the account code column must contain codes already in your chart of accounts in Xero. (These will be Xero default code numbers, or ones you've added.)
Add accounts to your chart of accounts if your CSV contains accounts not already in Xero.
Text in the tax type column must exactly match one of the tax rate display names in Xero.
Add your own tax rates if your CSV contains rates not already in Xero.
If needed. Text in the tracking column must exactly match one of the category options display names in Xero.
Delete the following columns and rows, and other data
- Columns that contain no data, and sit between other columns of data
- Rows that contain no data, and sit between other rows of data
- Columns containing opening and closing balances
- Your bank account number
Save your file
Save your file with .csv as the file extension. For example, you might name your file Jan17bankstatement.csv.
Import your file into Xero
- In the Accounts menu, select Bank Accounts.
- Find the bank account you want to import your file into. Click Manage Account, then click Import a Statement.
- Click Browse and select your saved CSV file.
Click Import.You may be prompted to set up CSV file import rules.
- Click Save.
Set up CSV file import rules
Assign CSV columns to Xero fields
The first time you import a CSV bank statement, you will need to assign each column heading in your file to a field in Xero. Xero applies this assignment to the entire statement, and CSV bank statements you import from now on.
Xero will prompt you to assign CSV files imported into this bank account again, if the files contain extra columns or different column headings.
If your file doesn't include column headings, assign each column number to a field.
Choose the date format
If your file contains a date that could be either DD/MM/YY, MM/DD/YY or YY/MM/DD Xero prompts you to confirm the format. Xero applies the format you choose to the entire statement, and CSV bank statements you import from now on.
Use checkbox to indicate if file has column headings
Select or clear the Don't import the first line because they are column headings checkbox to indicate if the file has column headings.