Importing information into
QuickBooks is a “closed” database. That means that you can only use a limited number of methods to import data, and all are controlled by Intuit. There are really only four ways to import data:
- IIF file import: This is an older format, accessed via the File/Utilities/Import/. This is an older format (see my older article on IIF). Intuit hasn’t been updating this for many years, and it isn’t always reliable. I always avoid it if I can.
Excelfile import: This is also found in File/Utilities/Import, and it is limited to just importing customer, vendor and item records. See my article on importing inventory records for an example. There also are a few other Excel related imports scattered about in the program where you can copy/paste Excel info into a grid, such as Add/Edit Multiple List Entries and the Lead import found in the Lead Center.
- QuickBooks SDK programming: This requires you to either be a programmer (and it can be complicate to use the SDK) or to use a third-party tool that uses the SDK (much easier!). See Doug Sleeter’s article on QuickBooks Add-ons for a discussion of the SDK.
- QuickBooks IPP programming: This is a newer method of programming being pushed by Intuit, but again it requires you to either be a programmer (and this is REALLY complicated!) or to use a third-party tool using this method. This is a newer method that is still being developed, so you won’t see it for imports as often.
Wait, what about all the other methods we hear about? The ODBC drivers, the “Method” programming language, other similar products? Well, they all are just layers on top of one of the approaches that I outline above (or, are a read-only method that won’t let you import).
I’ve worked with all of the methods listed above, and the absolute best method is to use a product that uses the QuickBooks SDK. This covers the widest range of QuickBooks data and has the best internal error checking.
My absolute favorite import tool is the Transaction Pro Importer, which is based on the QuickBooks SDK. The tool works, it is highly accurate, and it provides a wealth of features that I haven’t found anywhere else.
A Sample Import
Let’s take a look at how this program works. I’m going to import several Invoice transactions from a formatted text file. This is a common request – I am getting invoices from some sort of outside order system.
This isn’t a simple import. Consider the following:
- There will be a Customer for the invoice. What if the Customer record isn’t already found in the customer list. Will it be added?
- What if the Customer already exists in the customer list, will the import add a duplicate?
- You will have one or more items in the detail section of the invoice. Will the items be added if they aren’t found?
- If an item is added, what item type will it be added as?
- What about the accounts that are needed for the item if it is added? What accounts will be used?
It is not a simple task.
Here is a view of the file that I’m starting with (I edited out the blank columns to compress this). Click on the image to see a larger view.
This is a simple flat text file, tab delimited. This could be a file exported from an outside order generation system for example. Note that the Transaction Pro Importer isn’t limited to this kind of file, you can use many kinds of data sources (as I’ll list later).
In this sample file there are 11 invoices for 4 different customers.
Looking at my customer list in QuickBooks, only one of these customers is already on file. That means that this import must add three of the customers but not create a duplicate record for the fourth.
If we look at the item list we see that one of the items (“Door”) is already on the list, but the other items are not. This is a important issue because not only do the other items have to be added to the item list, adding items isn’t a simple task. At the very least the items must have an item name, an item type, and at least one account. This is information that you don’t normally find in an import file.
The first step should always be to make a backup copy of your file! Imports cannot be reversed, no matter what tool or method you are using. Make a backup copy before importing, so that if you find that something didn’t work right you can always go back to the file before you imported.
After starting Transaction Pro Importer you select the file to start with, some characteristics of the file, and the type of import.
The Options button provides you with a number of preferences that will control how the file is processed.
Next you get a display of the records that you are going to import. Note that you can make minor edits here, and delete records that you don’t want to include.
Mapping of your import records is always the toughest part of setting up a new import. Transaction Pro Importer helps by listing the possible QuickBooks fields in the left column, as well as highlighting in red those that are required. This is very valuable information. The middle column shows the fields that are available from your import file, which you select from a dropdown list.
Next you see your data as it will be mapped out. Again, you can make small edits to the file at this point.
The next window is an extremely important one – this is where you tell the program how you want to set up items that must be added to the item list. Here I’m specifying that any item in an invoice that doesn’t exist in the QuickBooks item list is to be added as a Service item, with Sales as the account, and a Sales
After this, the import takes place. The final screen is a log of what was imported. If there are any errors they will be listed here. You can save this log to Excel.
Here’s my customer list, with the three new customers added.
Here is the item list with the new items added.
And here is the list of invoices that were added.
The key points from my import example are:
- The program imported the records correctly.
- Records that were NOT in the lists were added properly without stopping the import.
- Records that matched those that were ALREADY in the lists were not duplicated.
- The process is straight forward and relatively simple.
- There is error checking at each step in the process (not shown here, as there were no errors in this sample import).
I’ve worked with several different import methods and tools with QuickBooks. I’ve written several spcial purpose SDK based import tools myself. This is my absolute favorite import tool and I think that any
Is it a perfect product? Well, no, there is always room for improvement. You may find (as I did with a test on importing inventory assemblies and their bills of material) that you have to create more than one import to accomplish a specific task. The product does require a lot of thought if you have a more complicated import setup, and the help file doesn’t always cover all cases (but their blog provides many great tutorial articles). There are some error messages that could be made a bit clearer. However, keep in mind that this is a general purpose import tool and it covers a huge range of transaction and list types, so it is difficult to have a specific setup for every possible import that you may need to do.
I do note that they have made some very significant improvements in error checking, the help file and the user interface from the 4.0 version I reviewed two years ago, and I expect that they’ll continue to make improvements.
Get the product, you won’t regret it!
Supported Transactions and Lists
Transaction Pro Importer 5.0 supports the following transaction types:
- Bank Statements
- Bill Payments
- Checks (except payroll checks which are not supported by the QuickBooks SDK)
- Credit Card Charges
- Credit Card Credits
- Credit Memos
- Estimates (Quotes)
- Inventory Adjustments
- Item Receipts (with or without Bill)
- Journal Entries
- Purchase Orders
- Receive Payments
- Sales Orders
- Sales Receipts
- Statement Charges
- Time Tracking
- Vendor Credits
- Item Assembly
- Item Fixed Asset
- Item Group
- Other Name
- Price Level
Custom fields are supported in the advanced options, and that is a VERY useful feature.
Importing multiple currency transactions? Transaction Pro supports that in a wide variety of transactions and lists.
If you are using the Advanced Inventory option for QuickBooks Enterprise you will find that Serial and Lot Number fields are available in Bills, Checks, Credit Card Charges, Invoices, Item Receipts and Sales Receipts. You will find multi-location site fields available in Bills, Invoices, Purchase Orders, Sales Orders, Sales Receipts, Items and Inventory Assemblies.
The latest release adds many new features, such as
- Payee alias matching, where you can easily match up payees with saved aliases for Bank Statements, Bills, Checks, and Credit Card Charges
- The new Bank Transaction import type which creates both checks and deposits from single import file.
- The ability to import Quicken (QIF) or Microsoft Money (OFX) formats.
- A new option – Do not add new accounts
- Support for Amazon.com date formats
In this short review I didn’t go into the details of the many options that you have to control how the import is managed. There is a very long list of options (preferences) that you can set that will manage the import process. The import example I used above shows that you can easily import records without a lot of detailed preparation, but if you have a more complicated situation you have many built in features to manipulate the data.
What about the things that aren’t listed here? Memorized transactions, Leads and so forth? Well, that is due to the restrictions that Intuit places on third party software developers – these kinds of transactions and lists aren’t accessible by ANY means. As fast as Intuit exposes these elements to developers, Baystate Consulting adds them to the product.
Source File Types
You can use any of the following kinds of files or data sources for your import:
- Delimited Text Files (TXT, CSV, TAB, ASC file extensions) using commas, tabs or other delimiters.
- Quicken (QIF) and Microsoft Money (OFX) files.
- Excel Files (XLS, XLSX, XLXM, XLSB extensions).
- Access database (MDB and ACCDB extensions).
- Any ODBC compliant database that can be implemented as a DSN in Windows (this is more complicated to set up).
The Transaction Pro Importer is available from Baystate Consulting and it will work with the Windows desktop version of QuickBooks Pro, Premier and Enterprise from the 2003 version (US) and later. Note that older versions of QuickBooks might not let you import all of the transaction/list types I show above, due to restrictions in the QuickBooks product itself. In addition, this will work with the Canadian version of QuickBooks (2003 and later), UK version (2005 and later) and the Australian version (2005 and later).
The Windows Desktop version of the product is available for $149.95. At the time I’m writing this the latest version is 5.0 – you can upgrade from 4.0 for a discounted price. In addition to this product, Baystate Consulting has:
- Transaction Pro Exporter for Windows desktop
- Transaction Pro Importer for QuickBooks Online Edition
- Transaction Pro Exporter for QuickBooks Online Edition
- Transaction Pro Importer for Intuit App Center (an Intuit App Center app that is web based and works with QuickBooks Online Edition by subscription)
- Delete Transaction & List Data from QuickBooks
- Accounts Payable Allocation Utility
- Country Club Manager Pro
Baystate Consulting was an exhibitor at The Sleeter Group’s Accounting Solutions Conference in 2011.
- Managing Duplicate and Outstanding Transactions in QuickBooks
- Transferring QuickBooks Invoices to Bills with Karl Irvin’s Invoice To Bill
- 1099s the Easy Way
- Salesforce for QuickBooks
- Integrating QuickBooks with ACT! and Xact