Your Answer
print

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.

When creating an IIF file for a bill, the bill must be item-based. That is, SPL entries in the IIF file must specify the item name in the INVITEM column. If you use an expense account, sales tax amounts will be posted as an uncategorized amount, since no sales tax item is associated with them.
 
  • 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 TRNSTYPE DATE ACCNT NAME AMOUNT CLEAR TOPRINT DUEDATE TERMS
!SPL TRNSTYPE DATE ACCNT NAME AMOUNT CLEAR QNTY INVITEM EXTRA
!ENDTRNS                  
TRNS BILL 7/16/2008 Accounts Payable ItemVendor -105 N N 8/15/2008 Net 30
SPL BILL 7/16/2008 Inventory Asset   100 N 1 PurchItem  
SPL BILL 7/16/2008 GST/HST Payable Receiver General 5 N 1 GST (ITC) AUTOSTAX
ENDTRNS                  

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".

The following items were incorrectly specified within the description of Transactions (TRNS and SPL) IIF files in the in-product help. If your IIF file does not import correctly, check the following:
 
  • 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.

The IIF file illustrated above can also be imported into QuickBooks 2007. However, in QuickBooks 2007, sales taxes on bills show up as a negative tax on sales, rather than a positive tax on purchases. This can be fixed by editing the general journal entry for the transaction.
 

Addition help:  Importing or exporting data with IIF files




Bookmark and Share

 

2014 Ontario Personal Tax Increase
Handling the 2014 personal tax increase for Ontario in QuickBooks

Error loading type library/DLL Error Code: 80029c4a
How to fix error 80029c4a when opening QuickBooks

Updating QuickBooks
How to update QuickBooks

Install QuickBooks on a network or multi-user environment
Multi-user setup after the upgrade

Payroll Year-End Checklist
Complete these year-end activities

QuickBooks Install Centre
Read step-by-step instructions

Product updates
Download the latest updates

Password Reset Tool
Password issues? Reset it with our new FREE tool.

Connection Diagnostic Tool
Helps troubleshoot network & multi-user errors

Customized templates
Download templates for QuickBooks 2008 and later

Manuals
Download manuals and guides

Tutorials & webinars
Watch our tutorials & webinars

TD1 basic amounts
View the latest TD1 amts

CPP and EI contribution limits
View latest CPP and EI limits

Did this article help you?
Your Feedback
Cancel Submit