Importing Assemblies via an Excel-based iiF Spreadsheet

by

While you might purchase one of several different importer programs that enable you to import assemblies, you can’t otherwise directly import an Excel spreadsheet containing assemblies into QuickBooks.

Intuit Interchange Format (iiF) files are ASCII text, TSV (tab-separated value) files. This means the files are a special ‘multi-line’ form of text file. The QuickBooks iiF file format has been around a long time.

It essentially was the first way to export data from, and import. data into QuickBooks files. Intuit still relies on this format for many purposes, including data services support.

While Intuit says,“creating iiF files from scratch is technically complex and may be difficult if you don't have experience formatting delimited text files,” the reality is that you can, with some simple format instructions, create an Excel-based iiF spreadsheet. This will allow you import even inventory assembly items, with their bills-of-material into QuickBooks.

Let me also mention that Intuit specifies in their IIF Import Manual (last updated 9/30/2008) that you cannot import Assembly Item Builds using iiF files. I'm here to tell you that is entirely wrong. You can import Assemblies with their Bills-of-material.

Formatting your Spreadsheet for Assembly Items

The thing to remember about iiF files is that the format and structure of the files is key to a successful import. Without maintaining these essentials, you can easily find your import ‘errors out,’ or even worse, corrupts your data.

That's why you always perform a backup of your QuickBooks Company-file prior to importing any data, especially iiF formatted files.

QuickBooks iiF files can support list or transaction information. Key fields within the file tell QuickBooks which type of data is included in the import file.

Let’s start by looking at a single Assembly Item laid-out in iiF format within an Excel spreadsheet:

Now I realize this is pretty small. That’s why we will look at this same example several times, examining smaller sections of the overall format one section at a time.

What I want you to notice is that the data is laid out in columns A through AE. That’s a total of 31 columns of data to capture all the possible information we need to import.

Now, it isn’t always necessary to include data in all of these columns, but there are several columns that are "required." We will go over those as we review the various sections of our import spreadsheet.

In the case of either lists or transactions, the actual header section of the file tells QuickBooks what the file is and which version of QuickBooks the file (came from, or) is intended for. The keyword for the header is HDR:

Let's narrow our view of view down a little closer:

You can see that the File Header, listed on Row 1 of our spreadsheet contains the column names for the header information listed on Row 2 of our spreadsheet. In this case, the PROD (short for product) is QuickBooks Enterprise, the VER (short for version) is Version 24.0D.

An exported iiF file always begins with a header like the one illustrated above that shows the version and release numbers of the QuickBooks software used to create the file. When you are creating a file to import, this header is optional. It's not required.

Personally, I always include the header information to insure that I'm posting the data into the correct QuickBooks flavor and version.

Now, when it comes to Assembly Items, we're talking about "list information." Of course, the list that contains assemblies is the item list.

For purposes of iiF files, the item list is known as the INVITEM (short for inventory item) list. This nomenclature (aka: keyword) will be used in various forms, always appearing in spreadsheet Column A, which is the column containing all header keys.

ENDASSEMBLY is the header key associated with assembly items. We also will use this header key within Column A.

In a list import, the header keys always begin with an exclamation point “ ! ”, so you'll see them written like !INVITEM or !ENDASSEMBLY. In most cases, the spreadsheet rows containing these header keys also will contain the appropriate column headings associated with the list type.

Because assemblies are made up of many items that are all considered one, the header key placement is critical to get them right.

Let’s look at an example of how a small assembly might look – in terms of the header keys only:

In this example, our assembly (called a Dongle) has just two components in the Bill-of-materials, Labor (items) and Utilities (items). There is a QNTY (quantity) of four of each of them.

With this formatting understood, we now can return to our earlier example:

The columns required for Assembly items are the NAME, INVITEMTYPE, ACCNT, ASSETACCNT, COGSACCNT and TAXABLE (this field is only required if sales tax is turned on in the Company file into which the assembly is being imported).

We have changed our example in the image below to include these fields in red. I recommend you also consider importing the fields shown in orange, even though they are not required:

The INVITEMTYPE field indicates the type of item list item. It is a required import field. In defining the Assembly Item, the INVITEMTYPE is ASSEMBLY.

You must use the appropriate nomenclature in formatting this field. The following table summarizes the nomenclature keywords and the item types:

In defining the Bill-of-material components of the Assembly Item, the NAME, INVITEMTYPE, and QNTY (quantity) fields are required:

You will see in our example we have SERV (service), OTHC (other charge), PART (non-inventory part), and INVENTORY (inventory) items. These fields are highlighted in green in the above example.

There are a few important limitations I want to mention here. First, when importing assembly items, all related data must already exist in your file. For example, you need your accounts and component items already in the file.

Second, you cannot import item types that are not supported by assemblies within QuickBooks. This means you cannot include Group-items in an assembly you import.

Third, if you're importing Service, Other-charge or Non-inventory-part items, they must be set-up as two-sided (passthru) items with an expense account. You will need to post them as “Y” (yes) in the PASSTHRU field as shown below:

Four, you can include other assembly items as sub-assembly components in your Bill of Materials, but you must insure those items already have been created in the file. In other words, if you're importing them, import the lowest level of assemblies before importing the finished good (parent) assemblies.

Saving and Importing your Spreadsheet

When it comes time to saving your Excel spreadsheet, you're not going to save it as an Excel file. Rather, you must save it as a Text (Tab delimited) (*.txt) file:

Typically, Excel will display the following informational message, advising you that some features in your spreadsheet might be lost if you save it as Text (Tab delimited). It will ask if you want to keep using that format:

You want to make certain you respond, Yes.

Once you have saved your IIF file, it's time to import it into Quickbooks, follow these steps:

  1. Open Quickbooks
  2. Back-up your Company file: File > Backup Company > Local backup > Finish
  3. Prepare for Import: File > Utilities > Import > IIF Files
  4. The Import window opens, navigate to and select the IIF file that you saved
  5. Click Open
  6. When QuickBooks displays the message, "Your data has been imported successfully," click OK

As they say, “the proof is in the puddin’,” so here is the imported Assembly in QuickBooks:

When we open the "full view" of the Bill of Materials, you can see that all of our components were imported properly:

So, now you have an option. When it comes to importing assemblies into QuickBooks (Desktop), you can buy an importer program or do-it-yourself, like many of us did long before there were importers on the market.

DISCLAIMER: This article is for informational purposes only and does not constitute an all inclusive guide to use of Intuit Interchange Format (iiF) files or the formatting of such files. Insightful Accountant accepts no responsibility or liability for any work performed using the material outlined in this article. Remember, always back-up your QuickBooks Company file before importing any data, from any source, or in any format. Always verify your data after import and restore your file using the pre-import back-up in the event you experience unexpected results or data irregularities.