IIF file format: build one and check it before import

The IIF file format is tab-separated text that QuickBooks Desktop imports, and each bank transaction in it is a TRNS line, an SPL line for the other side and an ENDTRNS line, with the amounts summing to zero. Evidilo writes one from a statement after adding every row against the printed balances. A QBO file is the alternative.

The IIF file format for QuickBooks, line by line

An IIF file declares its columns before it uses them. Intuit’s IIF overview and import kit page puts it in one line: “The file headers are in rows that begin with an exclamation point.” A row starting !ACCNT names the columns of the accounts that follow, and the three rows !TRNS, !SPL and !ENDTRNS name the columns of a transaction, its distribution lines and its closing line.

Below the headers, a transaction is a block. The header help in Intuit’s import kit says the distribution lines sit under the TRNS line and “The keyword ENDTRNS appears after the last distribution line.” It marks TRNSTYPE, ACCNT and AMOUNT as required, says “Debit amounts are always positive, credit amounts are always negative”, and asks that an SPL line repeat its transaction’s date and type.

No sentence on the three Intuit pages or in the kit says a block has to sum to zero. The kit shows it instead: of the 80 sample transactions in its 85 example files, 80 sum to zero. The overview page also warns that “Importing IIF files into QuickBooks provides only limited error checking.” That makes it a file to check before it goes in, because the program reading it checks little.

Building a QuickBooks IIF file for one statement

Evidilo is a bank statement to accounting import file converter: it reads a PDF, a scan, a photo of a paper statement, a CSV, an Excel or Word file or an OFX/QFX file and writes QBO, IIF, OFX, QFX, QIF, CSV or Excel. Before it writes any of them it adds up every row against the opening, closing and daily balances the bank printed and marks the first day that does not tie out. Its IIF writer builds each bank transaction in four steps:

1. Declare the accounts the file will use in !ACCNT rows: the bank account (BANK, or CCARD for a card), Uncategorized Expense if the statement has money out, Uncategorized Income if it has money in.
2. Write the TRNS line on the bank account: DEPOSIT for money in, CHECK for money out.
3. Write one SPL line with the same date and type and the opposite amount, on Uncategorized Income for a deposit or Uncategorized Expense for a payment.
4. Close the block with ENDTRNS.

We ran that code on one September statement for a JPMorgan Chase checking account ending 4417: a mobile deposit of 2,450.00 and check 1043 for 612.35. The bank account is named “JPMorgan Chase 4417” because no other name was given; when you download an IIF, Evidilo asks for the account’s name in QuickBooks Desktop, up to 31 characters, and writes that instead. The transaction lines it wrote:

Both blocks sum to 0.00. The !ACCNT header matches the one in the kit’s list declarations column for column, and all three accounts are declared before the first TRNS line. The six transaction columns (TRNSTYPE, DATE, ACCNT, NAME, AMOUNT, MEMO) all appear in the kit’s sample headers.

One column does not. The kit’s header help marks SPLID “(Required)” and all 80 !SPL headers in its samples carry it, while the check example on Intuit’s overview page has no SPLID column, and neither does Evidilo’s file. The two Intuit sources disagree, and without QuickBooks Desktop to run the file in, we cannot say which one the program holds to.

Six broken copies and the rule each one breaks

We then changed the file six ways and ran each copy through a checker built from the rules Intuit writes down. The last two copies are the ones to worry about.

An undeclared account does not stop the import. Intuit’s overview page says an account the company file does not have “will be created as a new bank account”, so a check meant for an expense account lands in a bank account nobody opened. The date rule is looser in practice than in the header help, which says “The date is always in MM/DD/YY format”: 186 dated lines in the kit’s own samples carry a four-digit year and 12 a two-digit one. Evidilo writes 09/03/2026.

What QuickBooks Desktop says it checks

