[FIXED] Xero import Bank Statement CSV errors
Top 5 tips to fix Xero import bank statement errors.
Oct 4, 2023 | Read time 9 minutes![](/img/blog-img-excel.png)
๐ Table of contents
Introduction
Have you ever tried to manually import bank transactions in a CSV file and get the following errors:
- An error occurred for the following reason: The file you uploaded does not contain valid statement data. Please check the file
- There was a problem importing the file
- The file could not be imported because the first row doesn’t contain the mandatory column headers
This can be quite frustrating when dealing with large CSV files and it is not obvious where the error is.
In this post I will go over ways to fix your Xero Bank statement import errors.
A note on CSVs
CSV, which stands for “Comma-Separated Values,” is a simple and widely-used file format that’s used to store tabular data (like a spreadsheet or database). Here’s a breakdown of the CSV format:
By default, fields are separated by commas, which is where the format gets its name. However, sometimes other delimiters like semicolons are used, especially in regions where the comma is used as a decimal separator.
Each line in a CSV file corresponds to a row in the table. As for columns - values on the same line, separated by commas, correspond to different columns
Consider the following table:
Name Age Occupation John Doe 30 Engineer Jane Doe 28 Doctor The above table would look like this in CSV format:
Name,Age,Occupation John Doe,30,Engineer Jane Doe,28,Doctor
Fix 1: Check your dates
One common mistake is that we have the date format not correct. Some banks have statements that do not contain the “year”.
Xero expects your transaction dates to follow the formats:
- DD/MM/YYYY (eg 15/01/2023)
- MM/DD/YYYY (eg 01/15/2023)
- YYYY/MM/DD (eg 2023/01/15)
So formats that do not adhere to the above will give you a failure message. For example the following dates will not work:
01 Nov 2022
- this contains the month as a text instead of number31/12/2025
- this is future date01/12/20
- this contains the wrong year format - years should have four numbers!
Tip
Xero does provide other date formats:
- DD/MM/YY (eg 15/01/23)
- MM/DD/YY (eg 01/15/23)
- DD/MMM/YY (15/JAN/23)
- DD/MMM/YYYY (15/JAN/2023)
- DD MM YY (using spaces) for example 15 01 2023
- DD-MM-YYYY (using hyphens) for example 15-01-2023
- DD.MM.YYYY (using periods) for example 15.01.2023
- DDMMYYYY (with no spaces between dates) for example 15012023
Fix 2: Make sure your CSV does not have blanks
When you get a “No valid statement data” error when trying to import your CSV into Xero, the most likely problem is that your file contains blank lines or that there are spaces in the columns.
As an example, consider the following CSV transaction file with the “Date” and “Amount” columns:
*Date,*Amount,Payee,Description,Reference,Check Number
21/12/2022,1.00,,,,,
22/12/2022,-7.00,,,,,
26/12/2022,4.45,,,,,
This file will not import correctly because we have a blank third row.
Fix 3: Check your columns and formats
If you receive an error that says along the lines of “There was a problem importing the file”, most likely that your columns or formats is not right.
- Ensure each column has data in the appropriate format. For instance, the ‘Date’ column should strictly have data in a date format, while the ‘Amount’ column should be filled with numbers only.
- The date should follow the format described in: Fix 1 and that there are no spaces
- With the “Amount” column, make sure that you do not use commas to show decimal places. This needs to be a period:
.
. Additionally transactions with$0.00
can not be imported since there is nothing to reconcile. - Consistency key here. Make sure all data in a column is aligned in the same manner. For example, if you’re going for a left alignment, ensure every entry in that column is left aligned.
- Make sure the column headers match the data. For example, the Transaction Date field will not match with the Payee field.
Tip: Check the separators
In one instance of the CSV transactions, I discovered that my decimal separator was set to
","
which made Excel use";"
in my CSV file. After changing the decimal separator in that particular spreadsheet to"."
, everything worked perfectlyAlternatively, we can change the separator in Windows settings. Navigate to settings, then select ‘Additional time, date, and regional settings’, followed by ‘Region’. In ‘Additional settings’, under the ‘Number’ tab, make sure the list separator is set to a comma. Under the ‘Currency’ tab, ensure the decimal symbol is a period. Once I made these changes, everything functioned correctly
Fix 4: Verify that the CSV is not too large
Make sure the CSV file does not contain more than 1000 bank transactions. Now the Xero documentation is all over the place.. in some instances they mention the limit is 100,000 and 1000 in other places.
Probably best to break your CSV files up if its gone over the 1000 transaction mark!
Fix 5: Check your character limits
Please be advised that there are specific character limits for certain fields during the import process. Exceeding these limits will result in an import failure. The established limits are as follows:
- Reference Field: Maximum of 255 characters.
- Cheque Number Field: Maximum of 20 characters.
Import into Xero
After we have cleaned up our CSV, we can then import it to Xero:
- Navigate to the Accounting menu and select Bank Accounts.
- Find and click Manage Account next to the bank account you want to import data into, then choose Import a Statement.
- Under File to upload, either drag and drop your CSV file or click Select file to choose the file from your computer, then click Next.
- If prompted, assign the columns in your CSV to the matching bank statement fields in Xero, then click Next.
- If prompted, select the date format used in your bank statement, then click Next.
- Review the transactions. Uncheck any transactions you donโt want to import.
- Click Complete import to finalize the import process.
Tip: Check Mac
When using the Mac version of Excel, it can insert some random characters. Always a good idea to double check your CSV by opening up in a text editor.
Final thoughts
Overall, importing CSV bank transactions into Xero can give you some random errors. We need to ensure the accuracy and consistency of our data.
Fixing these issue, I would suggest to follow a checklist:
- Check dates
- Remove blanks
- Check your columns and formats
- Keep size low and check column character limits
First, make sure to check that your transaction dates adhere to Xero’s accepted formats. This includes things such as avoiding textual months, future dates, or incorrect year formats.
Secondly, eliminate any blank lines or spaces within your CSV columns. We need to make sure that the column headers are correct too - at least have the “Date”, “Amount” columns.
Thirdly, maintain consistency in your columns and their formats, ensuring that data is left aligned and that decimal places use periods, not commas.
There could be timeout issues when you have large files; while there’s some ambiguity in Xero’s documentation, it’s safest to keep transactions below 1000.
Once your CSV is prepared, you can easily import it into Xero by navigating through the Accounting menu.
Now lastly if you are a Mac user, make sure to inspect your CSV in a text editor, as the Mac version of Excel might introduce unexpected characters.
๐ About the Author
G'day! I am Ken Ito a software engineer based in Australia. I have worked as a software engineer for more than 10 years ranging from roles such as tech lead, data analyst to individual contributor. I have experience in creating code (python, javascript) to extract data from PDFs for ETL and financial statement analysis for accounting purposes.
During those years, I have worked for Government departments, companies like NTT (Nippon Telegraph and Telephone), Bupa and not-for-profit organizations.
![How to convert your Td Bank statement to Excel. A step by step guide!](/img/blog-img-excel.png)
TD Bank Statement pdf to excel converter
Sep 24, 2023How to convert your Td Bank statement to Excel. A step by step guide!
![Tutorial on how to convert your MayBank Bank Statement PDFs to Excel format.](/img/blog-img-excel.png)
[HOW TO] Convert MayBank Statement to Excel
Sep 19, 2023Tutorial on how to convert your MayBank Bank Statement PDFs to Excel format.
![Steps to extract your CitiBank PDF bank statement to Excel](/img/blog-img-excel.png)
[RESOLVED] Export CitiBank Bank statements in Excel
Sep 15, 2023Steps to extract your CitiBank PDF bank statement to Excel