Creating Import files

Summary

I received a call which centers on IFF files. A client of mine uses an Excel spreadsheet to gather sales information and then they manually prepare invoices for billing. They do their books on QuickBooks and would like to download the Excel spreadsheet information into A/R. How does the Excel spreadsheet need to be formatted in order to accomplish this? What are the procedural steps involved?

Question

I received a call which centers on IFF files. A client of mine uses an Excel spreadsheet to gather sales information and then they manually prepare invoices for billing. They do their books on QuickBooks and would like to download the Excel spreadsheet information into A/R. How does the Excel spreadsheet need to be formatted in order to accomplish this? What are the procedural steps involved?

Answer

First off, this is not trivial. You'll have to format the data in the spreadsheet into IIF (Intuit Interchange Format) format. Intuit has posted several sample files on thei website that can be used as "templates" for creating iif files. You can download these sample file at http://www.quickbooks.com/support/faqs/docs/w_iiffiles.html.

After you've downloaded these sample files, follow these steps: 1) Create an excel spreadsheet(called import.iif) and copy the cells from one of the sample iif files into this new spreadsheet. 2) Copy the data from your client's source data into the appropriate cells for each cell on your iif worksheet. Be careful to get each field into the correct column and make sure all the rows follow the pattern shown in the sample file. 3) Save a copy of the iif worksheet into a file (txt format) and give the file a ".iif" extension. 4) From within QuickBooks, open you company file and select File > Utilities > Import. Then select your import file to be imported.

WARNING!!! Make sure you have backup your data file just prior to doing the import. If you get an error on the import, restore your backup before trying to import again. Otherwise you'll double some of the imported data. TEST, TEST, TEST before you let your client do this without your help. It is not an automated process, and it gets pretty technical. But if the benefit of reducing data entry outweighs the hassle-factor of creating these iif files, you'll probably have a net gain. However, manually entering the data is safer and less error prone.

Here is a sample IIF file with a single invoice transaction. Notice that the columns all have headers with specific tags. These must be exactly as shown here. !TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 DUEDATE TERMS PAID SHIPDATE !SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM PAYMETH TAXABLE REIMBEXP EXTRA !ENDTRNS TRNS 45 INVOICE 6/30/01 Accounts Receivable "Young, Bill:Window Replacement" 55 2 N Y 6/30/01 N 6/30/01 SPL 46 INVOICE 6/30/01 Services:Labor Income -30 N 30 Design N N SPL 47 INVOICE 6/30/01 Services:Labor Income -25 N 25 Labor N N SPL 48 INVOICE 6/30/01 Sales Tax Payable State Board of Equalization 0 2 N 7.75% Sales Tax N N AUTOSTAX ENDTRNS