Sometimes, the best way to integrate to applications is to give up on automatic interfacing and just dump out some common data that can be imported/exported as needed. Recently, an application I was working on reached this point with the QuickBooks API. So, I implemented the relevant exports from the primary application to IIF (Intuit Import Format) and I thought I would go ahead and post some notes on using it. IIF is a tab (hard tabs, not spaces) delimited format that is, in its evil heart, a hybrid approach between EDI and CSV.

An IIF file basically comes down to two components, repeated endlessly (well, almost; the QuickBooks KB has some notes on the process and gives the transaction limit as being at 10,000):

  1. A specification section (which, as its ad-hoc name implies, specifies the format to be used)
  2. A data section

In the specification section, we basically tell the importer what format is going to be used for the transaction. It is really just a list of fields and in which order they will occur. The data section, on the other hand, follows the template specified by the specification section, providing data in the order in which it was specified. So, to take apart one of the examples:

!TRNS    TRNSID    TRNSTYPE    DATE    ACCNT    NAME    CLASS    AMOUNT    DOCNUM    MEMO    CLEAR    TOPRINT    ADDR5    DUEDATE    TERMS
!SPL    SPLID    TRNSTYPE    DATE    ACCNT    NAME    CLASS    AMOUNT    DOCNUM    MEMO    CLEAR    QNTY    REIMBEXP    SERVICEDATE    OTHER2
!ENDTRNS
TRNS        BILL    7/16/98    Accounts Payable    Bayshore Water        -59.25            N    N        8/15/98    Net 30
SPL        BILL    7/16/98    Utilities:Water            59.25            N        NOTHING    0/0/0
ENDTRNS

For emphasis sake: this is a hard tab delimited format, so the spacing shown by this page is a little deceptive. Using C-style escapes (\t for tab, in case you were wondering), the file looks like:

!TRNS\tTRNSID\tTRNSTYPE\tDATE\tACCNT\tNAME\tCLASS\tAMOUNT\tDOCNUM\tMEMO\tCLEAR\tTOPRINT\tADDR5\tDUEDATE\tTERMS
!SPL\tSPLID\tTRNSTYPE\tDATE\tACCNT\tNAME\tCLASS\tAMOUNT\tDOCNUM\tMEMO\tCLEAR\tQNTY\tREIMBEXP\tSERVICEDATE\tOTHER2
!ENDTRNS\t\t\t\t\t\t\t\t\t\t\t\t\t\t
TRNS\t\tBILL\t7/16/98\tAccounts Payable\tBayshore Water\t\t-59.25\t\t\tN\tN\t\t8/15/98\tNet 30
SPL\t\tBILL\t7/16/98\tUtilities:Water\t\t\t59.25\t\t\tN\t\tNOTHING\t0/0/0\t
ENDTRNS\t\t\t\t\t\t\t\t\t\t\t\t\t\t

Which is a lot denser, but also a lot more precise. Any line beginning with an exclamation point (!) is one of the specification lines.

!TRNS    TRNSID    TRNSTYPE    DATE    ACCNT    NAME    CLASS    AMOUNT    DOCNUM    MEMO    CLEAR    TOPRINT    ADDR5    DUEDATE    TERMS

So, this line is specifying that the data to be imported is a transaction (which includes bills, invoices, and several other items), which has the following fields in order: transaction ID (I've never used this in my imports; your mileage may vary, though), transaction type (BILL in the case above), date (which will be the bill date here; in other places it has slightly different meanings; check the docs), account (AP), name (name of the entity sending the bill; more on this in a moment), class, the total amount of the bill, document number (reference number, for a bill), memo, whether or not it has cleared, whether or not this bill needs to be printed, an address, due date and terms.

A note on names: these names must match exactly what QuickBooks has on file. If it does not, the IIF importer will create the value automatically. So, if you want to import a bill from "Somecorp", but type it in "Somecorp, Ltd." a new vendor "Somecorp, Ltd." will be created with the bill. This applies to all name-based items in the file, making the IIF import a little tricky and fairly dangerous. Many entities in QuickBooks are hierarchichal, so if you want, for example, a class of "bar" which is a subclass of "foo", you would specify it as "foo:bar". Excluding quotation marks, with the colon, and no spaces between the colon and either "foo" or "bar".

The source listed below links a zip file with information on the IIF format. It is sparse, but enough to get going. It has some example IIF files (including the one dissected above) and some HTML files specifying which fields are available and/or required for each type of data to import. It is also important to realize that IIF imports are officially deprecated, so be aware of this when writing your own importer/exporter.

Sources