[FIXED] Xero import Bank Statement CSV errors

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 number
• 31/12/2025 - this is future date
• 01/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 perfectly

Alternatively, 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:

1. Navigate to the Accounting menu and select Bank Accounts.
2. Find and click Manage Account next to the bank account you want to import data into, then choose Import a Statement.
3. 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.
4. If prompted, assign the columns in your CSV to the matching bank statement fields in Xero, then click Next.
5. If prompted, select the date format used in your bank statement, then click Next.
6. Review the transactions. Uncheck any transactions you don’t want to import.
7. 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:

1. Check dates
2. Remove blanks
3. Check your columns and formats
4. 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.