Free Blog Post Updates

Stay up to date by Subscribing to Free Blog updates from Allan A. Krist of QuickBooksTrainingAndSupport.com. We will notify you immediately when new Blog Posts become available.

Contact Allan A. Krist

Allan A. Krist
QuickBooks Training And Support
Phone: 440-590-2553
Click Here to eMail: Allan A. Krist

Import and Export QuickBooks Data for Excel

QuickBooks Tips and Tricks by Allan A. Krist

By Allan A. Krist

We will deal with two types of data that we will manipulate with the QuickBooks/Excel interface: 1) list data; and 2) report data. In this first post we will deal with list data, and then in a subsequent second post we will deal with report data.

The techniques explained in this post apply only to the Windows-based versions of QuickBooks. Why would somebody want to export/import their list data to Excel? The obvious reason is that you may feel more comfortable doing en-masse modifications within Excel. The user can export a particular list, load it into Excel, make modifications to it en masse, and import it back into QuickBooks.

Prior to version 2010 of QuickBooks being released, there was no easy way to accomplish a mass change, but to do an export/import operation on the members of a list. Since version 2010 was released Intuit has simplified the export/import process. However not all users have version 2010 and newer, so export/import remains for them the quickest way to modify/create data en masse. That is just one reason why a user might depend on the export/import function built into QuickBooks. Another reason is that Intuit has not simplified the list export/import process for all its lists, only the customer, vendor, and certain types of items.

For the other lists a user can only resort to the export/import method to do en-masse modifications/additions more quickly than remaining in QuickBooks. Some people feel more comfortable working with Excel to initially create the members of a list and import them into QuickBooks.

First we must deal with definitions so as to minimize the misunderstanding that might result from this post. To export data is to copy it to an external file without modifying or deleting the original data in any fashion. To import data is to copy it into a program so as to cause existing data in the program to be created or modified.

Before we explore in depth the topic of Excel integration with QuickBooks, let's clarify the structure of the file that QuickBooks produces when it moves data outside of itself(exports) its list information(chart of accounts, item. customer, vendor, etc) for use by other programs.

It is called the "IIF" format, but what is it? It is simply the same format that Excel uses for text data, but Excel refers to it as a "txt"(tab delimited) file. Accordingly Excel can read directly the "IIF" file that has been created by QuickBooks in Windows Explorer by doing either of the following:

1. Changing last three characters of the file name from "iif" to "txt"; loading Excel, and opening the file with its new name
- OR -
2. Right-clicking on the icon of the file, choosing "open with" from the resultant drop-down-menu, and choosing Excel from the list of programs that appears

That's all there is to it on a Windows-based PC! Intuit has done the laborious tasks for you by creating its export file with the same format that Excel uses. This greatly simplifies the task of moving data to/from QuickBooks lists. The versions of Excel that are compatible with this "IIF" format of a QuickBooks-produced file are 2000, 2002, 2003, 2007, and 2010.

Now a note of caution for you before you try any of these export/import operations that are explained in this post. You should always make a back-up copy of your QuickBooks Company data file before attempting any exports/imports. If something doesn't work as you intended, you can easily restore your QuickBooks company file data. Now let's export our customer data to Excel so we can ultimately use it with another program or massage it further within Excel. From within the QuickBooks program click the "File" menu, click the "Utilities" option, click the "Export" sub-option, the "Lists to IIF files" sub-option, and then this dialog box will display:

Export QuickBooks Data into Excel
Specify which list you want to export by clicking it, and then click the "OK" button. Next the following dialog box will display:

Save QuickBooks Data for Excel
QuickBooks is asking you where you want to locate the file it is going to create for you. I recommend that you have QuickBooks create it in either the same folder that the Excel program is located in, the "My Documents" folder, or in the "Desktop" folder. QuickBooks will do the export for you when you click the "Save" button. Then you can read it into Excel using either of the two ways I have described above and change it per the instructions that are included with your other application program that you will ultimately import the data into.

Please Note: I have detailed for you the most basic way of exporting a QuickBooks list, there are other ways to do the same thing depending on the version of QuickBooks you are using, but this method will always work.

Importing a file of list data from Excel into a QuickBooks list is somewhat more complicated. Here the data in your list must be configured so as to conform with QuickBooks expectations. That means some extra informational rows have to be added prior to the data rows and an informational first column must be added to the data. I suggest you invoke the "Help" menu, "QuickBooks Help", and search for the details of the format by entering "import list data" in the search box. This approach will give you the detailed instructions on what rows and columns to include with your data.

So that is the explanation of the basic approach to exporting/importing list data. It can be confusing, so if you have any questions or problems with the above, please contact me and I can help you. Perhaps a free QuickBooks Consultation is appropriate for your business. Simply click on the link below to request your Free QuickBooks Consultation.

Click the following link to visit Import and Export QuickBooks Data for Excel Part 2

If you feel your QuickBooks may be slowing down lately or even freezing or crashing we have a special offer that will be of interest to you. For a limited time only we are providing a Free QuickBooks Health Checkup. Simply click on the link below to . . .

Request Your Free QuickBooks Health Checkup