How do I import transaction data into QuickBooks 2008 and later using IIF files?
Caution: Make sure all accounts in your IIF file are created in QuickBooks prior to importing. If you import a transaction with an account that is not already created in the QuickBooks data file, that account will be created as a new bank account.
As of QuickBooks 2008, QuickBooks uses a new database format and a new sales tax system. These allow for several benefits, including greater flexibility in your company setup and more efficient access to the records in your company file.
With current and previous versions of QuickBooks, you can create transaction data in an IIF file (typically with a spreadsheet application), then import that data into your company file. However, because these data fields are coded differently from previous versions of QuickBooks, there are significant differences in how sales tax information is processed from imported IIF files.
In previous versions of QuickBooks, sales tax codes could be assigned to each SPL line item in the IIF file for an exported transaction.
In QuickBooks 2008, sales tax codes assigned to line items in this fashion will not be imported. Instead, the IIF file must include sales tax items as separate line items in the body of the transaction.
That is, in the IIF file for a transaction in QuickBooks 2008:
First, list all items that are being bought or sold on their own SPL lines.
Then, list the sales tax items on additional SPL lines, identified with AUTOSTAX in the EXTRA column. You do not assign tax to accounts any more: you instead assign it to sales tax items.
Make sure you correctly specify the NAME (sales tax agency vendor name), ACCNT (account name), and INVITEM (item name) for the sales tax item. These must exactly match existing entries in your company file. For example, you must have an account with the name of ACCNT, and a sales tax item with the name in INVITEM.
As long as columns for sales tax item SPL lines are correctly indicated — and the EXTRA column has AUTOSTAX — the sales tax amounts will be correctly imported. The amounts posted to the sales tax items will carry through to all reports and the File Sales Tax window, and will not result in uncategorized transactions.
For example, the following IIF file (illustrated as a table) is for a simple bill for an item named "PurchItem", bought for $100 with $5 GST from a vendor named "ItemVendor":
|TRNS||BILL||7/16/2008||Accounts Payable||ItemVendor||-105||N||N||8/15/2008||Net 30|
|SPL||BILL||7/16/2008||GST/HST Payable||Receiver General||5||N||1||GST (ITC)||AUTOSTAX|
When creating an IIF file from scratch, you would typically create a table like the above in a spreadsheet application, save the spreadsheet as TAB-delimited text file, and rename the file to have an .IIF file name extension. For more details on creating an IIF file for importing transactions, search the in-product help for the "Creating an import file".
Dates (such as in the DUEDATE and DATE columns) use the format MM/DD/YYYY, not DD/MM/YYYY.
When entering the EXTRA column in an SPL line, using AUTOSTAX identifies the item as a sales tax item.