What Is an IIF File? How to Check a QuickBooks Import File Before You Trust It
An IIF file is a tab separated text file for QuickBooks Desktop, and readers can open damaged ones with no warning. Jump to your question below, or work through it in order.
What Is an IIF File? A tab separated text file that QuickBooks Desktop imports
Short answer
An IIF file is a text file that QuickBooks Desktop uses to import and export lists and transactions. Its fields are separated by tabs, and its first column has a keyword like TRNS or ACCNT. The ending .iif is a label, and the first line is what counts.
We read 72 public IIF files with six readers, and we damaged one IIF file in 50 ways. Three readers agreed on the count and the total of 35 of the 49 IIF files with transactions. Of the 50 damaged copies, only 14 gave the same events in all three readers that read events.
To check IIF files without QuickBooks, read the six steps. To see what is inside, read the layout. If your IIF file will not import, begin with the eight causes.
An IIF file is a tab separated text file in the Intuit Interchange Format. QuickBooks Desktop reads it to import lists and transactions. Its first line is a header row that starts with an exclamation mark, and the ending .iif is only a label.
Picture an accountant who emails you journal.iif, or a payroll service that offers an IIF download. Open the IIF file in Notepad and you can read it. Every line is a row, and the tab character splits the row into fields.
Wikipedia calls the format a proprietary text file that QuickBooks uses to import and export lists and transactions. Intuit describes IIF files as ASCII text, in a tab separated layout, that QuickBooks Desktop uses to import or export lists or transactions.
FileInfo files .iif as a data file from Intuit that is tab delimited and similar to a TSV file. It lists QuickBooks, Excel and Notepad as programs that open it. The page was last updated in 2010, so read it as a directory entry.
Wikipedia lists three media types for the format, application/qbooks, application/qbookspro and text/iif. We found none of the three in the IANA lists we read. The text list has no iif row, and the application list has no qbooks row.
Where our IIF files come from
Our 72 IIF files come from the test folders of 8 public projects. They are test files and sample output from tools that read or write IIF files, and many are built to be odd. We found no file that QuickBooks wrote, because none of the 72 starts with the !HDR row that Intuit lists for exports.
The 72 files total 126,806 bytes, with a median of 757 bytes. 46 are under 1 KB, 25 are between 1 KB and 10 KB and one is bigger, at 46,830 bytes. Together they have 362 TRNS rows and 715 SPL rows, and the SPL rows are the lines that move money.
The ending is no guide to the content. 54 of the 72 IIF files declare transactions and the other 18 have only lists, like accounts, customers or vendors. Our tests below also use 50 damaged IIF files made from one 745 byte file.
IIF file facts at a glance
| Property | Value |
|---|---|
| Meaning | Intuit Interchange Format. FileInfo files it as a data file from Intuit. |
| Made for | QuickBooks Desktop. Intuit says it imports or exports lists or transactions. |
| Text | Intuit says ASCII, tab separated. 71 of our 72 IIF files read as UTF-8 and one is Windows 1252. |
| First line |
A header row that starts with !. 70 of our 72 IIF files start with it.
|
| Keywords |
TRNS, SPL and ENDTRNS for transactions. ACCNT, CUST, VEND, CLASS and INVITEM for lists.
|
| Media type | Wikipedia lists three. We found none in the IANA lists. |
| Line ends | LF in 40 of our 72 IIF files, CRLF in 31 and a mix in one. |
| Not supported | QuickBooks Online does not import IIF files, per an Intuit community answer. |
| Opens with | QuickBooks Desktop, text editors and spreadsheet programs. We tested five readers and LibreOffice. |
| Our tests | 72 public files, 50 damaged copies of one file and six readers, on Linux. |
Wrong page? QIF files use the Quicken Interchange Format, a different text format. QBO files are bank statements for QuickBooks.
What Is Inside an IIF File? Header rows first, then transactions in blocks
An IIF file is a stack of tab separated rows. Header rows start with ! and name the fields. Data rows start with the same keyword. A transaction opens with TRNS, adds one or more SPL rows and closes with ENDTRNS.
We wrote one small file and called it base.iif, so every line can be read. It has four accounts, one check and one deposit. It is 745 bytes and 16 lines long. The figure shows its blocks from the top.
Read the figure from the top. Intuit on IIF headers says that header rows begin with an exclamation point, and that the first column of a header names a keyword. A data row repeats that keyword, so the reader knows which header to use.
The first lines of an IIF file
head -n 8 base.iif | sed 's/\t/ → /g'
!ACCNT → NAME → ACCNTTYPE → DESC
ACCNT → Checking → BANK → Main account
ACCNT → Office Supplies → EXP → Paper and pens
ACCNT → Sales → INC → Services sold
ACCNT → Sales Tax Payable → OCLIAB → Tax we owe
!TRNS → TRNSID → TRNSTYPE → DATE → ACCNT → NAME → AMOUNT → DOCNUM → MEMO
!SPL → SPLID → TRNSTYPE → DATE → ACCNT → NAME → AMOUNT → DOCNUM → MEMO
!ENDTRNS
The first line, !ACCNT, names the four fields of an account row. The next four lines are accounts. The last three lines above are header rows for transactions, and they come before any transaction. !ENDTRNS has no fields, because ENDTRNS has no values.
Every data row is a keyword and values in the order of its header. In the check, the first value after TRNS is empty, because we left the TRNSID field blank. Two tabs in a row make a blank field.
Transactions are blocks
Every transaction is a block. TRNS is the first row and gives the main line. One or more SPL rows follow, and ENDTRNS closes the block. In base.iif the check has one split line and the deposit has three.
In both blocks the amounts add to zero. The check is -125.40 plus 125.40. The deposit is 1500.00 minus 1000.00, 400.00 and 100.00. An Intuit payroll page names the error that the transaction is not in balance, and it gives no cause.
Of the 90 blocks in our public files that end in an ENDTRNS row, 9 do not add to zero. We do not know whether QuickBooks accepts them. Treat any block that does not add to zero as a warning, and check the amounts before you import.
Tabs, quotes and blank fields
The separator is the tab, so any tab inside a name or a memo splits the field in two. 22 of our 72 IIF files put quotes around some fields, and 21 have extra tabs at the end of rows. The Intuit pages we read give no rule for quotes, so we test them as a risk.
Dates are written month first. Of the 978 dates in our IIF files that fit a pattern, 719 use two digit years like 7/16/98 and 259 use four digit years. None writes a four digit year first. Amounts with commas, like 1,500.00, appear in 74 rows. 24 amounts are in quotes.
The rows you meet most
| Row | What we found |
|---|---|
ACCNT
|
An account in the chart of accounts. 20 of our IIF files declare it. Intuit says an account that is not in the books is created as a new bank account. |
CUST and VEND
|
Customers and vendors. 16 files declare CUST and 13 declare VEND. The headers reach 44 fields.
|
TRNS
|
The first line of a transaction. 54 files declare it, and 49 have rows. The 49 have 362 TRNS rows. The most common types are DEPOSIT with 203 rows, CHECK with 88 and GENERAL JOURNAL with 36.
|
SPL
|
A split line that gives part of the money. 715 rows in our IIF files. 48 of 90 blocks have exactly one, and the largest block has 101. |
ENDTRNS
|
Closes a block. 54 files declare it. One tool wrote 272 rows as ENDTRNS D and ENDTRNS C, with a letter after the keyword.
|
!HDR
|
The header row that Intuit lists for exports, with fields like PROD and VER. None of our 72 IIF files has it. |
The list is small. Our script reads rows, blocks and amounts. It reads no date and knows no account, so damage inside a value can pass it, as the damage tests show.
Which Kind of IIF File Do You Have? The ending names none of the lists, separators or dates
Four questions sort an IIF file, which lists or transactions it declares, how its fields are separated, how its numbers are written and how big it is. Of 72 IIF files, 54 declare transactions, 5 use commas in place of tabs and 22 use quotes.
Most questions about IIF files come down to what the IIF file is for and how it is written. A text editor answers both in one minute, and our script answers in one second.
Which lists and transactions a file declares
54 of our 72 IIF files declare TRNS and ENDTRNS, and 52 declare SPL. 20 declare accounts, 16 customers, 13 vendors, 12 classes and 11 items. In all, the files use 26 different header keywords, and most files declare more than one.
The transaction types are mostly money in and out. Of the 362 TRNS rows, 354 name a type. 203 are deposits, 88 are checks and 36 are general journal entries. The other 27 spread over 12 types, like bill payments, invoices and credit memos.
How the fields are separated
Intuit says IIF files are tab separated. 67 of our 72 IIF files agree. Five use commas in place of tabs, and one of the five also puts every field in quotes. All five came from the test folder of one parser, so they show what that tool expects to meet.
40 of the 72 IIF files use LF line ends, 31 use CRLF and one mixes them. 12 files have blank lines. One file is not UTF-8, and it was saved in Windows 1252. None starts with a byte order mark.
How the numbers are written
Of the 1,085 dates in the IIF files, 978 fit the month first pattern with two or four digit years. 103 do not, and 102 of those are in one file that writes 05-01-15. Four dates are blank. Amounts use commas in 74 rows, and none uses a dollar sign or parentheses.
9 of the 90 blocks that end in ENDTRNS do not add to zero. Those 9 blocks lie in 9 files. We cannot say from the IIF files whether the writer erred or left out a line on purpose.
Not sure the file is an IIF file at all? Check it with our script.
What Did We Find in 72 IIF Files? Six readers, and a split on 14 of 49 transaction files
Six readers saw our 72 IIF files in different ways. Our script refused 10, iif-parser failed on 13, pandas on 7 and bank-formats reported errors on 13. Of the 49 IIF files that have transactions, three readers agreed on the count and the total for 35.
We gathered every IIF file we could find in the test folders of 8 public projects. After we dropped copies with the same hash, 72 files were left. Many of them are built to be odd on purpose, so this is a study of test files and not of books.
The six readers
Our script is the 52 line Python program printed in the check part. The file command names the type. pandas reads the IIF file as a tab separated table. iif-parser is a Ruby gem that reads IIF files into entries. bank-formats is a JavaScript library that converts IIF files into bank transactions.
LibreOffice is the sixth reader, and it works by opening a file and saving it again. We used it twice on every file, once with the default import and once with special number detection on. We tested no QuickBooks and no Excel.
Where the readers agree
The file command called all 72 IIF files text. It cannot separate an IIF file from any other text, and it said ASCII text for 71 of them and extended ASCII for one. pandas opened 65 files as a table, but it checks no meaning, and its table can have the wrong shape.
iif-parser opened 59 files. bank-formats opened all 72, and reported errors on 13. Our script refused 10, and it gave notes on 30 more, like quotes around fields or a block that does not add to zero. It had no complaint about the other 32.
Where the readers split
49 of the 72 IIF files have transaction rows. For 35 of them, our script, iif-parser and bank-formats gave the same count of TRNS rows and the same total. For the other 14 they split. Nine of the 14 come from the sample folder of one project, and the other five are odd test files.
The nine copies have a row with the word Service where the amount should be. iif-parser refused all nine, with an error that Service is not a number. bank-formats returned no transaction and counted errors. Our script counted the row and noted that the amount is not a number.
TRNS → WEFDS234CT5 → BILLPMT → 7/16/98 → Checking → Bayshore → Service → 35 → Test Memo → N → YThe other files split for other reasons. bank-formats returned no transaction for 3 of the 19 files that have both quotes and transactions, and iif-parser refused three files that have the text foo in a budget amount. We do not know which reader QuickBooks would agree with.
What the quirks show
TRNS rows. iif-parser keeps every row. Our script checks the shape.
ENDTRNS on 272 rows. Our script refused it and iif-parser opened it.
Read the study with care. It shows what readers do with 72 public test files. Many are odd on purpose, and a reader that agrees on our IIF files can still fail on your IIF file. We tested no import into QuickBooks.
What Do Readers Do With a Damaged IIF File? 50 broken copies, six readers and silent changes
We damaged one 745 byte IIF file in 50 ways. Our script flagged 33 copies. iif-parser changed its answer on 11 copies without a warning, and bank-formats on 11. Only 14 copies passed all three readers.
The base file has 2 transactions and 4 split lines, and its amounts add to 1374.60 on the TRNS rows. We changed one thing at a time and sorted the 50 copies into 6 groups. Then we read every copy with six readers.
Green means the reader gave the same count and total as for the intact file and raised no warning. Amber means it read the copy and gave a different answer with no warning. Red means it refused the copy or raised a warning. For LibreOffice, amber means one value changed on the round trip.
Silent changes are the risk
iif-parser changed its answer on 11 copies without any warning. bank-formats did so on 11 copies, and 8 of them are the same copies. The copy with no !TRNS row (A1) was read by iif-parser as 2 transactions with a zero total, and bank-formats returned 0 transactions.
A file cut in half (C4) gave one transaction and no split line, and neither library complained. The file saved as UTF-16 (B2) gave 0 transactions in both libraries and no error, and our script refused it. 9 copies passed none of the three readers.
What the groups show
| Group | What we found |
|---|---|
| A. Structure lines |
Our script flagged 8 of 9. Both libraries read A2, A3, A5, A6 and A7 as intact, though those lack a header row or an ENDTRNS, or have an extra one. Losing !TRNS (A1, A4, A9) broke the amounts.
|
| B. First bytes |
A byte order mark (B1), text before the first line (B4), 6 missing bytes (B5) and a space for the first ! (B6) were read as intact by both libraries. UTF-16 (B2) gave nothing, and three blank lines (B3) changed nothing.
|
| C. Cut short | The libraries read copies cut at 5 bytes, after the first line and in half as valid files with fewer transactions. Only a cut inside a row (C3) made bank-formats warn. |
| D. Separators | LF, CR, mixed ends and extra tabs gave the same answer in all three readers. Commas (D4), quotes (D8) and spaces (D11) split them, and four spaces for tabs (D5) gave nothing. |
| E. Values | iif-parser refused the month 13 date (E2), the dollar sign (E5) and the text abc (E8). bank-formats dropped E2 and E3 with an error. Our script has no date or name check, so it passed E1, E2, E3, E5, E9 and E10. |
| F. Extra data | A second copy of the file (F3) read as 4 transactions in all three. iif-parser refused 64 KB of random bytes (F1). Both libraries read the other extras as intact. |
What LibreOffice did
We opened every copy in LibreOffice 24.2 and saved it again as a tab separated file. For 42 copies no value changed. It trimmed trailing zeros, so 125.40 came back as 125.4. We count that as a format change and not a value change.
The 8 copies that changed are B1, B2, C3, D11, E5, E6, F1 and F6. It dropped the byte order mark and the NUL byte. It rewrote the UTF-16 file as UTF-8. With special number detection on, it removed the dollar sign, turned (125.40) into -125.4 and completed the date cut at 11/02/20 as 11/02/2020.
With special number detection on, it changed values in 49 of the 72 public IIF files, and in 46 of them it rewrote dates. 7/12/1998 became 07/12/1998, and 10/24/14 became 10/24/2014. The two digit year is a guess.
Read the grid with care. Every copy changes one thing in one IIF file that we wrote. Damage in the wild changes many things at once. We imported no copy into QuickBooks, so a green cell means only that the reader gave the same events as for the intact file.
What Does Our Expert Say About IIF Files? A note from Neeraj Singh on trusting one reader
Check IIF files with two readers before you trust one answer. Three readers passed only 14 of 50 damaged copies, and two of them changed their answer in silence on 11. A first line check takes seconds, and the second reader takes longer.
Expert insight
An IIF file looks harmless because it is text, but QuickBooks changes your books when you import it. In our tests, three readers agreed on 35 of 49 public transaction files and passed only 14 of 50 damaged copies. Read the file with two tools, and back up your company file before you import.
Why one reader proves little
A reader that opens an IIF file shows that the rows make sense to that reader. It does not show that every amount reads right. In our tests our script flagged 33 of 50 damaged copies, and it missed damage in values, like a month 13 date.
The check has a limit. Two readers that agree can both be wrong, and neither says what QuickBooks will do. The script from the check reads rows, blocks and amounts. Compare its answer with a library and read the dates yourself.
Back up before you import. Intuit says to back up the company file before you import an IIF file, so you can restore it if a problem occurs. Write down the SHA256 hash of the original IIF file too, and work on a copy.
How Do You Open an IIF File? QuickBooks, an editor, a spreadsheet and a library
QuickBooks Desktop opens an IIF file through File, Utilities, Import. A text editor or spreadsheet program can read the IIF file first. We read our IIF files with a script, three libraries and LibreOffice, and none of them can say what QuickBooks will accept.
Opening an IIF file means two things. You can read it to see what is inside, or you can import it to put its lists and transactions into your books. Reading is safe on every copy. Importing changes the books, so back them up first.
Import an IIF file in QuickBooks Desktop
Intuit gives these steps for Windows. The page covers QuickBooks Desktop for Windows and Mac, and it does not mention QuickBooks Online. Intuit adds that it offers no technical support for IIF import or export.
- Back up the company file. Intuit marks this step as important, because you can restore the backup if a problem occurs.
- Sign in as an admin. In multi user mode, choose File and then Switch to Single user Mode.
- Choose File, then Utilities, then Import, then IIF Files.
- Select the IIF file and choose Open. Then choose OK to confirm.
On a Mac the path is File, then Import, then From IIF Files. QuickBooks Desktop 2019 and later has an improved import tool that scans the file for errors before it imports. It also writes a new IIF file with only the failed records, and it marks the problems with ERROR.
QuickBooks Online does not import IIF files. An Intuit community answer from October 2023 says the program is unable to support importing IIF files, and it names CSV, Google Sheets and XLS or XLSX files instead. Read the answer.
Read an IIF file in a text editor
Open a copy in Notepad or any text editor. You see header rows that start with !, and data rows under them. Fields line up by tab stops. A blank field shows as extra space, so text editors are good for the first line and poor for the amounts.
Read an IIF file in a spreadsheet
Intuit says to open the IIF file in Excel, set the file type to All Files, and follow the Text Import Wizard. Intuit warns that Excel cannot keep numbers with leading zeros, and it gives a custom cell format as the workaround. Intuit also says to exit Excel without a second save.
We tested LibreOffice instead of Excel. We opened every copy and saved it as a tab separated file with the command below. For the intact file, every value came back the same, but 125.40 came back as 125.4. The damage part lists the 8 copies where a value changed.
soffice --headless --infilter="Text - txt - csv (StarCalc):9,34,76,1,,0,false,false" --convert-to "csv:Text - txt - csv (StarCalc):9,34,76,1,,0,false,false,false" --outdir out base.iifRead an IIF file from the command line
The file command names the type of the IIF file, and it is the shortest test. It cannot separate an IIF file from other text. It said ASCII text for 71 of our 72 IIF files.
file base.iif B2.iif
base.iif: ASCII text, with CRLF line terminators
B2.iif: Unicode text, UTF-16, little-endian text, with CRLF line terminatorsA table reader does worse than it looks. Rows in an IIF file have different widths, because the first line is a header with 4 fields and later headers have 9. pandas takes the width from the first line, and it stops at line 6.
python3 -c "import pandas as pd; pd.read_csv('base.iif', sep='\t', dtype=str)"
ParserError: Error tokenizing data. C error: Expected 4 fields in line 6, saw 9Read an IIF file with a library
Two open source libraries read IIF files in code. iif-parser is a Ruby gem, and it returns every row as an entry. bank-formats is a JavaScript library, and it keeps only the TRNS rows as bank transactions. Both read base.iif and agreed on 2 transactions.
require 'iif/parser'
ps = Iif::Parser.new(File.binread('base.iif'))
puts ps.transactions.size
puts ps.entries.count { |e| e.type == 'SPL' }
puts ps.entries.select { |e| e.type == 'TRNS' }.map { |e| e.amount.to_s('F') }.join(' ')
# prints
2
4
-125.4 1500.0const bank = require('bank-formats');
const text = require('fs').readFileSync('base.iif', 'utf8');
const r = bank.iifToTransactions(text, {});
console.log(r.transactions.length, r.errors.length);
// prints
2 0To use iif-parser we replaced the rchardet gem with a substitute and changed one method, so that the text is never passed to open. The open method in Ruby can start a command if the text begins with a pipe. We used both libraries on test files only.
| Reader | Result |
|---|---|
| Our script | Refused 10, gave notes on 30 and had no complaint about 32. It reads rows, blocks and amounts. |
| file | Called all 72 IIF files text. It cannot separate an IIF file from other text. |
| pandas | Opened 65 as a table, and refused 46 of 50 damaged copies because the rows differ in width. |
| iif-parser | Opened 59 and failed on 13. It changed its answer on 11 damaged copies with no warning. |
| bank-formats | Opened all 72 and reported errors on 13. It changed its answer on 11 damaged copies with no warning. |
| LibreOffice | Opened every file. A default import changed values in 5 files, and special number detection changed values in 49. |
How Do You Check an IIF File? Six steps, and only the last two read values
Check an IIF file in six steps. Copy it, read the first line, read every header row, read the blocks, add up the amounts and read it with a second tool. Only the last two steps read values, and no step shows what QuickBooks will do.
The six steps
- Copy the IIF file, write down its SHA256 hash and work on the copy.
-
Read the first line in a text editor. It should start with
!, and the next characters should be a keyword likeTRNSorACCNT. - Look at the separators. Fields should be split by tabs. Commas, spaces or quotes around every field are a risk.
-
Match every data row to a header row. A
TRNSrow needs a!TRNSheader before it, and every block needs a closingENDTRNS. - Add up the amounts of every block. They should come to zero, and any block that does not is a warning.
- Read the IIF file with another tool, like our script, a library or a spreadsheet. Compare the counts and the total.
A 52 line script that reads the IIF file
The Python script below does steps 2 to 5 in one second. It reads the rows, splits them by tab and strips quotes. It keeps a header per keyword and counts TRNS and SPL rows. It also adds up the amounts in every block. It prints REFUSED when it finds a problem, and it adds notes for smaller worries.
import re, sys
from decimal import Decimal, InvalidOperation
def peek(path):
raw = open(path, 'rb').read()
out = {'size': len(raw), 'problems': [], 'notes': [], 'trns': 0, 'spl': 0, 'amount_sum': '0'}
if not raw:
out['problems'].append('empty file'); return out
if b'\x00' in raw: out['problems'].append('NUL byte')
try: text = raw.decode('utf-8')
except UnicodeDecodeError:
text = raw.decode('cp1252', errors='replace'); out['notes'].append('not UTF-8')
if text.startswith(''): out['notes'].append('byte order mark'); text = text[1:]
lines = [l for l in re.split(r'\r\n|\r|\n', text) if l.strip()]
if not lines or not lines[0].startswith('!'):
out['problems'].append('first line is not a header row')
if not any('\t' in l for l in lines): out['problems'].append('no tabs')
heads, block, total, nblocks = {}, False, Decimal(0), 0
for n, line in enumerate(lines, 1):
f = line.split('\t')
if any(x[:1] == '"' for x in f): out['notes'].append('fields in quotes'); f = [x[1:-1] if len(x) > 1 and x[0] == '"' and x[-1] == '"' else x for x in f]
kw = f[0]
if kw.startswith('!'): heads[kw[1:]] = f; continue
if kw not in heads and kw != 'ENDTRNS':
out['problems'].append('row %d has no header row (%s)' % (n, kw[:12])); continue
if kw in heads and len(f) > len(heads[kw]) and any(x.strip() for x in f[len(heads[kw]):]):
out['problems'].append('row %d has more fields than its header' % n)
if kw == 'TRNS':
if block: out['problems'].append('row %d: TRNS block never ended' % n)
block, total = True, Decimal(0); out['trns'] += 1
elif kw == 'SPL':
if not block: out['problems'].append('row %d: SPL outside a TRNS block' % n)
out['spl'] += 1
elif kw == 'ENDTRNS':
if not block: out['problems'].append('row %d: ENDTRNS with no TRNS' % n)
elif total != 0: out['notes'].append('block does not add to zero')
block = False
if kw in ('TRNS', 'SPL') and 'AMOUNT' in heads[kw]:
i = heads[kw].index('AMOUNT')
try: v = Decimal(f[i].replace(',', '').replace('$', '')); total += v
except (IndexError, InvalidOperation): out['notes'].append('row %d: amount is not a number' % n)
if kw == 'TRNS' and 'AMOUNT' in heads[kw]:
try: out['amount_sum'] = str(Decimal(out['amount_sum']) + Decimal(f[heads[kw].index('AMOUNT')].replace(',', '').replace('$', '')))
except (IndexError, InvalidOperation): pass
if block: out['problems'].append('last TRNS block never ended')
if not any(not l.split('\t')[0].strip('"').startswith('!') for l in lines): out['notes'].append('no data rows')
out['notes'] = sorted(set(out['notes']))
return out
if __name__ == '__main__':
for p in sys.argv[1:]:
r = peek(p); print(p, 'OK' if not r['problems'] else 'REFUSED', r['trns'], 'TRNS', r['spl'], 'SPL', r['problems'][:3], r['notes'])
On the intact file it finds 2 transactions and 4 split lines, and it has no complaint. On damaged copies it names the problem. A6 lost its last ENDTRNS row, B4 has a line of text in front and C4 is cut in half. E4 has an amount in quotes, which it reads and notes.
python3 iif_peek.py base.iif
base.iif OK 2 TRNS 4 SPL [] []
python3 iif_peek.py A6.iif B4.iif C4.iif E4.iif
A6.iif REFUSED 2 TRNS 4 SPL ['last TRNS block never ended'] []
B4.iif REFUSED 2 TRNS 4 SPL ['first line is not a header row', 'row 1 has no header row (Exported by )'] []
C4.iif REFUSED 1 TRNS 0 SPL ['row 10 has no header row (SP)', 'last TRNS block never ended'] []
E4.iif OK 2 TRNS 4 SPL [] ['fields in quotes']What the script cannot see
The script reads no date and checks no account name. It passed two copies, E2 with a date in month 13 and E9 with a misspelled account. It also treats fields in quotes as a note and not as a refusal, because we do not know whether QuickBooks accepts quotes.
A refusal from our script is a reason to look closer, not a verdict. It refused a file with a stray letter after ENDTRNS that iif-parser opened. A pass from our script is not a promise either. It passed 17 of 50 damaged copies, and only 14 passed all three readers.
Want the causes of an import error? Go to the causes.
Is an IIF File Safe to Open? Reading is safe, and importing changes your books
Reading an IIF file with a reader is safe, because the IIF file is text. Importing one is a different act, because QuickBooks changes your books. Back up the company file first, and use a copy of the IIF file.
An IIF file is rows of text, and a text editor shows all of it. Reading it starts no program. We scanned no IIF file for malware, so we cannot speak for files from unknown senders. The ending proves nothing, because the file command cannot separate IIF from other text.
What an import can do
Intuit warns that the format must match exactly. A wrong format can cause an error, or the data can import wrong with no error. Intuit says to check the imported data for completeness and accuracy. It also says it gives no assisted support for IIF files.
Accounts in the file must already exist. Intuit says an account that is not in the books is created as a new bank account. Intuit also says that imported transactions are not linked, so an invoice and its payment must be linked afterward in QuickBooks.
The improved import is the safer door
QuickBooks Desktop 2019 and later scans the file before it imports. It reports how many records worked and how many failed, and it writes the failed records to a new file. Intuit advises against the option called Import it for me, I will fix it later, because it skips the review.
Use a copy. Work on a copy of the IIF file, and write down its SHA256 hash. The hash shows which file you received, and it shows if a download changed later. We tested no import into QuickBooks, so we cannot say what any damaged copy would do there.
Why Will Your IIF File Not Import? Eight causes, and what to do about every one
An IIF file stops working for eight reasons that we could test. A broken first line, a cut copy, missing header rows and blocks that break come first. Odd separators, wrong values, odd bytes and the wrong place come last. Our script flagged 33 of 50 damaged copies.
We cannot say what QuickBooks does with any damaged copy, because we had no QuickBooks. The table lists what our readers did with every kind of damage, and what Intuit says about it. The first step for every cause is the same, copy the IIF file and work on the copy.
| Cause | What we saw and what to do |
|---|---|
| A broken first line (1) |
A byte order mark (B1), text in front (B4), 6 missing bytes (B5) and a space for the first ! (B6) were read as intact by iif-parser and bank-formats. Our script flagged all four. UTF-16 (B2) gave nothing in either library. Fix: Make ! the first character with nothing before it, and save the IIF file as text.
|
| A cut copy (2) | Copies cut at 5 bytes, after the first line and in half were read as valid files with fewer transactions, and neither library complained. Only a cut inside a row (C3) made bank-formats warn. Fix: Compare the size and the hash with the source, then copy the IIF file again. |
| Missing header rows (3) |
Without the !TRNS row (A1, A4, A9) iif-parser gave a total of zero and bank-formats returned no transactions. Intuit says header spelling and placement must be correct. Fix: Restore the header rows from Intuit's sample files for the same kind of transaction.
|
| Blocks that break (4) |
A missing or extra ENDTRNS row (A5, A6, A7) passed both libraries. A missing split line (A8) passed with no warning. Our script flagged all four. 9 of 90 public blocks do not add to zero. Fix: End every block with ENDTRNS and add up the amounts of every block.
|
| Wrong separators (5) | Commas for tabs (D4), every field in quotes (D8) and spaces around fields (D11) split the libraries, and four spaces for tabs (D5) gave nothing. A tab inside a memo (D12) was read as intact. Fix: Use one tab between fields, with no spaces and no quotes. |
| Broken values (6) | iif-parser refused a date in month 13 (E2), a dollar sign (E5) and the text abc in an amount (E8). bank-formats dropped E2 and E3, a blank date, with an error. Fix: Correct the value that the error names, and write dates month first. |
| Odd bytes (7) |
64 KB of random bytes after the end (F1) made iif-parser fail, and bank-formats read the file as intact. A NUL byte (F6) and a byte order mark changed in LibreOffice. iif-parser and bank-formats handled an accent saved as UTF-8 or Windows 1252. Fix: Save as ASCII or UTF-8 text and delete everything after the last ENDTRNS.
|
| The wrong place (8) | Intuit says IIF files cannot move between country versions of QuickBooks, and QuickBooks Online does not import them. Excel cannot keep numbers with leading zeros. Fix: Use the QuickBooks Desktop of the same country, or load a CSV file into QuickBooks Online. |
Fix an IIF file in seven steps
- Copy the IIF file, write its SHA256 hash and work on the copy.
-
Read the first line with the check steps. It should start with
!. - Read the file with the check script. A note or a REFUSED line names the point where the IIF file stops making sense.
- In QuickBooks Desktop 2019 and later, import the copy with the improved tool, and open the error file it writes. Fix the lines marked ERROR.
- Compare the count and the total with a second reader, as in the library examples.
- If the IIF file is smaller than the source says, copy it again and compare the hash.
- Back up the company file, then import the corrected copy.
What to send when you ask for help
Intuit offers no assisted support for IIF files. If a vendor or an accountant made the IIF file, send them these lines. They show what was read and where it stopped.
Subject: IIF file will not import
File: name, size in bytes and SHA256 hash
First lines: the output of iif_peek.py
QuickBooks: product, year, country and Windows or Mac
Error: the exact words, or the lines marked ERROR in the error file
Source: where the IIF file came from and when it was copiedNot a damaged file, but a QuickBooks Online question? QuickBooks Online does not import IIF files.
How Do You Make or Convert an IIF File? Export lists, edit in a spreadsheet and check the result
QuickBooks Desktop exports lists to IIF files and never transactions. To make an IIF file with transactions, write it with a tool or edit one in a spreadsheet, then check it. We used no writer, and the folders of six writer projects gave us 54 of our 85 file copies.
Making an IIF file is a different job from opening one. Intuit on creating IIF files says that making them from scratch is technically complex, and that entering data directly into QuickBooks is faster unless you have large amounts of data.
Export lists from QuickBooks Desktop
On Windows, an admin chooses File, Utilities, Export, then Lists to IIF Files. On a Mac the path is File, Export, then Lists to IIF File. Intuit on exporting lists says that only lists can be exported. Transactions can be imported and cannot be exported.
Price levels set up for items cannot be exported or imported, and payroll data can be exported only from Core, Premium and Elite Standalone Payroll. These lists matter when a file you did not write comes from an export, and it has no transactions.
Write an IIF file with a tool
Six public projects write IIF files. riif and qb_iif are Ruby tools and PumpkinSeed has a Go exporter. One Perl script converts CSV rows into IIF rows. payroll-magic converts payroll exports, and bank-formats can write IIF too. Their folders gave 54 of the 85 file copies in our study, and two parsers gave the other 31.
We used none of these writers. Their folders have fixtures, and the fixtures show what the tools expect to produce. One Perl script wrote 272 ENDTRNS rows with a letter after the keyword, and our script refused that file. Check the output of any writer before you import it.
Edit an IIF file in a spreadsheet
Intuit gives Excel steps for edits, and it names no file type for saving. LibreOffice wrote the tab separated copy with the ending .csv, so you must rename it to .iif. Use the 52 line script on the result.
soffice --headless --infilter="Text - txt - csv (StarCalc):9,34,76,1,,0,false,false" --convert-to "csv:Text - txt - csv (StarCalc):9,34,76,1,,0,false,false,false" --outdir out base.iifThe round trip kept every value in 42 of 50 damaged copies, and it trimmed trailing zeros. It changed a byte order mark, a NUL byte, an encoding and, with special number detection on, dates and signs. So spreadsheets are good editors and poor judges.
Move data into QuickBooks Online instead
QuickBooks Online does not import IIF files. An Intuit community answer names CSV, Google Sheets and XLS or XLSX files as the formats it accepts, and it points to an importer app from the App Center. We tested none of these.
How Does an IIF File Differ From QIF, QBO and CSV Files? The first line names the type
The first line names the type. An IIF file starts with a header row like !TRNS or !ACCNT. A QIF file starts with a line like !Type:Bank. A CSV file starts with names split by commas. QBO files are bank statements, and XLS files are workbooks.
All of these can be called a finance file, and QuickBooks or Quicken uses most of them. They are not interchangeable. A tool that reads one will refuse or misread another, so read the first line before you choose a tool.
| Type | How it differs |
|---|---|
| IIF |
Intuit Interchange Format. Tab separated rows in blocks, and header rows that start with !. QuickBooks Desktop imports lists and transactions with it.
|
| QIF |
Wikipedia says that Intuit made QIF, and that it is a line based text format. Its first line names the data type, like !Type:Bank, and a record ends with a caret.
|
| CSV | A list of rows with fields split by commas and a row of names first in most files. QuickBooks Online accepts CSV imports, and QuickBooks Desktop has its own Excel and CSV import kit from Intuit. |
| QBO | A QuickBooks bank statement file, per our QBO page. It is not the format you edit by hand. |
| XLS | An Excel workbook. See our XLS page. You can edit an IIF file in a spreadsheet program, but a workbook is not an IIF file. |
Intuit publishes a separate kit for Excel and CSV imports, apart from the IIF import kit. If your data starts as a spreadsheet, that kit is a second route into QuickBooks Desktop, and we tested neither.
A text file with commas is a CSV file, even with the ending .iif. Five of our 72 IIF files use commas and not tabs, and our script refused all five. A file that starts with !Type: is QIF data and not IIF.
What Is the IIF File Format Made Of? The facts we could check, and where they came from
Intuit created the format, and Intuit help pages, Wikipedia and FileInfo document it. The facts below come from those pages and from our checks of 72 IIF files and 50 damaged IIF files. Intuit does not spell out every rule, so we list what we could confirm.
| Part | What we could check |
|---|---|
| Name | Intuit Interchange Format. FileInfo files it as an Intuit data file. (FileInfo) |
| Type | ASCII text, tab separated. (Intuit overview) |
| Used by | QuickBooks Desktop, to import or export lists or transactions. (Intuit overview) |
| Header rows | Begin with an exclamation point. The first column names a keyword. (Intuit overview) |
| Named headers |
!CUST, !TRNS, !SPL, !ENDTRNS and !HDR. (Intuit overview)
|
| Accounts | Must exist in the books, or the account is created as a new bank account. (Intuit overview) |
| Export | Lists only. Transactions can be imported and not exported. (Intuit export guide) |
| Countries | Files cannot move between country versions of QuickBooks. (Intuit export guide) |
| QuickBooks Online | Does not import IIF files, per an Intuit community answer. (Intuit community answer) |
| Media types | Wikipedia lists three. We found none in the IANA lists. (Wikipedia) |
| Magic number | None that we found. The file command says ASCII text. |
| Our counts |
Of 72 IIF files, 40 use LF, 31 use CRLF and 1 mixes them. 70 start with !. 54 declare transactions.
|
About the values we list
Facts about the layout come from Intuit, Wikipedia and FileInfo, and every row names its source. The counts were checked against our IIF files. FileInfo is a directory, and its page was last updated in 2010, so we use it for the name and the programs only.
The Intuit overview page does not state rules for balanced transactions, required fields, date formats or amount formats. Our base file balances and uses month first dates because the public files do. We cannot say that QuickBooks requires either.
What we left out
We had no QuickBooks, so we imported no file and saw no error from it. We opened no file in Excel, and we used no writer. We tested no repair and no deletion, and we scanned no IIF file for malware.
Our 72 files come from 8 public test folders, and most are built to be odd. Our damaged IIF files change one thing at a time in one IIF file. A reader that fails on our IIF files can work on your IIF file, and the reverse is true as well.
Sources checked
For the format, see the Intuit IIF overview, the Intuit page on exporting, importing and editing IIF files and the Wikipedia article on Intuit Interchange Format.
For the improved import, see Intuit on the 2019 import tool. For QuickBooks Online, see the Intuit community answer. For the balance error, see the Intuit payroll help page.
For the directory entry, see the FileInfo page on IIF files. For media types, see the IANA text media types and application media types. Every count came from the files and scripts in our research folder.
Frequently Asked Questions About IIF Files
What is an IIF file?
An IIF file is a text file in the Intuit Interchange Format. QuickBooks Desktop uses it to import and export lists and transactions. Its fields are separated by tabs, and its header rows start with an exclamation mark. Of our 72 IIF files, 54 declare transactions and 18 have only lists.
How do I open an IIF file?
Open a copy in a text editor to read it. To import it, QuickBooks Desktop has File, Utilities, Import, IIF Files. We tested a script, two libraries, pandas and LibreOffice on 72 IIF files instead. We imported no file into QuickBooks.
What does IIF stand for?
It stands for Intuit Interchange Format. FileInfo gives that name and lists Intuit as the developer. Wikipedia calls it a proprietary text file for importing and exporting lists and transactions in QuickBooks.
Is an IIF file safe to open?
Reading an IIF file in a text editor is safe, because the IIF file is text. Importing one changes your books, so back up the company file first. Intuit warns that a wrong format can import wrong data with no error. We scanned no IIF file for malware.
What is inside an IIF file?
Rows of text split by tabs. Header rows start with an exclamation mark and name the fields. A transaction is a block with a TRNS row, one or more SPL rows and an ENDTRNS row. Our example, base.iif, is 745 bytes and 16 lines long.
Can I import an IIF file into QuickBooks Online?
No. An Intuit community answer from October 2023 says QuickBooks Online is unable to support importing IIF files. It names CSV, Google Sheets and XLS or XLSX files as the formats it accepts. We tested none of these imports.
How do I import an IIF file into QuickBooks Desktop?
Back up the company file, sign in as an admin and switch to single user mode. Then choose File, Utilities, Import, IIF Files, select the file and choose OK. On a Mac, choose File, Import, From IIF Files. Intuit offers no technical support for this.
Why will my IIF file not import?
Eight causes cover what we found. They are a broken first line, a cut copy, missing header rows, blocks that break, wrong separators, broken values, odd bytes and the wrong place. Read the first line of the IIF file first, then check the amounts.
Can I open an IIF file in Excel?
Intuit says to open it in Excel with the file type set to All Files and the Text Import Wizard. Excel cannot keep numbers with leading zeros, and Intuit gives a custom cell format as the workaround. We tested LibreOffice and not Excel.
How can I check if an IIF file is damaged?
Read the first line, then every header row, then add up the amounts of every block. Read the file with two tools. Three readers agreed on 35 of 49 public IIF files with transactions, and they passed only 14 of 50 damaged copies.
Can I export transactions to an IIF file?
No. Intuit says QuickBooks Desktop exports only lists to IIF files. Transactions can be imported and cannot be exported. To make an IIF file with transactions, write it with a tool or in a spreadsheet, then check it before you import.
How do I make an IIF file from a spreadsheet?
Intuit says creating IIF files from scratch is technically complex. Copy the header rows from an Intuit sample file, split the fields by tabs and end every block with ENDTRNS. We used no writer, and LibreOffice saved our tests as .csv, so rename the file to .iif.
Still Stuck With an IIF File? Six routes out, depending on what went wrong
Read the first line and add up the amounts. The cause table lists what to try in order.
The convert part shows how to export lists, edit in a spreadsheet and what to do for QuickBooks Online.
Is it a bank download? The page on QBO files covers the QuickBooks bank statement format.
Is it a spreadsheet? The page on XLS files covers Excel workbooks, which you can use to edit an IIF file.
Is it a file type you do not know? Browse the file extension resource library, or send it on the submit a file type page.
Do you need the format facts? Read the format part for the rows, the sources and what we left out.