Print This Post

Importing QuickBooks Transactions with the Transaction Pro Importer

February 22, 2012 | By | 39 Replies More

Importing information into QuickBooks can be a difficult chore, but it is a task that many people need to perform. One of the best tools that I’ve found over the years is the Transaction Pro Importer from Baystate Consulting. Let’s take a look at this important tool.

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/ (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.
  • Excel file 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.

Invoice source file for import

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.

QuickBooks Customer List

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.

QuickBooks Item List

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.

Transaction Pro Importer startup

The Options button provides you with a number of preferences that will control how the file is processed.

Basic options

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.

Records to be imported

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.

Mapping the fields

Next you see your data as it will be mapped out. Again, you can make small edits to the file at this point.

Mapped data fields

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 Tax Code of non-taxable.

Settings for added items

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.

Log of imported transactions

Here’s my customer list, with the three new customers added.

Updated customer list

Here is the item list with the new items added.

Updated Item List

And here is the list of invoices that were added.

Imported invoices

Conclusion

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 accounting professional that works with QuickBooks should have a copy. The program is well written and well documented. It is covers an incredible range of QuickBooks transactions and lists. Baystate Consulting has an excellent blog that provides great articles on how to use the product in many situations. There is a free user forum for support.

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
  • Bills
  • Bill Payments
  • Checks (except payroll checks which are not supported by the QuickBooks SDK)
  • Credit Card Charges
  • Credit Card Credits
  • Credit Memos
  • Deposits
  • Estimates (Quotes)
  • Inventory Adjustments
  • Invoices
  • Item Receipts  (with or without Bill)
  • Journal Entries
  • Purchase Orders
  • Receive Payments
  • Sales Orders
  • Sales Receipts
  • Statement Charges
  • Time Tracking
  • Vendor Credits

Lists supported:

  • Accounts
  • Class
  • Customer
  • Employee
  • Item
  • Item Assembly
  • Item Fixed Asset
  • Item Group
  • Other Name
  • Price Level
  • Vendor

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

Product Details

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 $199.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.

Tags: , , ,

Category: Product Reviews, QuickBooks Add-ons

About the Author ()

Charlie Russell is the founder of CCRSoftware. He's been involved with the small business software industry since the mid 70's, and remembers releasing his first commercial accounting software product when you had a one-floppy disk drive system, loading the program from one floppy and then replacing that with the other floppy to hold the data. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor with additional certifications for QuickBooks Online and QuickBooks Enterprise. He also is a Xero Certified Partner. Visit his CCRSoftware web site for information about his QuickBooks add-on products. Charlie can be reached at [email protected] He is also the author of the California Wildflower Hikes blog Connect with Charlie at Google

Comments (39)

Trackback URL | Comments RSS Feed

  1. William Murphy says:

    I agree with you completely Charlie. Transaction Pro Importer is an Excellent product, I have made use of it ‘a lot’ for some applications, and they certainly do their best to keep it ‘up to date’ with all that the QuickBooks SDK supports, such as Advanced (hate what the devilish feature can do to a data-file) Inventory; I think they were the first to offer support for that feature a few months after it was released.

    I admit that I have not used their product ‘on the App center’ or for the on-line version of QB, but I must commend them for taking the steps to offer that functionality as well, because our ability to perform ‘file related’ functions in the QB-online product has always been otherwise ‘bad at best’. I have just found it simple in the past to ‘download an on-line file’ to the desktop version, do the work, and then upload it to a newly established account. But that approach would not be good for ‘routine daily or periodic imports’, so the day may soon come when their On-line related products may do the trick far better.

    Murph

  2. Jim 'Rustler' Ernest says:

    One easy question, if I set up mapping for Capital One CC, the next time I use it for CapOne can I pull up that mapping again? Is it stored as a profile in other words?
    If so how many profiles can I store?

    I can’t see having to remap each time. I’m interested mainly for CC and bank downloads.

    But it will not read a .qbo download? That seems short sighted.
    Jim

    • Jim, you can have as many mappings as you wish. They are saved to a file. If you look at the screen shot of the mapping window, you’ll see buttons for “load map” and “save as”, and just at the top of the mapping columns you can see the path of the map file that I used (in red).

      As far as .qbo files, I don’t have an answer (I don’t work with those) so I’ll see if they have a comment on that.

    • Karen Magno says:

      Hi Jim I work at Baystate Consulting. Yes you can have as many maps as you need for payee aliases and we have a detailed article on this on our blog. Just do a search for payee aliases and you will see the blog post. If you have any questions on this please let us know.

      As far as the importing of QBO files we asked our users for the most common file types that they would need to import into QuickBooks as either bank or credit card transactions and we added support for all these file types in Transaction Pro Importer 5.0. Our users told us that they would not have any need to utilize a third party tool to import QBO files as you can do this within QuickBooks by accessing the following menus: File/Utilities/Import/Web Connect Files. That being said if this situation changes and QuickBooks users would prefer to purchase a third party tool to import QBO files we will consider adding this import type to Transaction Pro Importer 5.0.

      • Jim 'Rustler' Ernest says:

        Karen, thanks for responding, the reason I wanted the ability to import qbo files was two fold.
        1. the format seems to be a standard, so I would only need one map, or better you could include a default qbo map
        2. when my version of QB is sunsetted, I lose the ability to use a qbo file in QB through the menu option. But nothing stops me from downloading the qbo file from my bank/CC. If y’all imported it then sunsetting would not be a big issue in this respect (I do not use any other online intuit services, and from the forums there are a lot like me) and it seems to me that would also be an advertising point too

      • Karin says:

        Hi Karen,

        We have our QuickBooks hosted by a company “in the cloud”. Will Transaction Pro Importer 5.0 be able to work between our computer and the web to import the data to the cloud or does the version of QuickBooks have to be on the same computer as Transaction Pro?

        Thank you,
        Karin

        • Karin – “hosted by a company” can be done different ways – which hosting company?

          However, in general, an SDK based desktop product like Transaction Pro Importer has to be installed on the same computer where QuickBooks is installed. That is the way that Intuit has set up the interface. So TPI would have to be installed on the hosted system.

  3. Eric Downs says:

    Charlie,

    I agree as well Transaction Pro Importer is by far the best. I hope they continue to evolve the tool. In the past few months we have taken on a number of projects which have required this great utility. In some cases we have needed to use another tool to supplement what Transaction Pro either can’t do or does’nt do that well. I would love to see an “update” capability where we could add data to an exsiting transactions (like custom fields, purchase order number..so on). I would love to see it take on the credit card transaction import tools like ez credit card import – then this truly could be the swiss army import utility.

  4. Claudia says:

    I am trying to use the transaction pro importer to import invoices for my landscaping business. With the first batch of invoices that I sent out, some customers say that they no longer have a payment network link to pay on line. Is online payment not possible?

    • You should talk to the Baystate folks. I’ve not looked at this particular issue. It does depend on the version of QuickBooks you are using. If you have 2012, there are those preference boxes in the lower left corner of the invoice that control if the link shows, as well as preferences in the customer record and an overall preference setting to enable the feature. Intuit hasn’t made those check boxes available to any third party program, so the import can’t control how they are set. You may have to review each imported invoice individually to check the boxes.

      However, that is just what my guess is, you really should talk to the Baystate folks about that particular detail. If Baystate can’t set that flag, then nobody can.

  5. Mike D says:

    I’ve found TPI5 to be buggy, slow and incomplete.

    I had to develop my own data importer to be able to process the data necessary and to be able to do a complete import.

    Their solution was to break up my import data to smaller datasets and import each separately.. Nothing like a little busy work.. After trying un-successfully for a week, I found that 50K record cause TPI5 to lose itself in the details…

    • Mike, I guess this will just have to be a point where we disagree. I find the software to be an excellent product, and I know of MANY accounting professionals who rely on the product. I’ll admit that I have not done an import of 50,000 records, but I’ll submit that adding that many records to a product like QuickBooks might be a bad idea no matter what method you use. QB isn’t a good high-transaction-volume product.

  6. Chris says:

    Does anyone know how the product deals with product sub-categories. I have my products set up so you might have:

    Dresses
    Dress 1 £3.00
    Dress 2 £4.00

    Tops
    Top 1 £3.00

    If you have imported all your products in to QB can you map to products that are classed as sub products?

  7. Ryan LaPierre says:

    Charlie,

    I was wondering how the Transaction Pro Importer knew which QuickBooks file to import my item list, if there are multiple QuickBooks files for multiple entities on the computer? Does the Transaction Pro Importer import into the QuickBooks file that is open at that time?

    • Ryan, the desktop version of Transaction Pro Importer is going to work the same way as any other add-on product that uses the QuickBooks SDK (programming interface). If you have multiple company files on a computer, the program is either going to work with the one that you specify when you set the program up, or (more likely) the one that you have open at the time that you run the add-on. You can’t use an SDK program if there are multiple company files actually open at a time. So, the simplest way, open your company file in QB, then run TPI, it will use the file currently open.

  8. Graeme Fraser says:

    Hi,
    Is this compatible with Australia Quickbooks?
    Cheers,
    Graeme

    • Yes, it is. However, note that the programming interface for the Australian version isn’t as advanced as the US version, so there are fewer list and transaction types that can be updated. That isn’t the fault of Transaction Pro Importer, it is a limitation based on the support that Reckon provides through QuickBooks.

  9. Payam Manavi says:

    Hi Charlie,
    Thanks for the useful information. I know that this probably is not the right place to ask, but do you by any chance know of a way to export the credit card information for customers out of QB 2012? I have a requirement for a client that is planning on migrating to a standalone POS system and the only problem that I am running into after using ODBC, is that QB is replacing the CC numbers with XXXX1234 for PCI compliancy. They have over 10,000 records and inputting those all by hand will take months. Any help that you can throw my way would be much appreciated.

    • Payam, you won’t ever get that kind of info from the QuickBooks SDK (which QODBC is built on).

      Intuit had a merchant services SDK at one time – I don’t know if that is available still. It might not do what you want. And you probably have to be approved to get that kind of data.

  10. Lee F says:

    A limitation we have with a straight excel import in QB 2011 Pro is that we set up several “mappings” yet seem to be limited to one mapping per import, which is so tedious as to be ridiculous. We’d like to have a single excel worksheet with multiple vendor and customer fields under appropriate headings and have a single import of those multiple mapped fields. Sorry if I am a bit dense but is that capability in Transaction Pro?

  11. Maureen D says:

    I am just starting to learn how to use this product and right at the get go, I get a problem. I am trying to open Trans Pro Importer and it says “Please start QuickBooks and open a company file before starting this utility”. Then it gives me the old “other program busy, retry or switch to” and of course it gets me no where. I cannot get to the screen where it even lets me try and do anything.
    also, is there any kind of manual available as a guide to this tool. Thanks all.

  12. Karen Magno says:

    Hi Maureen,

    When you requested a free trial of the software or if you have purchased the software there are a bunch of links to articles to help get you started as well as an article on how to troubleshoot if you have issues connecting to your QuickBooks company file. You can review this specific article at http://transactionpro.com/forum/forum_posts.asp?TID=73&title=trouble-connecting-to-the-quickbooks-file

    Also once the product is open there are help buttons within the product which will also to take you to our various support resources. If you still have questions feel free to reach out to our support team at [email protected].

  13. Robin says:

    I am admin for a survey company. We have recently branched out and have 2 branches now. Aledo And Weatherford. I am in the W’ford one and take care of all payroll, billing, And A/R. My Aledo person does not know QB and is not interested in learning QB or has the time for it. We just need a basic invoice software and to be able to make the invoice & mark paid on them in that office. But also be able for me in W’ford to import the Aledo invoices to my QBas simple and as little time consuming as possible. Just curious if there’s anything out there that you may use or could suggest to make this easier for my company, thanks for your time.

  14. Julie says:

    Hi Charlie!

    We’re currently using QuickBooks Enterprise Solutions – Manufacturing and Wholesale with Advanced Inventory (multiple warehouse locations / bins) – are there any conflicts or issues you are aware of using this product to import sales order information? Do you happen to know if they offer their product on a 10 or 30 day trial basis?
    Thank you –
    Julie

    • Julie, you have to check with them on support for locations and bins. I haven’t used that specific feature. And, it will depend on what year of Enterprise Solutions. Intuit didn’t make those fields accessible to add-on programs in all years of Enterprise.

      There is a trial, but you have to use it with one of the “sample” company files.

      Again, best to talk to them directly. They have great support.

  15. Jodi says:

    I am trying to find a solution for QuickBooks Enterprise to let me import a file of checks to print into the batch screen and then print the entire batch of checks. Is there anyway to do this currently in QuickBooks or do I have to buy an add-on module?

    Thank you,

    Jodi Cantu

    • Depending on the year and version of Enterprise, the Enter Batch Transactions feature may be available, and you can use that. It is very simple.

      • Jodi says:

        Thank you for your reply. Entering the batch transactions is very simple, its just getting the checks to print once you have them entered is the problem I am having. There is not an option to mark the transactions as “print later” so as to populate the print batch checks window. I can’t find anything on the internet and QuickBooks support isn’t helpful.

        Thanks for your help.

        Jodi

Leave a Reply