Intuit’s page on the improved IIF import in QuickBooks 2019 and later says the import “eliminates the possibility of importing bad data by scanning your file for errors”, and that it “Provides a new .IIF file of the failed transactions, along with the descriptions of errors found.” You open that file, “Look for ERROR to see what the problem(s) with the file is”, correct it and import it again. The page does not list which errors the scan looks for. The overview page is more guarded: a wrong format can produce an error, or the data will import incorrectly with no error at all. A day-first date is that second kind, because it is a valid date either way.

Why an IIF file goes in once

The manual inside Intuit’s import kit, last updated in 2008, is blunt: “There is no preventative mechanism to stop the same IIF from being imported multiple times.” It goes on: “There is no Undo on the import function.” The three Intuit help pages we read on 25 October 2026 do not mention duplicates at all, and the page on exporting and importing IIF files opens its import steps with “Back up your company file before you import IIF files.” Our sixth broken copy shows why nothing can catch a repeat: an IIF line carries no id, so two copies of a transaction are identical.

A QBO file works differently. Every transaction in it carries an id, and how QBO row ids stop a statement importing twice covers what we measured on the ids Evidilo writes. For an IIF, the guard is on your side of the import. Evidilo’s download panel names the dates of an earlier conversion of the same account whose period overlaps the one in front of you, and a backup of the company file is the only undo.

How to open an IIF file in Excel

Intuit’s export, import and edit IIF files page gives the steps for Microsoft Excel:

1. In Excel’s Open window, set the file type to All Files and pick the .iif file.
2. Work through the Text Import Wizard.
3. Edit, then save the file.
4. Close Excel and choose Don’t Save when it asks again, so the file stays an IIF.

The same page warns: “When you create an IIF import file, you can’t enter numbers with leading zeros in Microsoft Excel.” Dates are the other column to watch, because a spreadsheet reads them in its own order; what a spreadsheet does to a statement’s dates shows it on a CSV. For a statement you have not imported yet, the file to edit is the statement itself, then write the IIF again.

How was this checked?

On 25 October 2026 we downloaded Intuit’s IIF import kit, read its manual and header help, and summed every sample transaction in its 85 example files. We wrote one statement with Evidilo’s production IIF writer through vitest 3.2.7, checked it against the kit, and ran six altered copies through a Python checker built from the rules Intuit states; QuickBooks Desktop itself was not run. The results are in dataset.csv, and check.py repeats every step and re-reads the three Intuit pages.

What bookkeepers ask about IIF imports

Does QuickBooks Desktop stop an IIF file being imported twice?

No. The manual in Intuit’s import kit says there is no mechanism to stop the same IIF being imported multiple times, and the import has no undo. Evidilo’s download panel names an earlier conversion of the same account whose dates overlap, which is a warning before the file exists rather than a check inside QuickBooks. A QBO file is the alternative where it fits, since each of its transactions carries an id.

Why did my IIF import create a new bank account?

The file named an account your company file did not have, and Intuit’s overview page says such an account will be created as a new bank account. Evidilo declares every account its IIF uses at the top of the file and asks for the account’s exact name in QuickBooks Desktop, up to 31 characters, before you download. Creating the accounts in QuickBooks Desktop’s chart of accounts before the import avoids it as well.

Do the amounts in an IIF transaction have to add up to zero?

Every sample transaction in Intuit’s import kit does, 80 of 80, although no Intuit page states the rule in a sentence. Evidilo writes each SPL line as the exact negative of its TRNS line, so both blocks in our test came to 0.00. Intuit’s pages do not say what QuickBooks Desktop does with a block that is off by a few cents, so check the sums before importing.

What date format does an IIF file use?

Month first: the kit’s header help says MM/DD/YY, and its own samples mostly use a four-digit year. Evidilo writes dates as 09/03/2026. A date typed day first is still a valid date, so neither our checker nor any rule in the file can tell it is wrong. Read the first few dates back against the statement before importing.

Can QuickBooks Online import an IIF file?

Not by any route Intuit’s IIF articles describe: the three we read on 25 October 2026 are written for QuickBooks Desktop. Evidilo writes the same statement as a QBO, CSV or Excel file, and its QBO file carries an id on every row. The account-name field Evidilo shows before a download, for the name QuickBooks Desktop already uses, appears for the IIF only.