Why Import or Export To or From QuickBooks?

Depending on how you use QuickBooks, importing and/or exporting data to or from QuickBooks can be much more efficient and reliable than entering transactions one at a time.

"It's fairly common for a client to hire me to help them move transaction data either into or out of their QuickBooks file. There are a lot of different reasons why, for example:

• Improving QuickBooks performance — If you're a longtime QuickBooks user, you may reach a point where your data file has become so large that it's beginning to affect processing speed, so that the only solution is to create a new data file with only a limited number of transactions from the original data file.
• Data cleanup — Your file may contain some damaged transaction data that are un-repairable. If this is the case, your best solution might be to export the 'good' transactions out of the original data file and start a new one.
• Updating information in bulk — If your company requires frequent price updates to an extensive product line, you can complete the updates much faster by importing from a spreadsheet.
• Employees 'in the field' — You may have employees who work out in the field, generating sales receipts or invoices that you'd like to import directly into your QuickBooks data file."

— Leslie Capachietti

"The most common reason I work with clients importing and exporting data is when they're using Add-On products that provide features that QuickBooks does not. In that case, most of the developers of the Add-Ons have provided the tools to make importing and exporting relatively easy. The biggest issue there is to understand how the process works for each specific tool."

(You can learn more about extending the power of QuickBooks with Add-On Applications at the QuickBooks Solutions Marketplace. -ed.)

"The other reason I work with importing and exporting data is when I use a specific importing/exporting tool. It's amazing what's available. Some tools use the IIF technology, which works with older versions of QuickBooks, but most use XML, which will only work with versions 2002 and higher. There are transaction copiers (which mean that you can copy transactions from one file to another) as well as beginning balance tools (to transfer the balances to start a new file from an old one). There is even an Excel tool included with the newer QuickBooks versions to permit importing list items for customers, vendors, items, and chart of accounts.

"The advantage of importing and exporting is quick transfer of the information. The disadvantage is that some transaction types are not supported based on a limitation imposed by Intuit. (These are primarily payroll-related transactions, because of security concerns with employee social security numbers)."

— Bonnie J. Nagayama

Tips and Tricks

If you decide you want to import or export data, you can hire a Certified QuickBooks ProAdvisor to guide you through the process (or do it for you), or you can learn the process and available applications to do the importing and exporting yourself.

"Importing files into QuickBooks is a much more difficult task than exporting them, for two reasons. The first is that everybody's QuickBooks setup is different. The second is that everybody's source file to be imported is different.

"One thing that is very important for any importing process: You need a pre-flight check that informs you what is going to be imported, and then an audit trail that shows what has been imported."

— Chuck Vigeant

"Here's my recipe for exporting and importing lists:

1. Starting with a QuickBooks sample company, I export each list as an IIF file independently. (File>Export>Lists to IIF File)
2. Next, I open each exported list in Microsoft Excel and save it as an IIF file with an appropriate name (for example "QB Import COA" for the chart of accounts). This creates a template for me to use as I create the new lists to be imported. Note: I always save as an IIF file, not a normal Excel .xls file, because I know from experience that a clean IIF export equals a clean IIF import.
3. Now I export my client's list from their old program into a new sheet in my template Excel file, and I save it again as an IIF file.
4. Next I turn it over to my client so they can clean up the lists by deleting old records and editing cell info to their heart's content.
5. When I get the clean IIF files back, I simply delete the sample data records and copy and paste the real data into the appropriate cells.
6. My client and I then create the new companies together (with no chart of accounts pre-selected) and import each IIF list, creating a new (Append Date and Timestamp) QuickBooks backup before each import.

Two other pieces of advice:

1. Importing your lists will fail if you forget to copy all of the appropriate header cells from the template you make (in step 2, above) into your real data sheet before you do the import.
   
2. As all Certified ProAdvisors have learned...You cannot have duplicate names in any of the QuickBooks name-based lists, including employee, vendor, or customer."

— Wayne I. Greenfeld

• "Make sure your import file is in the correct format and mapped properly so that the information imports where you want. This is the key to importing list and transaction data into QuickBooks. The required format is different based on the type of data you're importing, so it's important to make sure you use the right one. If you don't, QuickBooks will not import the data into the proper fields. I recommend working with a QuickBooks ProAdvisor or at least reading the QuickBooks Help menus about importing files to find the right formatting instructions."
  
  (You can learn more about import/export formats, mapping, and list cleanup in the "Import (and Export) Your QuickBooks Data" article in this issue. -ed.)
