CSV, OFX or QBO: which bank file should you import?
When you import bank transactions into accounting software, you usually get a choice of file: CSV, OFX, QBO, sometimes QIF. They can hold the same transactions, but they don't behave the same way on import. Picking the right one saves cleanup later.
The short answer
| Format | Best for | Watch out for |
|---|---|---|
| QBO (Web Connect) | QuickBooks, especially Desktop | Bank ID checks in Desktop; duplicate transaction IDs |
| OFX | Xero, GnuCash, Moneydance, most personal finance apps | Duplicate transaction IDs; account type |
| CSV | Anything, and checking the data yourself | Date order, signs, column mapping |
| QIF | Older Quicken versions | Date format; mostly replaced by OFX |
CSV: flexible, but nothing is defined
A CSV is just a table. That's its strength (you can open it, read it and fix it in any spreadsheet) and its weakness: nothing in the file says which column is the date, which way round the date is written, or whether a payment is negative.
QuickBooks Online accepts a CSV with either three columns (date, description, one signed amount) or four (date, description, credit, debit). Xero has its own CSV template with a date and a signed amount as the required fields. Both ask you to map columns and choose the date format during import. That step is where most CSV mistakes happen:
- Day and month swapped. If every date is the 12th or earlier, the software can't tell 03/04 from 04/03 and will take your word for it.
- Signs flipped. Some bank CSVs show payments as positive numbers in a "debit" column. Map that to a single amount column and every payment becomes a deposit.
- Extra rows. Opening balance lines, subtotals and blank rows at the top or bottom get imported as transactions if you don't delete them first.
Use CSV when you need to see or edit the data, or when nothing else is available. Check it before import (the closing-balance check in this article takes two minutes).
OFX: structured, with built-in duplicate protection
OFX (Open Financial Exchange) is the format banks use for direct downloads. Each transaction has a type, a posted date in YYYYMMDD form, a signed amount and a transaction ID called the FITID.
Because dates and signs are defined by the format, the two biggest CSV problems disappear. The FITID adds something CSV can't do: software remembers the IDs it has imported for an account and skips any it has already seen, so importing overlapping date ranges doesn't create duplicates.
That same feature is the main way OFX goes wrong. If two different transactions share a FITID (two identical coffees on the same day, from a converter that builds IDs from date and amount alone), the second one is silently skipped. If the same transaction gets a different FITID each time it's exported, overlapping imports create duplicates.
QBO: OFX with a QuickBooks bank ID
A QBO file is OFX with one extra field, INTU.BID, the Intuit bank ID, and a .qbo extension. QuickBooks Online is relaxed about it. QuickBooks Desktop checks the bank ID against Intuit's list of supported banks and stops the import if it doesn't recognise it.
If you're on QuickBooks Desktop and your bank offers a QBO download, use it. If you're converting from a PDF or CSV yourself, use a bank ID that Desktop accepts, and keep the FITID rules above in mind.
What about QIF?
QIF is older than OFX and has no transaction IDs, so it has no duplicate protection. Its date format also varies between versions. Use it only for software that doesn't take anything else, such as older versions of Quicken.
Which one to choose
- QuickBooks Desktop: QBO.
- QuickBooks Online: QBO or OFX if you have one; CSV works fine if you check the date order and signs.
- Xero: OFX, or Xero's CSV template.
- Only have a PDF statement: convert it, then check the result against the statement's closing balance before importing. (BankStatementHub, which I built, does the conversion and that check in one step, but any converter's output should go through the same check.)