QuickBooks IIF Files

QuickBooks IIF Files
Transactions
(Learn and Work Smarter not Harder)

November 29, 2011

 

In the previous articles we discussed some basis of the IIF files, some ways of exporting and importing lists (i.e. customer, vendors, items, etc) but now transactions (i.e. Invoices, Sales Receipts, Bills, Payments, Checks, etc.).

 

 

You CANNOT export IIF files from QuickBooks Online.  You cannot natively import IIF files into QuickBooks Online but you can with our IIFImporter.

 

You should know what the ONLY way to import transactions into QuickBooks natively (without a 3^rd party program) is with IIF, web connect file or bank download (QuickBookso) file.  Normal xls or csv files will NOT import transactions. You can use our TransImporter for that.

 

Each transaction type will have a slightly different format.  We cannot go into all the different types and formats but just the basics.  Please check with the QuickBooks Help, Reference Guide to Importing for more information.

 

 

 

A transaction will consist of 3 different row types.

TRNS for the transaction header row.  A !TRNS row would indicate what columns are for what fields i.e.

!TRNS,TRNSTYPE,NAME,AMOUNT

The SPL would describe the SPLIT of the transaction into item or account lines. There would be as many SPL rows as different items on an transactions.

It would end with an ENDTRNS.

 

Note that IIF files can be either tab delimited or comma delimited. But since tabs cannot be shown commas are used here.

 

So you would see a set of definition (!) rows with the data rows underneath.

!TRNS,TRNSTYPE,NAME,DATE,AMOUNT,…

!SPL,TRNSTYPE,NAME,DATE,AMOUNT,INVITEM,QNTY, PRICE,

!ENDTRNS (optional)

 

Would indicated that the transaction type is column B or 2^nd column, the NAME field is column C, etc.

The actual data for the transaction header would be in a TRNS record.

 

TRNS,INVOICE,JOHN SMITH,12/31/2011,100.00,…

SPL,INVOICE,JOHN SMITH,12/31/2011,-20,-1,20,…

SPL,INVOICE,JOHN SMITH,12/31/2011,-80,-4,20,…

ENDTRNS (required)

 

Notes:

1. Different transaction types have different rules.  i.e. the TRNS AMOUNT for an INVOICE must be positive but for a BILL negative.  This is based on accounting practices and the ACCOUNTS associated with the transaction.
2. the transaction must balance.
3. All the amount fields must be equal to 0.
4. SPL AMOUNT = PRICE (or COST) x QNTY
5. QNTY (quantity) is usually negative on sales transactions (INVOICE, CASH SALE, etc.)
6. The accounts used must be of the correct type.
7. Names for customer, vendors, items, accounts, etc. if do not exist in QuickBooks will be created without notice or any logging.  Items are created as SERVICE items and ACCOUNTS as BANKS. This may not be what you want.  It is easy to get your QuickBooks all messed but with similar but different names i.e. Item123 vs Item 123 (space).
8. NOT all transaction types are supported by QuickBooks native iif (but most are with Our IIFImporter).
9. Customer Payments (i.e. RECEIVE PAYMENTS) are NOT posted to any INVOICES but are left as just unapplied (dangling) credits for that customer.

Our IIFImporter imports IIF Files into both QuickBooks desktop and QuickBooks online and has many features that you will probably like. Such as error logs, replacement options, not requiring either Admin or Single user mode and optionally not always creating names (optionally we can).  A log is created of all activity.

 

We will talk more on this in future article.