• Similarly, the key to importing information from third-party software is to make sure the import file is mapped properly. If it is, the importing/exporting process is usually very simple.
• Clean up the list items in the text file prior to importing data into QuickBooks. If you need to make changes, it is much easier to do in a spreadsheet than in QuickBooks where you have to go into each list item one at a time.
• Keep character limitations in mind. If you have rows with too many characters, QuickBooks will either not import them at all, or import everything except the fields with the error (based on your selected Preference).
• Consider required field formats. Some QuickBooks fields have to be set up in a particular way. For example, addresses won't appear properly on invoices, checks, and other forms unless you've followed the specified field format.
• Export first. Before you import a list, export that list from a QuickBooks sample company to see how QuickBooks formats the export file. For example, if you're importing your customer list, first export the customer list from a sample company. It will provide a good example of how you should prepare your import information.
• When you're finished importing, review the data. This should always be your final step, regardless of the source of the import file. Find, print and review a report from within QuickBooks that will show the details of the imported list(s) or transaction(s) to make sure everything imported properly."

— Inga Arendt

Must-have Applications

Now that you've soaked in these expert tips, are there other ways Certified QuickBooks ProAdvisors streamline the import/export process? Absolutely. Take a look at some of the helpful utilities these in-the-know professionals recommend to simplify the import/export process.

"I work with FLEXquarters.com, maker of the QODBC driver, and have become the largest provider of custom integration and reporting solutions for QuickBooks accounting solutions. Importing and exporting QuickBooks data is our specialty. We've created custom applications and tools that import data from Excel spreadsheets, text files, XML formats, and that integrate with other desktop applications, custom shopping carts, online CRM tools, and a host of others.

QTableGrabber, from FLEXquarters.com Exporting data from QuickBooks is easy using a tool called QTableGrabber from FLEXquarters.com. From within Excel, it exports both lists and transactions for either pivot reporting or saving as a .csv file.

Data Transfer Utility, from Karl Irvin, www.q2q.us For exporting data from one QuickBooks file directly to another, the Data Transfer Utility from Karl Irvin at www.q2q.com is the top tool of its kind.

QDataImport, from FLEXquarters.com We have developed a tool called QDataImport, which imports lists, and eliminates the biggest pain point that Excel users have when trying to import list information: All the errors are corrected before import. The tool allows you to map, preview, manipulate, and import list data with no errors and no duplicates, and eliminates the start and stop processes we used to have in the past.

However, I'd like to stress that for importing transactions, there is no tool capable of handling every situation. Transaction files from invoices, sales receipts, deposits, bills, etc., take many shapes and formats. You may have 100 import files for the same transaction, and none of them would be laid out in the same fashion. So unfortunately, we have been relegated to custom work for most of these routines."

— Chuck Vigeant

"The tools developed by Big Red Consulting are among the most widely used and easy to use import/export tools — and the ones I most often recommend to my clients. These tools are great time savers and can often save hours or even days of data entry time. I have found them to be an invaluable resource for my clients and one of the most frequently used tools in my own practice.

IIF Transaction Import File Creator, by Big Red Consulting
I typically use this tool to import transactions into QuickBooks from text files or spreadsheets. Another practical use for this tool would be to import downloaded bank or credit card detail, for example.

Transaction & List Copier for QuickBooks, by Big Red Consulting
This tool helps you copy data from one QuickBooks company to another (like invoices and checks) and list records (like customers, vendors, and accounts).

Both of these tools are Excel "Add-Ins" which create a special menu item in Excel and can read an Excel worksheet in order to create an IIF file for importing into QuickBooks. For example, to use the Transaction & List Copier tool to send transaction data from one QuickBooks company file to another, you basically create an Excel spreadsheet using a QuickBooks "Journal" report (by simply exporting the report to excel from QuickBooks), with the data that you want to send to another QuickBooks file, and then use the tool to create a file you can import it into the new QuickBooks file."

— Leslie Capachietti

"The tools we use and resell most frequently are from Big Red Consulting (for IIF) and Karl Irvin (for XML).

Transaction & List Copier for QuickBooks, by Big Red Consulting
When a client is using an older version of QuickBooks or I want to "touch" the transactions between the export and the import, I prefer the Transaction & List Copier for QuickBooks from Big Red Consulting.

Data Transfer Utility, from Karl Irvin, www.q2q.us
For an easy transfer from one QuickBooks file to the other, with reports for both the import and export, I prefer the Data Transfer Utility from Karl Irvin.

Beginning Balance Transfer Utility, from Karl Irvin, www.q2q.us
List Importer for QuickBooks, by Big Red Consulting For starting a new file from an existing QuickBooks data file with the beginning balances for accounts, customers, vendors, and inventory, there are two alternatives: the Beginning Balance Transfer Utility from Karl Irvin and the List Importer for QuickBooks from Big Red Consulting.

QIF to Excel to IIF Converter by Big Red Consulting
For transfer of transactions from Quicken to QuickBooks (this works great when the client uses Quicken but the accountant wants to use QuickBooks), I prefer the QIF to Excel to IIF Converter from Big Red Consulting."

— Bonnie J. Nagayama