A Xero OFX import error usually comes from something inside the file rather than from Xero itself: a damaged or missing header, account details for a different account, or transaction IDs that repeat. Open the OFX in a text editor, check those three areas, and either fix the file or export the same transactions as CSV.
Opening the OFX file Xero rejected
An OFX file is plain text, so the quickest way to see why Xero rejected one is to open a copy in a text editor such as Notepad or TextEdit. Do not open it in Excel or Word. Both can rewrite the file when you save, and neither shows you the raw tags you need to read.
Once it is open, scroll from top to bottom and take a first look:
- Does it start with OFX text at all? A file that begins with HTML, or with an error message from the bank's site, was never an OFX download.
- Does it end properly? The last line should close the OFX block. A file that stops mid-transaction was cut off during download.
- Are there transactions? Each one sits in a STMTTRN block. A file with a header and no STMTTRN blocks has nothing for Xero to import.
- Is it one account? Some downloads bundle several accounts. Note how many account blocks there are.
Write down what you see before you change anything. If the bank has to send a new file, "the file ends after the third transaction" gets a better answer than "Xero gave an error".
Is the OFX header why Xero won't read the file?
The OFX header is a common reason a file won't read, because it tells the software which OFX version and syntax follow. If the header is missing, mangled or mixed between the two styles, a reader can reject the whole file before it reaches a single transaction.
There are two header styles. Older OFX 1.x files start with plain lines such as OFXHEADER:100 and DATA:OFXSGML. Version 2 files are XML. The OFX 2.2 specification from the Financial Data Exchange says an XML declaration must come first, followed by an OFX processing instruction with OFXHEADER, VERSION, SECURITY, OLDFILEUID and NEWFILEUID, all of them required. Its sample is OFXHEADER="200" VERSION="220".
Look for these faults:
| What to look at | What good looks like | Common fault |
|---|---|---|
| First line | OFXHEADER:100 (version 1) or an XML declaration (version 2) | Blank lines or stray characters before it |
| Header style | All lines in one style | Version 1 lines followed by an XML declaration |
| Version 2 processing instruction | All five attributes present | Attributes missing after a hand edit |
| Body start | An OFX tag after the header | Text from a web page |
Telling the two styles apart is covered in the guide to SGML and XML OFX headers.
OFX account details against the Xero bank account
The account details in an OFX file say which account the transactions belong to, and they should match the Xero bank account you are importing into. A bank account uses a BANKACCTFROM block with a routing number, account number and account type. A credit card uses a CCACCTFROM block with just the account number.
The OFX specification lists the bank account types as CHECKING, SAVINGS, MONEYMRKT, CREDITLINE and CD. Check three things:
- The account number in ACCTID is the account you meant. Downloads from the wrong account are an easy slip when one login covers several.
- The block type fits. A card statement should be CCACCTFROM, not a checking account block.
- The amounts read the right way. The specification says amounts are signed from the customer's point of view, so a card purchase is negative and a card payment is positive.
If the file holds several accounts, split them or download each account on its own.
Do repeated FITID values break an OFX import?
Repeated FITID values can make lines disappear from an OFX import. FITID is the transaction ID the bank gives each line, and the OFX specification says its main job is to let software detect duplicate downloads. It must be unique within an account, so two different transactions sharing one FITID can be treated as a single line.
The opposite problem happens too. If a tool rebuilds a file and gives the same transactions new FITIDs, software that relies on the IDs sees them as new, and lines you already imported can arrive twice. If lines doubled after an import, remove the duplicates in Xero before importing again. If the same file also went into QuickBooks and matched the wrong entries there, unmatching a transaction in QuickBooks Online shows how to back that out.
To check, search the file for FITID and scan the values. Every line should have one, and no two should be the same.
When to swap the OFX for a CSV in Xero
Swap the OFX for a CSV when the bank's own OFX export keeps failing, or when you have only a PDF statement and want to check rows first. Xero says on its bank reconciliation page that you can import transaction data by hand if your bank doesn't connect, so a clean file of some kind is the goal.
A CSV has its own traps. Amounts must be numbers with one clear sign, and descriptions should not carry characters that confuse the columns. The same rules apply in QuickBooks, so the posts on fixing amount column errors in a QuickBooks CSV and special characters that break a QuickBooks CSV are useful checklists for a Xero CSV as well.
If all you have is a PDF statement, SimpleBankConvert is being built to turn one into a CSV laid out for Xero's statement import, with payee and reference in their own columns. It isn't open yet; get started to be told when it is.