Process credit/debit cards and ACH (E-check) all from within QuickBooks. Installation is quick and easy!

Importing QuickBooks Transactions with Transaction Pro Importer

| January 14, 2010 | 35 Comments

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:

  • Bills
  • Bill Payments
  • Checks (except payroll checks)
  • Credit Card Charges
  • Credit Card Credits
  • Credit Memos
  • Deposits
  • Estimates
  • Inventory Adjustments
  • Invoices
  • 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:

  • Accounts
  • Customers
  • Employees
  • Items
  • Other Names
  • Vendors

Installing

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.

image 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.

image

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.

image

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.

image

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.

image

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.

image 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.

image

In my first trial import there were some problems with the data. The wizard notified me of the errors.

image

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).

image You can edit the information in the grid directly (it is not saved back to the original source).

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.

image image

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.

image

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.

image

Conclusion

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:

Category: Import/Export, Product Reviews

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 QuickBooks and Beyond blog, as well as his California Wildflower Hikes blog.

Connect with Charlie at Google

Comments (35)

Trackback URL | Comments RSS Feed

  1. John Magno says:

    The help file gives an overview of how to find the sample files and the basics of using the product. For more detailed help users should visit our support forum where they can see FAQ’s and post their own specific import challenges. It is company monitored on a daily basis and has over 1000 members.

    Users can find the full data dictionary on our support forum under the FAQ’s section here: http://baystateconsulting.com/forum/forum_posts.asp?TID=338

  2. John Magno says:

    For advanced users and programmers there is a full command line interface included in the product where you can set the file name, transaction type, map file and much more. You can even let the Transaction Pro Importer run automatically or in the system tray. Visit this link for more details: http://bscdownload.com/downloads/Command_Line_Parameters.htm

  3. Charlie says:

    Thanks, John. As people can see, the product has a wealth of options that I didn’t get into (since this is just a quick overview). This all makes it a more valuable and useful product.

    I will also mention that you can get updates and features on pointers about using the product by following John’s “TransactionPro” Twitter account.

  4. Jo King says:

    Hi Charlie,

    I am told that you also need the exporter tool. I have thought of using this but with being so busy was worried I would open a huge can of worms.

    Jo King

    • Charlie says:

      Jo, I’m not sure what you are saying (or asking). If you want to import things from the outside world TO QuickBooks, use the Importer. If you want to send things FROM QuickBooks to the outside world, use the Exporter. They are independent. If you want to transfer things FROM QuickBooks TO another QuickBooks company file, you can use both the Exporter and the Importer, but I would probably use Karl Irvin’s Data Transfer Utility for that. It all depends on what you want to do and what you are working with…

  5. Peter Choi says:

    I bought this and had to get a refund. The program is great if you have users with a lot of access to full or modify permissions, but if you set the Chart of Accounts to view only, the import will not work. When I spoke with their support, they pretty much stated that is the case and I couldn’t do much about it.

    • Charlie says:

      Importing usually has to be done by someone with high permissions because of the way that QuickBooks works. Sometimes it doesn’t make sense as to what permissions are needed, but their interface (Intuit’s) is a bit obtuse. In any case, when doing an import (with ANY method or tool) you should always be making a backup first, and that requires a higher level permission in any case.

  6. Sandra says:

    I’d like to know How can I Turn off Validate before importing data to QuickBooks using Transaction Pro importes 4.0. Because this proccess takes a lot time qhen I need to import an excel page with more than 3.000 lines.

    If you can help me will be great.

    Thanks.

    • Charlie says:

      Sandra, I’m not sure how myself. There is a very active support forum for the Baystate Consulting products at their web site. I’m sure you can find your info there.

  7. olamide says:

    is there a tool to import inventory receipts into quickbooks POS using excel? i have QB POS 9 and this tool is much needed.

    thanks

  8. Clindy says:

    Have you tested the Invoice import? If so, will it update inventory values (i.e. transfer from B/S to P&L Cost of Goods)

    • Charlie says:

      I haven’t done that specific task, but as I understand how it would work, it should. Note that you can ask about this in the support forum that Baystate Consulting provides.

  9. lauren says:

    i’d like to ask the maximum limit that i can use in setting up the item list in quickbooks, is it 14,000? how about the customer and vendor lists?thanks.

    • Charlie says:

      Lauren, that is a bit off topic here. However, the answer depends on what version and year of QuickBooks you have. The limit to the item list in Pro and Premier is 14,500. In current releases of Enterprise there is no limit, technically, but you are better off if you stay below 100,000

  10. Lyn says:

    I’d just like to give a shout-out to Baystate Consulting, to Karen & John Magno, and to the Transaction Pro Importer tool. I am an Intuit Solution Provider and I have used this tool rather extensively for my clients. I find it to be versatile, reliable, and ECONOMICAL.

  11. John Magno says:

    Thanks Lyn – so glad you like our product :)
    Check out our command line parameters document to do all sorts of cool automation with TPI. Great for advanced users like yourself: http://bscdownload.com/downloads/Command_Line_Parameters.htm

  12. Adam says:

    Any Amazon.com merchant sellers using this software to import their settlement reports into Quickbooks? I tested the trial version and like it, however it seems like there will be a fair amount of data manipulation required prior to importing.

  13. Patrick says:

    Transaction Pro Importer 4.0 is a wonderful tool! I have setup several of my clients to use this to import web store files and for migrating from one accounting system to QuickBooks Enterprise. If anyone needs assistance with data manipulation prior to using Transaction Pro Importer we can write custom macros that will take your raw data file (.txt, .csv, .xls etc) and set it up to be imported into QuickBooks.

  14. Doug says:

    HI Charlie
    I had heard that there was one part of this program where I could import a file so that it filled the respective item pricing per item into the price level list so that I would not have to do that by adding each item’s price individually since we do not use a consistent % off basis? Do you have any idea if this is so?

  15. Charlie says:

    Doug, I’m not sure, as I don’t use it to do that. You can check with them, they have a very active user and support forum. You can also contact my company at sales@ccrsoftware.com, as we have some price level list update features in one of our products.

  16. Brian says:

    Does anyone know if Transaction Pro Importer 4.0 can import tracking numbers (FedEx/UPS) and shipping charges to sales orders if each record to be imported has the SO# in it?

    If it can’t, does anyone know of an app or customized QB programmer that could write an app, that uses the SDK verses the IIF method to handle such a task?

    • Charlie says:

      QuickBooks sales orders don’t have a field for a tracking number, specifically. You could import a transaction with a value like that in a “custom field”.

      I don’t know if the program can update fields in EXISTING transactions or not – I’ll see if I can find out.

      You can contact me directly if you want an SDK based app, I develop custom SDK based software.

  17. John Magno says:

    TPI 4.0 now has a replace transaction feature where if a matching transaction is found then it is deleted and replaced with the new one.

  18. Nick says:

    Hello,

    I purchased Transaction Importer and just getting started. I’ve run across the error below and not quite sure what it means. I am logged in as the Administrator.

    “transaction pro cannot access chart of accounts exit transaction pro and log into the QBs datafile with more permissions and restart this utility.”

    Any idea how to correct this error? Please help!

  19. Nick says:

    I’m working on importing an Investran file to a new QB’s file. I have exported a list of all the J/E’s (roughly 5000 lines) and a detailed trial balance from Investran. I dont believe it’s possible to import an entire TB, but I see that it does import J/E’s. Do you think importing the list of J/E’s is the only way to transfer all the data?

  20. Charlie says:

    Nick, Baystate Consulting has a great support forum, I would recommend asking there. They respond very quickly.

    I would guess that the error has to do either with you being logged in to QB as a user that doesn’t have full permissions, or that you didn’t check the box that says to allow the program to have access to sensitive data when you authorized access to QB. I’d guess the first.

    As for the other, I don’t know what an “Investran” file is, so I can’t help much there.

  21. Nick says:

    Thanks for your quick response! Any idea how to go back to the authorization screen to change the access level?

    • Charlie says:

      Nick, again, try the Baystate Consulting site for help.

      You might run QuickBooks as the admin user, select “Edit” then “Preferences” and select your “integrated applications” preference. Click on the product and then “remove”. Close the windows, then start the Baystate product (with QuickBooks still open). It should ask you for permissions then. But that might not be the right way to do it for their product, that would just be the normal way for most add-ons.

  22. Chris says:

    Hi Charlie,
    So I’ve read and searched for different options on how to sync two QB files running Pro 2012. Let me first give you my situation:
    Running QB 2012 at Home, and on a laptop I take everywhere (2 user license).
    I’ve tried using clouds from different vendors, no good.
    remotely accessing the QB from home is not an option as the laptop doesnt always have internet connection thus the cloud wont always work.
    I like the idea of exporting from laptop to home computer…
    but would also like the laptop to have the most recent database.
    So my question is…. what program would best fit my needs…
    most exporting would be sales and invoice… (no credit cards yet)

    • Charlie says:

      If it is just you, and you are moving between two computers, I would just make a backup and bring it along. That is the simplest and cheapest way.

      If I’m trying to sync transactions between two copies that are being updated in parallel, I would look at the Data Transfer Utility by Karl Irvin, which is set up to do this kind of thing. See my review at http://qbblog.ccrsoftware.info/2008/05/review-data-transfer-utility/

      It does depend on what kind of work you are doing, how many users there are.

Leave a Reply