Xero bank statement import failed? Fixes for CSV and OFX errors

When a bank statement import goes wrong in Xero, it usually shows up on the review screen: some lines are flagged with warnings, others with errors that stop them importing. Sometimes the import works but leaves you with duplicates. Here's what causes each problem and how to fix it.

First: use OFX if your bank offers it

Xero recommends OFX for manual imports, because an OFX file can go into Xero without any changes to what your bank exported. A CSV usually needs cleaning up first. If your bank's download page offers OFX, try that before anything else.

If the OFX import also fails, open the file in the free OFX and QFX viewer. It reads the file in your browser, flags common problems such as a cut-off download, missing transaction IDs or a bare "&" in a payee name, and can write a repaired copy.

Fixing a CSV that Xero rejects

Most CSV problems come from the file having more in it than Xero wants. Xero's help says to:

• Delete any columns with opening and closing balances. Xero calculates the balance itself from the transactions.
• Delete the bank account number if it appears in the file.
• Delete empty rows.

Then check the two fields Xero needs, Date and Amount:

• Dates must use one of the formats Xero accepts: DD/MM/YYYY, MM/DD/YYYY or YYYY/MM/DD. Use the same one throughout the file, and choose it when Xero asks.
• Amounts go in a single column, money in as positive and money out as negative, written either -30.00 or (30.00). Don't use a comma for decimals. If your bank puts money in and money out in separate columns, they need combining into one.

Payee, Description and Reference are optional, but they make reconciling much easier, so keep them if your bank provides them. A CSV can't have more than 100,000 rows.

The free bank CSV converter does all of this in one step. Choose the Xero option and it writes a file in the layout of Xero's own template, with one signed amount column and no balance columns. It can also write OFX instead.

Some lines imported, some didn't

Xero's advice when only part of a file goes in is to continue the import, review what came through, and then import the missing transactions from a different file type, such as OFX instead of CSV, or a corrected CSV covering only the dates that failed.

Duplicates after the import

Duplicate statement lines appear when a manual import overlaps with transactions Xero already has, from a bank feed or an earlier import of the same dates. Before importing, check the date of the most recent transaction already in that bank account and trim the file so it starts after it.

To find duplicates that have already crept in, run Xero's Duplicate Statement Lines report. With OFX files, keeping each transaction's ID (FITID) the same between downloads also helps software recognise transactions it has already seen. The OFX viewer's repaired file keeps original IDs for that reason.

Wrong account, or the import finds nothing

Xero imports one bank account per file. If your bank's download combines several accounts, download each account separately. For an OFX file, the viewer shows how many statements, and which accounts, the file contains.

If you only have PDF statements

In the US and Canada, Xero can read PDF statements from the banks it supports. Elsewhere, or for other banks, the statement converter reads the PDF in your browser, checks every transaction against the statement's closing balance, and exports OFX or CSV ready for Xero. There's more on the Xero page.