Excel Tip: Converting CSV files into IIF files

The problem with importing data into QuickBooks is their funky multi-line IIF file format. For each transaction(e.g. invoice) in the single row CSV format you need to create three or more rows in IIF format. For me I import the CSV file into one worksheet and create a separate worksheet I call IIF for the IIF transactions. You could code up each cell in the Export individually but I like the cut and paste method for speed and to cut down on errors. They key bit of information for me was learning about the ROW and OFFSET functions in Excel. As an example I have used the following calculation on my IIF worksheet to copy the contents from a CSV row.

OFFSET('February 2006'!$C$2,((ROW()-1)/3)-1,0)

Using C2 as my starting point in the CSV worksheet(i.e. February 2006) and three rows for each IIF transaction in IIF worksheet, I can copy and paste the three rows that make up the IIF transaction to create more IIF transactions in the IIF worksheet. In this example the next IIF transaction will point to C3 in the CSV worksheet without manually editing.

The final trick is save the Excel file once in Excel format and then save the IIF worksheet in “Text (Tab delimited)” format with a file name like, export.iif. When you go into QuickBooks you will now be able to see file in the Import menu. If you have set up your IIF worksheet with the correct values for the Account, Item, etc. you should be able to import the IIF transactions easily and accurately into the correct places.

2 thoughts on “Excel Tip: Converting CSV files into IIF files”

Leave a Reply