Xero Training - How to import CSV file bank statement in Xero (2019)

Video

Sponsor

This video is sponsored by our online course, Master User Academy. Master User Academy helps non-accountants to do bookkeeping by using Cloud Accounting System like a Pro in simple English. Get real-time financial data of your business to grow your business faster. Join Master User Academy Level 1 now for FREE! ➡️ https://www.nutsaccounting.com/store

The first step of bank reconciliation in Xero Accounting is to get your bank statement into Xero. There is 2 ways to do so, which is bank feed and manual import.

If you have set up your bank feed, that's great! All your bank statement transactions will automatically appear in your Xero Accounting. However, if you can't set up your bank feed, your alternative will be manual import. Here is a tutorial to guide you step-by-step how to import CSV file bank statement in Xero.

Files supported by Xero manual import

Before uploading your files into Xero bank statement, make sure that you have the correct file formats. Xero currently supports the following file formats:

• OFX
• QIF
• CSV

CSV is the most common upload file used right now. In today's blog post, you will learn how to prepare and upload a CSV file into Xero Accounting.

What is CSV file?

CSV is short for Comma-Separated Values. 

It is a simple file format to store data. All columns are separated by a comma symbol (,).

Here is a sample CSV file.

If you open your CSV file in Notepad to see its data, it will look intimidating to you (I know, it looks like programming codes, don't you think?). Don't worry, we are not going to type our data in a Notepad. 

You can use any spreadsheet software to create your CSV file. You can use Microsoft Excel or Google Sheets to prepare your data. Once you're done, save your document as CSV file format.

Step 1: Open your bank statement in Ms Excel

2 ways to prepare your bank statement into CSV file:

1. Download CSV file from your Online Banking
2. Create your import file manually

Method 1: Download CSV file from Online Banking

Some online banking allows you to download your bank statement to a CSV file. Login to your online banking website and look for a button or a link to download it. If you can't find it, you may check out their help file, user guide or ask their customer service.

Method 2: Create your import file manually

Don't worry if you can't download your CSV file from your online banking. You can also create your CSV file manually by following the steps below:

1. Login to Xero Accounting. Go to Bank account Name > Manage Account > Import a statement.

2. Click on the link Download our CSV template. Open the downloaded file in Microsoft Excel (or your choice of spreadsheet software).

This is how it looks like when its opened in Microsoft Excel. 

Step 2: Insert Heading (Row 1)

Headings of your CSV file must fulfill the following criteria:

• Heading must be row number 1
• All column names must be unique.
  • If you have 2 columns of the same column name, add additional identifier to make your column name unique. Example: Amount(USD), Amount(Euro)

Step 3: Columns

If you use Xero's CSV template file, the mandatory columns are marked with an asterisks (*). The mandatory columns and its formats are:

• Date
  • DD/MM/YYYY (example: 31/12/2019)
  • MM/DD/YYYY (example: 12/31/2019)

• Amount (1 column only)
  • Positive Amount = Money received (example: 100)
  • Negative Amount = Money spent (example: -80)
  • No currency symbols (example: $)
  • No thousand separators (example: 1,000.00 )

Xero will only import your data if these 2 columns contains data.

All other columns in your CSV file are Optional Columns. You may add in additional information to your CSV file for your reference when you do your bank reconciliation in Xero. 

Step 4: Delete Unnecessary Data

If you download your CSV file from online banking, you must check your CSV file for any unnecessary data. Some of the example of the unnecessary data are:

• Bank account details
• Report dates
• Empty rows
• Opening & closing balances
• Unnecessary columns

Once you have cleared all the unnecessary rows and columns from your CSV file, it should look similar to this:

Your CSV file is now ready for import! Let's move on to your Xero account.

Step 5: Open Xero Import Bank Transactions page

Login to your Xero account. Click Accounting > Bank Accounts at the top menu bar.

It will now load to your Bank Account page and each section of the page is your individual bank account in Xero. Look for the bank account that you want to import to. Remember to match the bank name and bank account number before you proceed. Once you found your bank account, click on the button Manage Account > Import a Statement.

Step 6: Upload your CSV file

It will now load to Import Bank Transactions page. Click on the button Browse and a pop up box will appear.

 Locate your CSV file and click Open. This pop up box will now disappear.

Your CSV file name will now appear next to the Browse button. In my example below, my CSV file name is Bank Statement CSV file.csv. If the file name is correct, click Import button.

Step 7: Assign columns (only on first import)

This step is only applicable if:

• You are importing your CSV statement for the first time

• Your CSV file is different from previous imports:

  • Different column headers

  • Additional columns

We need to check if all our rows of data has been imported successfully. To check this, find out the total number of rows of data that you have in your CSV file. Follow the simple formula below to find out your number:

Total rows - Header rows = Rows of Data to Import

In my example below, the last row number of my data is 23 and I have 1 header row. Hence my total rows of data to import is 23 - 1 = 22.

Once you have clicked import in the previous page, it will load to this page which shows you a preview of your import. Check that the number of 22 in Statement line 1 of 22 is same as your total number of rows of data to import. If you have 50 rows of data to import, yours will show Statement line 1 of 50. 

If you need to assign your columns, the Assign to... fields will appear in this page. You need to tell Xero which column headers in your file is to assign to which field in Xero. In the example below, I am assigning the Date column in my CSV file to the Transaction Date in Xero. Check the checkbox Don't import the first line because they are column headings. If you do not have a header row in your CSV file, you may uncheck this. On the right part of this page is the preview of your data and how it will look like when it has been imported into Xero. Once you have checked everything is correct, click Save button.

A pop up box with the Import Results will appear. Check that the total row of data to import is correct (in my example its 22). Click OK button.

In the Bank Reconcile page, the bank statement that you have newly imported into Xero will now appear on the left part of this page. You have now successfully imported your bank statement CSV file into Xero.

What is the next Xero Accounting tutorial that you would like to learn?

Let me know in any of the social platforms or by email!

As always, Nuts Accounting is here to help Entrepreneurs to master Cloud Accounting System in simple English. 

On this blog, there’s weekly tips & tutorials on Cloud Accounting System & online business curated just for you.

If you like this blog post, share it with your friends.

If you want to learn more on Wave Accounting tutorials, bookkeeping or tips on online business, click on the categories in the side bar.

I’ll see you in the next blog post! 

Disclaimer: This blog post is not sponsored by any of the products mentioned in anyways at all. I created this blog post based on my personal experience and I hope that by sharing my knowledge with you, you can use yours better.

No part of the content of this video or any video published by Nuts Accounting, shall be stored, copied, recreated, republished, or transported.

If you click on the affiliate links and took action (sign up or purchase) I will earn a small commission to support this blog.