Xero – How to Import Bank Statement Transactions

After you have set up a bank account in Xero you may need to import some transactions or an entire bank statement. Xero has an Import Bank Transactions tool that makes this easy to do.

In this tutorial, we will import a few bank transactions into a new bank account in the demo company.

To get started, go to Accounting → Bank accounts from the main menu and find the bank account you need.

To start the process of importing bank transactions, click on the Import a bank statement link or click on Manage Account → Import a Statement.

In the next screen, you will see basic instructions on how to download files from your bank’s website. The instructions here are not comprehensive as each bank’s website will be different. If you have trouble downloading the correct files, contact your bank for further instructions.

Note: you cannot upload more than 1,000 (one thousand) bank statement lines at a time. If you wish to upload more than 1,000 lines, you need to create more than one file and upload them one at a time.

As you can see from Xero’s instructions, there are three different types of files that can be uploaded into your bank account in Xero:

1. OFX: an Open Financial Exchange file is a text file containing financial data.
2. QIF: a Quicken Interchange Format file is a text file containing financial data.
3. CSV: a Comma Separated Value file that uses commas to separate values.

How to Upload a CSV File

In this tutorial, we are going to upload a CSV file. As the bank account we have created is fake and we cannot download a file from a bank’s website, we must create a CSV file and add some transactions to it. To do this, click on the Download our CSV template link.

Once you have downloaded the file, open it in Microsoft Excel or Google Sheets. Now we can populate a few lines of the file with some data that will become our fake bank transactions. Then, populate the lines with your data.

When you save the file, you may be asked by Excel or Sheets whether you want to keep the file in the CSV format. Be sure to say yes, otherwise your file will be saved as a spreadsheet and won’t be able to be uploaded into Xero.

When you have finished preparing and saving your CSV file, return to Xero and select the file for upload via the Browse button. Click on Import once you are ready to upload the transactions.

If this is the first time you have imported a bank statement, you will see a screen that will ask you whether the fields in your file are mapped to the right places in Xero. This can be a confusing screen at first but is easy to comprehend once you know what to do.

For our upload, we can see that the fields in our CSV file are going to be assigned to fields in Xero that make sense:

• The * Date field of our bank statement is being mapped to the Transaction Date field in Xero.
• The * Amount field of our bank statement is being mapped to the Transaction Amount field in Xero.
• The Payee field of our bank statement is being mapped to the Payee field in Xero.
• The Description field of our bank statement is being mapped to the Description field in Xero.

It is also worth noting that the first row of our CSV file was made up of column headings such as * Amount rather than bank transaction data. This is why the checkbox is ticked for the Don’t import the first line because they are column headings option. If you import a file that does not have a row of headings at the top, you may need to untick this box.

As we are happy with our mapping, we will press Save to continue. After the file imports, a pop-up box will appear with the results.

Click OK to continue. You should now be taken to a screen that shows the transactions you uploaded into the bank account.

That’s all you need to do to upload a bank statement in Xero. From here, you can go ahead and reconcile each transaction to the general ledger.

If you would like to learn more about Xero, check out our massive Xero Australia Course Bundle and our YouTube channel.