QuickBooks has a “closed” database – they control access to the data very tightly. While you have some limited ability to import list data (items, customers), it is generally difficult to import transaction data (invoices, checks). Today I’m reviewing an excellent tool that you can use to import transactions into QuickBooks (and lists as well), the Transaction Pro Importer from Baystate Consulting.
Last year I reviewed the Transaction Pro Exporter tool from Baystate Consulting, which is used to extract data from QuickBooks. The Importer tool provides the same excellent quality. This tool is available for $199.95, and can work with information from Excel, Access, various SQL databases (using ODBC), and several text formats.
2/22/12 Note: For a more current review of the Transaction Pro Importer (version 5) see this article: http://www.sleeter.com/blog/2012/02/quickbooks-transaction-pro-importer/
Transactions and Lists
The Importer supports the following QuickBooks transactions:
- Bill Payments
- Checks (except payroll checks)
- Credit Card Charges
- Credit Card Credits
- Credit Memos
- Inventory Adjustments
- Item Receipts
- Journal Entries
- Purchase Orders
- Receive Payments
- Sales Orders
- Sales Receipts
- Statement Charges
- Time Tracking
- Vendor Credits
In addition, the Importer supports the following QuickBooks lists:
- Other Names
Installing is simple, as I expect from Baystate Consulting. The file is provided as a ZIP file. Unzip it, run the installer, open your QuickBooks file, and “authorize” the application in QuickBooks. This is a very typical third-party product installation.Please note that the first time you run this you must be logged in to your QuickBooks company file as the “Admin” user or you won’t be able to access the file.
Also note that if you use the trial version it will only work with one of the QuickBooks sample company files. You cannot use this on your own file in a trial situation.
Simple to Use?
Actually, yes, I found it simple to use considering the complexity of the task. Please note that I’m the type of person that just crashes on in and runs the program, I didn’t read any of the documentation or view any tutorials before I started using it.
The program isn’t drop dead simple to use, BUT that is because importing information into QuickBooks from a wide variety of database formats just can’t be done simply. The program is very flexible and has a huge range of options. The options can seem daunting, but if you focus on the task you are trying to accomplish the process is very straight forward.
Please note that with any import method you use, I strongly recommend that you make a backup copy of your company file before importing. Imports cannot be “undone” other than by restoring a backup, and due to the complexity you can’t always be sure ahead of time that you will get the exact results you are looking for.
There is a simple help file. I would have liked this to be indexed, but you can use ctrl-F to do a search.
There are a number of sample files in various formats that you can use to see how you might format your data, and to use as a test. I didn’t notice this information at first – they are installed into your Documents folder.
Importing a File
In my test I wanted to import a series of checks from an Excel file. The program works in a wizard mode that leads you step by step through the process. I’m going to show you most of the steps – this article is not a tutorial, but an overview.
In the initial screen you select some general characteristics of the import.
Once I selected the import source (an Excel file in this case), the wizard automatically adjusts the screen to show options that relate to the file type.
There are many options available (click the options button). Again, this may seem daunting, but you can focus on just the aspects that fit your situation. Options are GOOD, because every import file and situation is different and you need the flexibility to handle many different things.
Your data is brought in to a spreadsheet-like grid. You have the ability to actually edit the data in this grid before it is imported, which is a feature I found very useful.
As with the Excel list import in QuickBooks you have to create a “map” to associate the fields in your database with the fields in QuickBooks. If you have used any of the “advanced Excel” list imports in QuickBooks you are familiar with this concept.
In my first trial import there were some problems with the data. The wizard notified me of the errors.
The data grid then shows you all of your records, and the information that created the error is highlighted (please note that I’m blurring the data, as it is real client data).
I would have seen fewer errors if I had spent more time looking at the options screen. Several of my records had fields that were too long, and there was an option to have the program automatically truncate those fields (found that after I was done).
Once your data is verified, the import proceeds. This is a slow process, but that is due to the arcane and slow features that QuickBooks provides to third party developers and is not the fault of Baystate Consulting.
If there are errors in the import, you will be notified, and a log will display the errors.
Once I resolved all of the missing fields, fields that were too long, and got the mapping worked out, the import brought in over 3000 checks into my client’s file without any problems at all. This was a huge timesaver for us!
Room for Improvement?
As I said before, importing is inherently a complicated task, and the program has a very large number of options. There were a few places where I was puzzled by the messages that were presented, and some places where the program can be improved. None of these issues are show stoppers – as a programmer myself I always find things that I would like to change.
When there were import errors, you were notified and given a list of ALL of your transactions, with the errors highlighted. You have to search through the list to find the records that have errors.
It would be helpful to get a list of just the bad records, or at least a count of the number of bad records. If you have a very large file to import, locating the errors can be a bit difficult. BUT, I will note that this program is a great improvement over the QuickBooks imports which make errors very difficult to find.
I would have liked more detail in the help file. There were times when I had to fumble about to determine what fields I needed, and how to use some features. Their web site does have a good support forum and tutorial videos, which helps.
The first time I set up an import, after I viewed the data, I got a warning about a saved map not matching. Curious, I hadn’t specified a map yet. I would have expected the program to just take me to the map creation step. Which it did, after displaying this message.
Again, when you are working with data that is coming from a source that organizes things differently than QuickBooks, the import task can be very complicated. This tool certainly has room for improvement, but it worked very reliably. The program didn’t crash, it didn’t damage my QuickBooks company file (IIF imports, an older Intuit method, potentially can damage your file), and the results were exactly what I was looking for. If you need a way to import data into QuickBooks, the Transaction Pro Importer for QuickBooks is an excellent choice.
In the interest of full disclosure, after I had started my evaluation of this product, Baystate Consulting provided me with a license at no charge, as they are aware of my blog and my review articles. This did not influence my review of the product, and I do not receive any income based on this review (unless a Google ad pops up by chance).
Other Articles on Importing
I’ve written a number of other articles on importing data into QuickBooks:
- Importing Overview
- Importing Inventory with Excel
- Review of the Data Transfer Utility (moving data between QuickBooks companies)
- Importing Invoices with Adobe Acrobat Pro
- QuickBooks 2010 Rapid Data Entry
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70’s, focusing on inventory and accounting software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor. Look for Charlie’s articles in the Accountex Report blog, as well as his California Wildflower Hikes blog.