QuickBooks Add On Programs: IIF

| February 16, 2010 | 30 Comments

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:

image Once you have created a file in the proper format, you can import the information to QuickBooks via the File/Utilities menu.

image Extracting information to IIF is done through the export menu in the same location.

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:

imageNote 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:

image My thanks to Steuart Smith of Big Red Consulting for the updated listing.

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.

 

Tags: ,

Category: Import/Export

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.

Comments (30)

Trackback URL | Comments RSS Feed

  1. Laura Dion says:

    Excellent article, Charlie!! Thanks for sharing.

  2. Dave Diedrich says:

    Great article. I’m trying to import budgets from excel. Is there a good tool that will help me set up proper budgets for import? I have about 30 classes that I’d like to import, so a lot of data. Thanks, Dave

  3. Laura Dion says:

    I would start a budget in QuickBooks and, then, export it. Once it is opened in excel, you will have the desired format to create and import your budgets.

  4. Jim Bovard says:

    Hello Charlie,
    I was wondering if you know way to print a UCC-128 Barcode shipping label from inside QB with a third party add-in. We currently use a third party add-in called ShipRush Pro to print our UPS/FEDEX barcode labels and we love it.

    Our problems is we have a new national retail customer that requires us to put a UCC-128 BarCoded Label on their pallets. I can buy label software off the shelf and get it done,but would prefer one that is integrated to QB that would pull the data from the Invoice (PO, ACCT #, SKU, QTY, etc..).

    I realize a UCC-128 label is usually printed from with-in an EDI system, which we have, but this customer does not have enough PO annually to justify the $1,000-$2,000 expense to put them on EDI and our current EDI software will not print just a label. It need an inbound EDI PO.

    We are looking at products like Bartnder & WASP, but aren’t integrated to QB.
    Hope you might have an idea.

    Jim Bovard
    Sport Seats Int’l

  5. Charlie says:

    Jim, contact me directly about this, using the email address that is in the “about this blog” link at the top of the page. I have a bar code program that integrates with QuickBooks and can print labels from invoices. It supports EAN128, which I understand to be the same as UCC128. It isn’t an off-the-shelf program yet as there are some things that I need to refine, but I can provide you with a demo so you can see if it would fit the bill. The cost is low.

  6. Eddard Stark says:

    Don’t call intuit and ask how to modify IIF’s to import transactions, they do not support it if it’s a transaction. If it is list data, yes, transactions, no.

  7. Charlie says:

    Intuit generally would like IIF to go away, but have it there only for compatibility issues. They want you to use a tool that uses the SDK, or something related to that. I also don’t recommend that you use IIF as it has poor error checking, and it generally doesn’t support any feature that has been added since 2005.

    Having said that, I find it interesting that some Intuit services are still using IIF.

    Also, the IIF feature is built in and has no cost involved. The SDK is free, but very technical to use, so you pretty much need to purchase a tool.

    I avoid IIF except in very rare situations, myself.

  8. Melissa says:

    Charlie, I have QB Pro 2010 and Mitchell 1 automotive software. Mitchell caused QB to crash and they were unable to fix the issue and MADE me create a new company within QB and start all over again with my transactions. How or can I download all of the payable, payroll, banking and other non A/R related transactions from old QB company to the NEW QB company easily? If i use the export IIF, I only get the lists not the actual transactions. Help!

  9. shelly says:

    I have posted this question twice on the Intuit’s community website but no answer. Could it be that the charge that I imported is not linked to the “unbilled Cost by Job Report?”

    Unbilled item imported through IIF not showing up in the Unbilled Costs by Job rep

    I created an IIF import file to import billable items however, when I go to the Unbilled Costs by Job report, the unbilled item is not recorded. To see a few screen shots of what I am facing you can click on the zoho link at :

    http://viewer.zoho.com/docs/pLabcca.

    On 12/01/10 I entered $250.00 the conventional way by clicking on banking, and entering the credit card charge manually. That transaction showed up on the Unbilled Costs by Job report.
    However when I entered the $68.17 using the IIF import, it does not show up on the Unbilled Costs by Job report , Is something wrong with my IIF file heading or is the column missing something?
    Thanks

  10. Charlie says:

    Shelly, sorry, I avoid working with IIF imports, so I don’t know the answer.

  11. Darwin says:

    Hello, Charlie – I am having trouble finding a list of valid “TRNSTYPE” values to programatically write an IIF file. You have a table of transaction types on this page, but for instance, my understanding is that “CASH SALE” is a valid TRNSTYPE, but I don’t see it on your list. I would like my customers to be able to select the transaction type from a dropdown list prior to generating the .iif file from my software. I’ve googled and even searched the Intuit site for “TRNSTYPE”, with no success. Can you please point me in the right direction?

    • Charlie says:

      Darwin, I avoid working with IIF files whenever possible, because of the great likelihood that an IIF import will corrupt the QB file. So I’m not an expert on that. Note that IIF doesn’t support all of the transaction types that are found in QuickBooks, since it is no longer actively being developed by Intuit.

      The best source for documentation on IIF is found at http://support.quickbooks.intuit.com/support/Articles/HOW12778, where you can download the IIF Import Kit. That is Intuit’s set of documentation.

  12. Chris Lawing says:

    Thanks for all of the great info. I am a little new to some of the details of doing these things in QBs but have done a ton in other software.

    I would like to download all credit card spending transactions and get them into QB as transactions. It sounds like using one of the apps (not IFF) such as Big Red Consulting would be the best way to accomplish this. I am going to delve in and try to figure it all out. Any pointers, warnings, suggestions you have would be most appreciated.

    Regards,
    Chris-Charlotte NC

    • Charlie says:

      That would depend on what version of QuickBooks you are using, and which credit card company.

      Some credit card companies provide a Web Connector (QBO)file that can be imported into QuickBooks.

      You might also look at tools from Baystate Consulting and Zachary Systems

  13. Jessica says:

    Hi Charlie,
    I am using the 2012 version of Quickbooks and it has the ability to generate reports in Excel and integrate the reports with quickbooks. Here is what I know: Run a report in QB, export it to Excel, save the Excel file. Here is my problem: I run the same report once a week and I have 8 different companies in my QB that require the same report weekly. Quickbooks 12 has an integration feature that is supposed to work with Excel and let the user update the excel file without even opening QB. I cannot get this function to work properly. It always gives me an error message about needing the INTUIT EXCEL ADDIN. For some reason this addin is listed for some of my 8 companies, but not all. (It is under Preferences, Integrated Applications, Company). How do I get it on that list if it isn’t already there? I see no way of adding it to the list and I cannot find any articles online to help. Can you offer any assistance?

    • Charlie says:

      Jessica, offhand I don’t have an answer. I haven’t had my hands on a system that had this problem. I’m not sure when exactly that addin is installed. QuickBooks tech support should be able to help you – or better yet, get in touch with an Advanced certified QuickBooks ProAdvisor to get help – they have access to a higher level of support from Intuit.

  14. Carrie says:

    Hi Charlie,

    We are using Quickbooks Pro and Mitchell Ondemand 5 and are having to imput all info twice (very time consuming) What is the best way to intergrate these two programs? Will they both update on a server? Can QB data for checking and employee information be protected?

    I read about a Mitchell intergrator product but can’t find any info from anyone who has actually used this product to get pros and cons. Your recommmendations.

    Carrie

  15. Marianne says:

    Any idea why QuickBooks Pro 2010 won’t recognize Credit Card Charge as a TRNSTYPE? I have tried entering it all sorts of ways (ie. with spaces, w/o spaces, with the word Charge, w/o it, etc.) and it always comes through as a GENERAL JOURNAL.

    • Marianne says:

      Charlie, no need to answer, I read another one of your post above and see that you avoid IIF files.

    • Charlie says:

      I haven’t looked up the specs to be sure, but I will guess that this is not one of the supported transaction types. IIF was frozen many years ago, so many transaction types are not supported

  16. Eric says:

    Hi there,

    I am trying to import customer data via iif into quickbooks and my challenge is having the correct currency for that customer appear. multi-currency is on in QB and the currency is listed on the IIF file. Any suggestions?

    • Charlie says:

      Eric, I can’t help with that. I haven’t worked with multiple currencies and IIF – I avoid IIF as much as possible. If you set up a customer with a different currency manually, through the user interface, and then export the customer list TO IIF, do you see the currency designation there? If not, it isn’t supported (which wouldn’t surprise me).

Leave a Reply