While QuickBooks is a very flexible software system, it probably doesn’t do everything that you need (as far as accounting and inventory control). Fortunately, you can expand the capabilities of your copy of QuickBooks by adding an “add on” or “QuickBooks compatible” program. This week I’ll talk about programs that use the IIF method of exchanging data.
In future articles I’ll talk about off-the-shelf programs that use the QuickBooks SDK, the Intuit Partner Platform, and then talk about custom software.
This article was updated on 2/19/2010 and again on 2/28/2014
Communicating with QuickBooks Data
QuickBooks uses a “closed” database. That is, it is locked down so that people outside of Intuit cannot directly access the company data. In a general sense this is good – you don’t want just anyone to be able to get in and mess around with things. You have information that should not be easily accessible without some sort of control: Client credit card information, employee payroll information, your own company financial information. You also don’t want to make it too easy for people to get to the data and cause problems – both in changing the data as well as updating things incorrectly.
On the other hand, QuickBooks doesn’t always provide you with the reports you need, or allow you to update the data the way you need. So if it is totally locked out, it interferes with your ability to run your business with this data.
So, to resolve this, Intuit provides several different ways of getting data in and out in a controlled and somewhat secure manner.
- You have Excel imports and exports in various places. I’ve talked about the Excel method in my article on Importing Inventory with Excel. This isn’t a programming method that you would use with an add on program so I won’t go into that further.
- There is the export and import features in the File/Utilities menu, which uses the IIF file format that I’m discussing in this article.
- A more advanced method is the QuickBooks SDK, which is a programming interface.
- The Intuit Partner Platform is another, newer programming interface.
What Is IIF?
IIF stands for Intuit Interchange Format. This method of integrating information with QuickBooks has you exchanging highly formatted text files with QuickBooks through the File/Utilities menu.
Here is a sample of what one of these files might look like:
This is an old format for working with QuickBooks. I’m not sure how long this has been around, but Intuit stopped adding significant features somewhere around 2002 (I haven’t pinned down the dates yet). There have been some minor updates since that time, but you can’t expect new features to be added.
What Kinds of Data?
QuickBooks has three general kinds of data: List information, Transaction information, and company/preference information.
You can import and export the following lists with IIF:
Note that there are some restrictions in these lists. Some lists won’t contain all of the record types, for example. The price level list will include just the fixed % lists, and not the per item lists. Some lists won’t contain all of the fields that the record would normally contain. The item list won’t contain the field for the manufacturer’s part number for example.
Why these omissions? Because Intuit stopped doing significant development of the IIF format years ago, before all of these features were implemented in QuickBooks. Since that time they have focused on other data exchange methods, such as the QuickBooks SDK.
Transactions are very limited, in that you can import transactions but you cannot export transactions with IIF. You can import the following transaction types:
Limitations of IIF
IIF has the following limitations that you must be aware of:
- You must be in single user mode to import or export data.
- You must be logged in as the Admin user or the External Accountant user to import or export data.
- Transactions cannot be exported (as described above).
- IIF transactions that are imported will not create links between transactions. Such as – an invoice and a payment to the invoice, when imported, won’t be linked in the QB data.
- Error checking is very minimal and error messages may be cryptic.
- IIF won’t work with transaction types, or fields in records, that have been added to QuickBooks since around 2002 or so.
Another issue that you have to be aware of is that IIF isn’t always the most reliable method of importing data. On occasion, the data may not import correctly and your company file could possibly be damaged. You should always back up your company file BEFORE importing IIF data. Of course, I say that with ANY import of data, since imports don’t have a mechanism that let them be rolled back if you don’t like the results (other than by restoring your backup).
Why Use IIF?
With all of these restrictions, why would you want to work with IIF? Here are a couple of thoughts:
- It is a built in feature of QuickBooks, so you don’t have to buy any additional software (although I recommend that you do, as I’ll mention later).
- Since you are importing from a file, you can create the file in another computer that doesn’t have QuickBooks installed, and just transfer the file to your QuickBooks location.
- There are some applications that have been around for ages that use QuickBooks, and you want to keep using what you have set up already.
- It is a simple way to transfer list information from one company file to another.
Programs That Use IIF
Although IIF is just an import file format, there are a number of programs that can be used to create IIF files for you. Because IIF is fairly cryptic, I recommend that you purchase a utility program to handle the chores for you, rather than try to learn all of the details yourself.
Unfortunately, finding those kinds of programs can be tough. Intuit does not keep a list of IIF programs. You won’t find them in the normal Intuit integration web sites such as the Intuit Marketplace because Intuit is pushing people to use the more advanced programming interfaces.
In general, you will find that IIF based utility programs (which format your data so that you can import them with the IIF utility) have a lower cost than similar tools that use more advanced import methods.
One of my favorite examples of a great IIF tool is the IIF Transaction Creator by Big Red Consulting. I’ve used this tool on several occasions with great success. It takes your Excel file and converts it to the IIF format, saving you the effort of trying to decipher the complex requirements of an IIF file. If you need to work with IIF files for any reason, this tool will save you a considerable amount of trouble. I recommend it highly.
Other than this import tool, however, I generally do not recommend using a program that uses IIF import. Using IIF is not a “best practice”.
Learning More About IIF
If you want to work with IIF Intuit has information at support article HOW12778.
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70’s, focusing on inventory and accounting software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor. Look for Charlie’s articles in the Accountex Report blog, as well as his California Wildflower Hikes blog.
Sites That Link to this Post
- Importing QuickBooks Transactions with the Transaction Pro Importer : QuickBooks and Beyond | February 22, 2012
- Evaluating QuickBooks Integration Applications : QuickBooks and Beyond | May 21, 2012
- Connecting the Pieces with Transaction Pro Exporter : QuickBooks and Beyond | July 11, 2012
- Importing Sales Tax Rates into QuickBooks : QuickBooks and Beyond | December 13, 2012
- In Search of the Perfect QuickBooks Shopping Cart Integration | QuickBooks and Beyond | February 12, 2014
- Integrating FreshBooks with QuickBooks | QuickBooks and Beyond | March 3, 2014