Import errors

Xero bank statement import: fixing common problems

Why a bank statement won't import into Xero, or imports twice: file formats, PDF limits, the 100,000-row CSV cap, date prompts, duplicates and balances.

By Updated Last verified

On this page
  1. Which formats Xero accepts
  2. "PDF bank statements can't be imported into Xero"
  3. CSV file with more than 100,000 rows
  4. Dates import in the wrong order
  5. Transactions missing or duplicated
  6. A whole statement imported twice
  7. Statement balance and balance in Xero differ
  8. Converting a PDF statement for Xero
  9. Sources

When a bank statement won't import into Xero, check the file type first. Xero supports OFX, QFX, QIF, CSV and QuickBooks files and recommends OFX (Xero Central (opens in a new tab)), while PDF import is limited to organisations in the US and Canada (Xero Central (opens in a new tab)). If you only have a PDF statement, convert it for Xero, or try the free bank statement converter for a Xero CSV.

Which formats Xero accepts

Xero's global help lists OFX, QFX, QIF, CSV and QuickBooks file formats (Xero Central (opens in a new tab)). It recommends OFX where possible. Xero says OFX files can be imported without any changes to the export file from your bank.

Xero's US version of the same article adds PDF to the list (Xero Central US (opens in a new tab)).

For OFX, QFX and QuickBooks files, you need a separate file for each bank account (Xero Central (opens in a new tab)). Xero describes them as text file formats. The QBO, QFX and OFX validator checks the structure, dates, amounts and transaction IDs of such a file.

Check a QBO, QFX or OFX file

See whether a bank file is well formed before you import it: dates, amounts, transaction IDs and the account block. Free, no account, nothing stored.

Open the file checker Convert a PDF statement

"PDF bank statements can't be imported into Xero"

That is what Xero's global help says (Xero Central (opens in a new tab)). It suggests exporting the transactions from online banking instead, an option usually found when you view the transactions as a list. Alternatively, it suggests a third-party service that converts the PDF statement to a supported format.

PDF import exists, but only for Xero organisations in Canada and the United States, and not on the Xero Ledger plan (Xero Central (opens in a new tab)). The PDF must also:

  • be at most 40 inches (2880 points) high and wide, and 35MB or less, with no more than 100 pages per statement
  • have a resolution of at least 200 DPI
  • be written in English
  • be a debit or credit statement
  • be scanned straight, as crooked or slanted scans can cause an error
  • not be password protected

Xero can import PDFs from banks it doesn't list, but says the accuracy of the results might vary for those banks.

CSV file with more than 100,000 rows

Xero can't import a CSV file with more than 100,000 rows (Xero Central (opens in a new tab)). Delete any empty rows and try again, or use a different file type such as OFX or QIF.

Dates import in the wrong order

If a date in your CSV could be read as DD/MM/YY, MM/DD/YY or YY/MM/DD, Xero asks you to confirm the format (Xero Central (opens in a new tab)). Xero applies the chosen format to the whole statement and to all future CSV bank statements. A wrong choice therefore affects later CSV imports too.

The Xero CSV template guide lists the date formats and columns Xero reads. The bank CSV cleaner asks for the date order when a bank CSV is ambiguous and reshapes it into the Xero layout.

Transactions missing or duplicated

Xero recognises duplicate transactions already imported in other files and excludes them during import (Xero Central (opens in a new tab)). For a CSV import, transactions that match ones already in Xero are treated as duplicates and are not imported.

Duplicate bank statement lines can still occur when a statement is imported manually and also arrives through a bank feed (Xero Central (opens in a new tab)). Xero has a report that compares the dates and amounts of statement lines in a bank account and flags potential duplicates. It doesn't show partial matches, or duplicates that Xero already caught and deleted at import.

When a direct bank feed starts, Xero advises checking for missing or duplicate transactions (Xero Central (opens in a new tab)). You can import missing transactions by hand or delete duplicates.

A whole statement imported twice

If a whole bank statement has been imported twice, you can delete the entire statement (Xero Central (opens in a new tab)):

  1. For the bank account, click the menu icon, then select Bank Statements.
  2. From Showing, select Statements.
  3. From the list of statements, click the date of the statement you want to delete.
  4. Click Delete Entire Statement, at the bottom of the list of statement lines.
  5. Optionally, select Also delete reconciled transactions for this statement.
  6. Click Delete to confirm.

Deleted bank statements can't be restored. If you delete one in error, you need to import it again manually.

Statement balance and balance in Xero differ

Xero keeps two balances for a bank account (Xero Central (opens in a new tab)):

  • Statement Balance: the opening bank balance plus all bank statements imported into the account, manually or by bank feed.
  • Balance in Xero: the opening bank balance plus the account transactions created in Xero. This is the balance on the Balance Sheet.

If there are unreconciled transactions you think you already reconciled, Xero says you might have created duplicate transactions.

Once the account is reconciled, the two balances should be the same (Xero Central (opens in a new tab)). Xero also says to check that the account's opening balance is correct and that the statement balance matches your actual bank balance. For the wider process, see bank statement reconciliation.

Converting a PDF statement for Xero

StatementLoom reads text PDF bank and credit card statements. It refuses scanned or photographed PDFs. It checks that the opening balance plus every transaction equals the closing balance before it allows any export, and warns when two statements for the same account overlap.

It exports Xero CSV and OFX, among other formats. The free converter downloads Xero CSV without an account; OFX downloads need a free account. StatementLoom creates the file; Xero handles the import.

Sources

Tools for this

Frequently asked questions

Which file formats can Xero import?

Xero supports OFX, QFX, QIF, CSV and QuickBooks files, and recommends OFX where your bank offers it. PDF import is available only to organisations in the US and Canada.

Can I undo a bank statement I imported into Xero twice?

Yes. Delete Entire Statement removes it, but a deleted statement can't be restored, so you would have to import it again by hand if you delete the wrong one.

Why are the statement balance and balance in Xero different?

The statement balance adds imported statement lines to the opening balance, while the balance in Xero adds the transactions created in Xero. After reconciling they should be the same.

Why does Xero skip some transactions in my file?

Xero treats transactions that match ones already imported as duplicates and excludes them during the import.

Check a QBO, QFX or OFX file

See whether a bank file is well formed before you import it: dates, amounts, transaction IDs and the account block. Free, no account, nothing stored.

Open the file checker Convert a PDF statement