Why Excel makes such a mess of an IIF file
IIF is tab separated text, so Excel opens it, and then three things go wrong at once.
-
Header rows are scattered through your data. An IIF file declares its
columns with rows beginning
!, and it can declare them more than once. Those rows sit in the middle of the sheet looking like transactions. -
Every transaction takes at least three rows. A
TRNSline, one or moreSPLlines, thenENDTRNS. Sorting by date scrambles them beyond repair. - The two sides have opposite signs. Spend 45.20 and the TRNS line reads -45.20 while the SPL line reads 45.20. Sum the amount column and you get zero, every time.
This page reads the structure properly and gives you one row per transaction.
It reads your file's own column headers
There is no fixed column order in IIF. The file we write has eight columns; QuickBooks exports more than twenty, and which ones depend on the version. A converter that assumes a position reads the date out of the amount column on somebody else's file, and it does not report an error while doing it.
This one maps every column by the name in the ! header, and
follows it when the file redeclares the columns halfway through.
What you get that a bank CSV doesn't have
Exporting a statement from your bank gives you a date, a description and an amount. An IIF file has been through QuickBooks, so it carries the work you already did:
- Category. The account on the split line. That's your categorising, kept.
- Payee. Separate from the description, because IIF keeps them apart.
- Account. Which of your accounts each transaction belongs to, by name.
- Transaction type.
CHECK,DEPOSIT,CREDIT CARD CHARGEand the rest.
Those columns only appear when your file actually has the data. If a field is empty throughout, the column is left out rather than added as a wall of blanks.
The one thing a CSV can't carry
A split transaction, where one payment is divided across several categories, has no representation in a CSV. One row, one category. Those transactions come out as a single line for the total with the category blank.
The totals stay right; the breakdown does not survive. The page lists exactly which transactions were affected rather than letting you find out later. Converting to QIF keeps them, because QIF has fields for splits.
Questions
Can I just rename the file to .csv?
No. The extension is not the problem. IIF separates fields with tab characters rather than commas, and its structure spreads one transaction across several rows with headers in between. Renaming it changes nothing about any of that.
Which QuickBooks versions does this work with?
Any of them, because it reads the column layout out of the file instead of assuming one. That covers Pro, Premier and Enterprise on Windows and QuickBooks for Mac. If a file does not come out right we would like to see it.
Why is the amount column negative for spending?
Because that is what IIF says, from the account's point of view. Money leaving your account is negative and money arriving is positive, which is what most spreadsheets and importers expect. If you want the two directions in separate columns, pick the QuickBooks 4-column layout.
Some transactions are missing from the download.
The page tells you when that happens and how many, rather than quietly shortening the file. A transaction is left out when it has no readable date or no readable amount, which usually means the file was truncated or written by something that got the format wrong. If the count surprises you, the original is worth a look.
Is my file uploaded anywhere?
No. Every converter here runs in the browser, and each page ships a Content Security Policy
with connect-src 'none', so your browser refuses to let the page make any
network request. Watch the Network tab in developer tools while you convert and it stays
empty.
Related
- IIF to QIF converter keeps split transactions
- CSV to IIF converter the other direction
- Why an IIF import can silently do nothing what we measured
- QBO to CSV converter for bank downloads instead