Opening an IIF file without wrecking what is inside it

What you are actually holding

Intuit's own description: Intuit Interchange Format files are ASCII text, tab-separated value files that QuickBooks Desktop uses to import or export lists or transactions.

So it is a text file with an unusual extension, not a database and not a spreadsheet. Lines beginning with an exclamation mark are header lines that name the columns for the rows underneath them. A transactions file uses three: !TRNS, !SPL and !ENDTRNS. A list file uses one per list, such as !CUST for customers or !ACCNT for the chart of accounts.

Each transaction is then written as a group of lines: a TRNS line for the transaction itself, one or more SPL lines for the accounts it splits across, and an ENDTRNS line to close it. Double-entry bookkeeping, flattened into text.

Windows has no default program for the .iif extension, which is why a double-click gives you a program picker rather than anything useful. That is the first thing people read as the file being broken. It is not.

The safe way to open it in Excel

Intuit's route is File, then Open, then set the file type dropdown to All Files, pick the IIF, and work through the Text Import Wizard.

In current Excel that wizard is hidden, so before you can use it: File, Options, Data, and under "Show legacy data import wizards" tick From Text (Legacy). Then Data, Get Data, Legacy Wizards, From Text (Legacy).

In the wizard, set the delimiter to Tab and nothing else. Then, in the third step, select every column and set it to Text. That last part is the one that matters. It stops Excel making decisions about your data, and you can convert the two or three columns you need afterwards.

The modern alternative is Data, then From Text/CSV, which opens Power Query. Set the delimiter to Tab and change each column's type to Text before loading. Power Query is better behaved because it shows you the transformations it intends to apply instead of applying them silently.

If you only want to look, Notepad shows the file exactly as it is, which is how you check the header lines are intact.

What Excel does if you let it guess

Three conversions wreck accounting data specifically, and all three happen quietly:

• A reference written as 00451 becomes the number 451.
• A long digit string, such as a card or account number, is truncated to 15 digits of precision and shown in scientific notation.
• Text that looks like a date is rewritten as a date, using whatever order your machine's regional settings prefer.

Newer Excel builds let you turn these off, under the Automatic Data Conversion section in Excel Options. If your build does not have it, setting every column to Text on import does the same job.

This is not a cosmetic complaint. If you edit an IIF in Excel and save it back, those conversions are now in the file, and a check number that lost its leading zero goes into QuickBooks wrong.

Why it looks like a mess even when it opened correctly

Because a single IIF file can hold several sections, each with its own header line and its own number of columns. A spreadsheet is one rectangle. Your file is not.

So you see a block of rows with customer columns, then a block with account columns that do not line up, then transaction rows where column 4 means something different than it did twelve rows earlier. Nothing is corrupted. You are looking at several tables stacked in one sheet.

Reading it goes faster if you mark the sections first. Every row starting with an exclamation mark is a header, and everything between one header and the next belongs to it.

Where your IIF came from decides what is in it

This catches people out, so it is worth being exact. QuickBooks Desktop's own export menu is File, Utilities, Export, Lists to IIF Files, and it exports lists. Intuit's help page states the limit plainly: "You can import transactions but you can't export them from QuickBooks." Only lists come out.

Which means a file full of !TRNS rows almost certainly did not come out of QuickBooks Desktop. It came from something writing an IIF to be imported into QuickBooks: a payroll service exporting paycheques, a third-party app, a bookkeeper's tool, an older system being migrated.

So before you start, look at the header lines. If they are !CUST, !VEND, !ACCNT or similar, you have lists. If they are !TRNS, !SPL, !ENDTRNS, you have transactions. The two need completely different handling and most advice you will find does not say which it is talking about.

If you are editing it to import back into QuickBooks

Back up the company file first. Intuit says this on every IIF page and it is not boilerplate, because an IIF import is not something you undo with a keystroke.

From QuickBooks Desktop 2019 the importer checks the file before it commits, reports how many items imported and how many failed, and writes a new IIF containing just the failed rows with the errors described. That failed-rows file is the most useful diagnostic in the process. Intuit also notes the importer works in multi-user mode from 2019, and advises against the "Import it for me. I'll fix it later" option, which skips the review.

Set expectations too: Intuit's own page says it "does not offer assisted technical support for creating or importing .IIF files".

When you save your edits, save as tab-delimited text, rename the extension back to .iif, and check the result in a text editor. Spreadsheets like adding quotation marks around fields containing commas, and a quoted field where QuickBooks expects a bare one is a rejected row.

If you just want the data, not a re-import

Different job, and an easier one. If you are opening this file to read the transactions, reconcile something or move the data into software that is not QuickBooks Desktop, the IIF structure does not need preserving. You need one flat table.

By hand: open it with every column as Text using the steps above, delete the sections you do not need, work out which columns the header line names, fold each transaction's splits into its own row, and fix the dates. An hour the first time, twenty minutes once you know the shape of your own files.

One thing to settle before trusting any of it. IIF records dates in whatever convention the program that wrote it used, and says nothing about which that was. QuickBooks Desktop is overwhelmingly a US product, so month-first is the sensible default, but a default is not a finding. Check a row where the day is past the 12th, which is the only kind that proves it either way.

And if the destination is QuickBooks Online, it does not read IIF. Intuit's own community threads are consistent on that, and the routes there are CSV and spreadsheets. Same with Xero.

Questions, answered.