QB Issue Resolution:
If you’ve been using another financial software product, and you’d like to import information from that product into QuickBooks, you can do so if the product allows you to export to a spreadsheet or text file.
This topic describes how to format information in a spreadsheet and save it as an import (IIF) file that QuickBooks can read. Using an IIF file, you can import information for your QuickBooks lists, budgets, and transactions. When formatting the IIF files, you may want to refer to the Reference Guide to Import Files.
If you already have a correctly formatted IIF file, go directly to step 10 below.
The easiest way to see and understand the format QuickBooks needs to import data is to export some of your QuickBooks lists to IIF files and view the resulting file in a spreadsheet. If you don’t already have a QuickBooks company, you can export lists from the sample data (sample_product-based business.qbw or sample_service-based business.qbw).
- Open the spreadsheet that contains the data.
- Move the contents of all the cells in your spreadsheet one column to the right so that the first column is blank.
- Move the contents of all the cells down by one row so that the first row is blank.
- Check the structure of the spreadsheet. If it contains more than one type of list—or a mixture of lists, budgets, and transactions—make sure that each type of information has its own block in the spreadsheet. The blocks should follow each other vertically. Insert a blank row of cells to separate the blocks.
For example, if you have a list of customers and a list of vendors, all the customer data should be in one block and all the vendor information should be in another block.
- In the first cell in the blank row above each block of information, enter one of the keywords shown in this table. Be sure to include the exclamation point (!).
Type this text… If the block contains… !ACCNT Details about your chart of accounts. !CUST A customer address or phone list. !VEND A vendor address or phone list. !EMP A list of employees. !OTHERNAME A list of names you’d like to add to QuickBooks Other Name list. !BUD Budget details. !CLASS A list of general classifications you’d like to add to QuickBooks Class list. !CTYPE A list of customer classifications you’d like to add to QuickBooks Customer Type list. !INVITEM Details about the line items you use on sales and purchase forms. !INVMEMO Messages you’d like to add to QuickBooks Customer Message list. !PAYMETH A list of payment methods you’d like to add to QuickBooks Payment Method list. !SHIPMETH A list of shipping methods you’d like to add to QuickBooks Ship Via list. !TERMS A list of payment terms you’d like to add to QuickBooks Terms list. !TIMEACT Details about activities you timed with the QuickBooks Timer. Works with !TIMERHDR. !TIMERHDR QuickBooks Timer data. !TODO A list of upcoming “to do” tasks you want QuickBooks to remind you about. !TRNS Transactions. !VTYPE A list of vendor classifications you’d like to add to QuickBooks Vendor Type list.
For example, if the spreadsheet contains a list of customers and a list of vendors, the structure of the spreadsheet would be as follows:
42 [List of customers]
44 [List of vendors]
- Fill in the remaining cells of the first column with the keyword you entered in the first row for each informational block. This time, omit the exclamation point.
In our example, the first column would look like this:
- Complete the first row for each list by adding the headings that QuickBooks expects to find for that type of information. To see a list of the headings you can use, click the type of information you are importing:
Chart of accounts Payment Method list Budgets Ship Via list Class list Terms list Customer Type list To Do list Customer:Job list Vendor list Employee list Vendor Type list Item list Transactions Customer Message list Distribution lines in transactions Other Names list Activities timed with the QuickBooks Timer
For example, the first row for a list of vendor names, addresses, and phone numbers would look like this:
30 !VEND NAME ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 PHONE1 PHONE2
- Make sure that the content of the columns in each block matches the headings you just added:
- If you have a vendor’s entire address in one column, you need to break it up so that the first line of the address appears under the ADDR1 column, the second line under the ADDR2 column, and so on.
- You may have some columns that are filled with information, but you could find no heading that matched the information. In this case, consider moving the material in those columns to the NOTES column.
- Save the spreadsheet as a text file. (The columns must be separated by tabs or commas.) Add an .IIF extension to the filename.
- Start QuickBooks. Go to the File menu, choose Utilities, choose Import, and click IIF Files. Enter the name and path of the file you just created. Click OK.
QuickBooks adds the data to your company file, updating any lists as needed. For example, if you have imported a list of vendors, QuickBooks adds the new vendor names to your Vendor list.