When you first start with QuickBooks, there’s a lot of data to enter. You need to set up your list of customers, vendors, items (what you sell and buy), and employees. You may also have historical transactions you need to enter. You can always enter all of this information through the QuickBooks menus, but if you’ve been running your business for a while, you probably have all of this information in other files that you’d like to pack up and import into QuickBooks.
Well, using .iif (Intuit Interchange Format) text files you can format your information in a way that QuickBooks can understand it. Just a warning, this method may not be for you. But if you feel like tapping into your inner geek, .iif can save you a lot of time.
In this article, I’ll be talking about your lists: customer, vendors, employee, items, and other things you’ll find in the List menu. These are the building blocks QuickBooks needs to create transactions. In another article, I’ll go into transactions.
Note: If you use Address Book, you can also drag and drop contact information into QuickBooks.
When you’re ready to import your .iif file, choose File > Import > From IIF Files.
Why IIF?
Think of it this way. You’re moving from an apartment to a house. Your apartment has two bedrooms, a living room, one bath, and a shared dining room/kitchen. Your new house has three bedrooms, two baths, a separate dining room from the kitchen, a garage, a living room, a den, and a study. The movers need to know where to put what. So you box things up and label all your boxes to tell the movers where the stuff in your apartment should go in your new house.
So IIF is the boxes and the labels. You organize your data in the .iif format, then QuickBooks knows where to put your data in its new home.
Taking a look at IIF
The .iif format is just a text file with information in a specific format. If you’d like to see an example of what an .iif file looks like, just open up either of the sample files that comes with QuickBooks. Then choose File > Export > Lists to IIF File. When you open that file in your favorite text editor, it will look something like this.
The word you may be looking for is: YIKES! Don’t worry, there’s an easier way to do this.
The best way to view a .iif file is to open it in a spreadsheet program (like Microsoft Excel or iWork Numbers). That way, you’ll see the data laid out into rows and columns. If you don’t have a spreadsheet program, you can also view an export file in a word processor.
Now when you view the file, you’ll see keywords in capital letters that serve as row and column headings. The keywords that appear at the beginning of each row identify the type of information in that row. For example, the keyword CUST indicates that the row contains data about a customer on your Customer:Job list.
Rows that begin with the same keyword are grouped together. For example, all of the CUST entries from your Customer:Job list appear as a single block of rows in the file. Each block, in turn, has its own column headings that appear in capital letters in the cells of the first row in the block. The column headings identify the type of information that each column in the block contains.
The rest of this article is a reference guide for formatting your data in IIF. If you need an example, try using IIF with the sample files that come with QuickBooks. I’ve provided copies of those files here for you.
Header information (HDR)
An .iif file you’ve exported from QuickBooks always begins with a header that shows the version and release numbers of the QuickBooks software used to create the file. The keyword for the header is HDR.
If you are creating a file to import, you do not need to include a header.
PROD | Identifies the Intuit product that created the export file. |
VER | The version number of the QuickBooks software that created the export file. |
REL | The release number of the QuickBooks software that created the export file. |
IIFVER | The version number of the export file format (IIF). |
DATE | The date the export file was created. |
TIME | The time the export file was created. |
Chart of accounts (ACCNT)
An .iff file can contain complete information about your Chart of Accounts. The keyword for the chart of accounts is ACCNT.
NAME | (Required) The name of an account in your chart of accounts. If the account is a subaccount, the account’s name includes the names of the parent accounts, beginning with the highest level account. If you are creating an import file, use a colon (:) to separate subaccount names. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the chart of accounts. |
REFNUM | (Export files only) A unique number that identifies the account in the list. |
ACCNTTYPE | (Required) The type of account. If you are creating an import file, use one of the keywords below to indicate the account type:
|
OBAMOUNT | The opening balance of the account. If you are creating an import file, enter the dollar amount WITHOUT a dollar sign. |
DESC | A brief description of the account. |
SCD | The tax line assigned to the account. To specify the tax line, enter its number (tax line numbers come from the file BUSTAX.SCD). If you don’t know the correct number, leave this field blank. You can always assign the tax line later, after you import the chart of accounts into QuickBooks. |
ACCNUM | The account number of the account. |
EXTRA | Identifies an account as one of the special balance sheet accounts that QuickBooks automatically creates when the need for the account arises. If you are creating an import file, use one of these keywords to identify the account:
|
Budgets (BUD)
An .iff file can contain complete information about your budgets. The keyword for a budget is BUD.
ACCNT | (Required) The name of the account to which this budget applies. If the account is a subaccount, the account’s name includes the names of the parent accounts, beginning with the highest level account. If you are creating an import file, use a colon (:) to separate subaccount names. |
PERIOD | A keyword that identifies the time intervals within the budget. Because QuickBooks only allows one month intervals in a budget, an export file always shows the keyword MONTH. If you are creating an import file, type MONTH. |
AMOUNT | (Required) The budget amounts. Because a QuickBooks budget covers an entire year, an export file shows 12 amounts, one for each month of the year. If you are creating an import file, each month’s amount must have its own column. |
STARTDATE | The starting date of the budget. If you are creating an import file, enter the date in MM/DD/YY format. For example, 1/30/04. |
CLASS | The name of the class that the budget applies to. If the class is a subclass, the class name includes the names of the parent classes, beginning with the highest level class. If you are creating an import file, use a colon (:) to separate subclass names. |
CUSTOMER | The customer that the budget applies to. If you are applying the budget to a specific job for the customer, the customer name includes the job name. The customer’s name comes first, then a colon (:), and then the job name. |
Class list (CLASS)
An .iff file can contain the list of classes you use in QuickBooks. The keyword for the Class list is CLASS.
NAME | (Required) The name of the class. If the class is a subclass of another class, the name includes the name of the parent classes, beginning with the highest level class. If you are creating an import file, use a colon (:) to separate subclass names. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Class list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
Customer Type list (CTYPE)
An .iff file can contain the list of customer types you use in QuickBooks. The keyword for the Customer Type list is CTYPE.
NAME | (Required) The customer type. If the customer type is a subtype of another customer type, the name includes the name of the parent types, beginning with the highest level type. If you are creating an import file, use a colon (:) to separate subtype names. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Customer Type list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
Customer:Job list (CUST)
An .iff file can contain all the information on your Customer:Job list. The keyword for the Customer:Job list is CUST.
NAME | (Required) The name of the customer. If a job name is included, the customer name appears first. If you are creating an import file, use a colon (:) to separate the customer name from the job name. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Customer:Job list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
BADDR1-5 | The customer’s billing address. |
SADDR1-5 | The customer’s shipping address. |
PHONE1 | The customer’s phone number. |
PHONE2 | The customer’s alternate phone number. |
FAXNUM | The customer’s FAX number. |
CONT1 | The name of your primary contact with the customer. |
CONT2 | The name of an alternate contact with the customer. |
CTYPE | Your classification for the customer (QuickBooks calls this a “customer type”). If you import a customer type that is not on your Customer Type list, QuickBooks adds the new customer type to the list. |
TERMS | The customer’s payment terms with your company. |
TAXABLE | Indicates whether you can charge sales tax to the customer. If you are creating an import file, enter one of these keywords in the TAXABLE field:
|
LIMIT | The customer’s credit limit with your company. If you are creating an import file, enter the dollar amount. |
RESALENUM | The customer’s resale number. |
REP | The initials of the sales representative who deals with the customer. |
TAXITEM | The name of the tax item you have assigned to this customer. The name you enter must correspond to one of the sales tax items on your Item list. |
NOTEPAD | Your notes about the customer. If you are creating an import file, the notes appear in the Notepad window for the customer. |
SALUTATION | The customer’s salutation, or title (Mr., Ms., Doctor, etc.). |
COMPANYNAME | The name of the customer’s company. |
FIRSTNAME | The customer’s first name. |
MIDINIT | The customer’s middle initial. |
LASTNAME | The customer’s last name. |
CUSTFLD1…CUSTFLD15 | The custom field entries for the customer. Custom fields let you track special information about the customer, such as the customer’s birthday or email address. |
Employee list (EMP)
An .iff file can contain all the information in your Employee list. The keyword for the Employee list is EMP.
NAME | (Required) The name of the employee. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Employee list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
INIT | (Required) The employee’s initials. |
ADDR1-5 | The employee’s address. |
SSNO | The employee’s Social Security number. |
PHONE1 | The employee’s phone number. |
PHONE2 | An alternate phone number for the employee. |
NOTEPAD | Your notes about the employee. If you are creating an import file, the notes appear in the Notepad window for the employee. |
FIRSTNAME | The employee’s first name. |
MIDINIT | The employee’s middle initial. |
LASTNAME | The employee’s last name. |
SALUTATION | The employee’s salutation, or title (Mr., Ms., Doctor, etc.). |
CUSTFLD1
CUSTFLD2… CUSTFLD15 |
Custom fields let you track information such as the employee’s birthday or email address. |
Item list (INVITEM)
An .iff file can contain all the information on your Item list. The keyword for an entry in the list is INVITEM.
NAME | (Required) The name of the invoice item. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Item list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
INVITEMTYPE | (Required) Indicates the type of invoice item. If you are creating an import file, use one of these keywords to indicate the item type.
|
DESC | A description of the item as you want it to appear in the Description column on invoices, credit memos, and sales receipts. |
PURCHASEDESC | (Inventory part items only) A description of the item as you want it to appear on purchase orders. |
ACCNT | (Required) The name of the income account you use to track sales of the item. The type of this account should be INC. |
ASSETACCNT | (Inventory part items only) The name of the asset account you use to track the value of your inventory. The type of this account should be OASSET. |
COGSACCNT | (Inventory part items only) The name of the account you use to track the cost of your sales. The type of this account should be COGS. |
PRICE | (All item types except group, payment, and subtotal) The rate or price you charge for the item. If you are creating an import file, add a percent sign (%) if the amount is a percentage. |
COST | (Inventory part items only) The unit cost of the item. |
TAXABLE | (Discount, other charges, part, and service items only) Indicates whether the item is taxable. If you are creating an import file, enter one of these keywords in the TAXABLE field:
|
PAYMETH | (Payment items only) The payment method customers use (check, Visa, etc.). |
TAXVEND | (Sales tax items only) The name of the agency to which you pay sales tax. |
TAXDIST | (Sales tax items only) The name of your tax district. |
TOPRINT | (Group items only) Indicates whether QuickBooks includes a list of the items in the group when you print an invoice, credit memo, or sales receipt. If you are creating an import file, enter one of these keywords in the TOPRINT field:
|
PREFVEND | (Inventory part items only) The name of the vendor from whom you normally purchase the item. |
REORDERPOINT | (Inventory part items only) The minimum quantity you want to keep in stock at any given time. When your inventory reaches this level, QuickBooks informs you that it is time to reorder the item. |
EXTRA | Adds additional information about the invoice item. These keywords can appear in the EXTRA field:
|
CUSTFLD1
CUSTFLD2…CUSTFLD5 |
Custom fields let you track information about the item, such as color, unit or measure, or size. |
Customer Message list (INVMEMO)
An .iff file can contain the list of customer messages you use in QuickBooks. The keyword for the Customer Message list is INVMEMO.
NAME | (Required) The text of the customer message. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Customer Message list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
Other Names list (OTHERNAME)
An .iff file can contain all the information in your Other Names list. The keyword for the Other Names list is OTHERNAME.
NAME | (Required) A name or entry on the list. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Other Names list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
BADDR1-5 | The person’s address. |
PHONE1 | The person’s phone number. |
PHONE2 | The person’s alternate phone number. |
FAXNUM | The person’s FAX number. |
CONT1 | The name of your primary contact. |
NOTEPAD | Your notes about this person. If you are creating an import file, the notes appear in the Notepad window for this person. |
SALUTATION | The person’s salutation, or title (Mr., Ms., Doctor, etc.). |
COMPANYNAME | The name of the person’s company. |
FIRSTNAME | The person’s first name. |
MIDINIT | The person’s middle initial. |
LASTNAME | The person’s last name. |
Payment Method list (PAYMETH)
An .iff file can contain the list of payment methods you use in QuickBooks. The keyword for the Payment Method list is PAYMETH.
NAME | (Required) The name of the payment method. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Payment Method list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
Ship Via list (SHIPMETH)
An .iff file can contain the list of shipping methods you use in QuickBooks. The keyword for the Ship Via list is SHIPMETH.
NAME | (Required) The name of the shipping method. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Ship Via list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
Terms list (TERMS)
An .iff file can contain all the information in your Terms list. The keyword for the Terms list is TERMS.
NAME | (Required) Your name for the payment terms. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Terms list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
DUEDAYS | When TERMSTYPE = 0, the number of days in which payment is due. When TERMSTYPE =1, the day of the month by which payment is due. |
DISCPER | The discount percentage earned for early payment. If you are creating an import file, enter the percent number. |
DISCDAYS | The number of days by which an early payment can earn the discount specified by DISCPER. |
TERMSTYPE | Indicates the type of terms you are setting up. Type 0 for “standard terms” (payment to be made within a specific number of days), or type 1 for “date driven” terms (payment to be made by a certain date of the month). |
To Do list (TODO)
An .iff file can contain all the information on your To Do list. The keyword for the To Do list is TODO.
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
ISDONE | Indicates whether the task is complete. If you are creating an import file, enter one of these keywords in the ISDONE column:
|
DATE | The date you want QuickBooks to place the task on your Reminders list. |
DESC | Your notes about the task. |
Vendor list (VEND)
An .iff file can contain all the information in your Vendor list. The keyword for the Vendor list is VEND.
NAME | (Required) The name of the vendor. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Vendor list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
PRINTAS | The name you would like checks to be made out to. This field allows you to make checks out to a different name than the name that appears on your Vendor list. |
ADDR1-5 | The vendor’s address. |
VTYPE | Your classification for the vendor (QuickBooks calls this a “vendor type”). If you import a vendor type that is not on your Vendor Type list, QuickBooks adds the new vendor type to the list. |
CONT1 | The name of your primary contact with the vendor. |
PHONE1 | The vendor’s phone number. |
PHONE2 | The vendor’s alternate phone number. |
FAXNUM | The vendor’s FAX number. |
NOTE | A short note or phrase you want to associate with the vendor. QuickBooks automatically puts your note in the Memo field of checks you send to the vendor. |
TAXID | The vendor’s tax identification number. |
LIMIT | Your credit limit with the vendor. If you are creating an import file, enter the dollar amount. |
TERMS | Your payment terms with the vendor. |
NOTEPAD | Your notes about the vendor. If you are creating an import file, the notes appear in the Notepad window for the vendor. |
SALUTATION | The vendor’s salutation, or title (Mr., Ms., Doctor, etc.). |
COMPANYNAME | The name of the vendor’s company. |
FIRSTNAME | The vendor’s first name. |
MIDINIT | The vendor’s middle initial. |
LASTNAME | The vendor’s last name. |
CUSTFLD1
CUSTFLD2… CUSTFLD15 |
The custom field entries for the vendor. Custom fields let you track special information about the vendor, such as the vendor’s birthday or email address. |
1099 | Indicates whether you file a 1099-MISC form for this vendor. If you are creating an import file, enter one of these keywords in the 1099 field:
|
Vendor Type list (VTYPE)
An .iff file can contain the list of vendor types you use in QuickBooks. The keyword for the Vendor Type list is VTYPE.
NAME | (Required) The vendor type. If the vendor type is a subtype of another vendor type, the name includes the name of the parent types, beginning with the highest level type. If you are creating an import file, use a colon (:) to separate subtype names. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Vendor Type list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |
Vendor Type list (VTYPE)
An .iff file can contain the list of vendor types you use in QuickBooks. The keyword for the Vendor Type list is VTYPE.
NAME | (Required) The vendor type. If the vendor type is a subtype of another vendor type, the name includes the name of the parent types, beginning with the highest level type. If you are creating an import file, use a colon (:) to separate subtype names. |
TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Vendor Type list. |
REFNUM | (Export files only) A unique number that identifies an entry in the list. |