QuickBooks uses a “closed” database that you cannot access directly. If you want to extract transaction data, such as invoices or checks, you have to use a third party tool to extract the data (or get the SDK/ programming toolkit and write your own program). Today I’ll review the Transaction Pro Exporter from Transaction Pro, which is an excellent program for extracting transactions from QuickBooks.
The Transaction Pro Exporter is available for $125.00, and can extract information into a Excel spreadsheet, a CSV (comma delimited) file, or a tab delimited file. Each record that is extracted will be represented by one row in the exported file, with separate columns for each field.
(Note: See my review of the Transaction Pro Importer if you want to import transactions)
Transactions and Lists
The transactions that are currently supported are:
- Bill
- Bill Payment
- Check
- Credit Card Charge
- Credit Card Credit
- Credit Memo
- Estimate
- Deposit
- Journal Entry
- Invoice
- Invoice w/Payments
- Item Receipt
- Payroll Detail
- Purchase Order
- Receive Payment
- Sales Order
- Sales Receipt
- Time Tracking
- Vendor Credit
Note that these are all transactions, as opposed to lists. A list would be something like your customer list, item list or vendor list. Transactions are things that adjust a balance in an account. You can export lists from QuickBooks by using the IIF file export (under File then Utilities then Export) or, in some cases, an Excel export.
Unfortunately, some lists in QuickBooks cannot be exported, and in some cases you cannot export all of the information in those lists. It would be very helpful if the Transaction Pro Exporter could add the ability to export lists.
Installing
Installing is simple – run the installation program, open your QuickBooks company file, and run Transaction Pro Exporter. QuickBooks will ask you if you want this program to access the data, you need to say “yes”. This only has to be done one time with each company file you will use.
HOWEVER, note that you must be logged in to your QuickBooks company file as the “Admin” user or you won’t be able to access the file. The program help file doesn’t mention this important item.
A Simple Program to Use
The program presents a very simple user interface.
Simply select the transaction type to export, choose a range of reference numbers or dates, and click the Retrieve Transactions button. The transactions will be listed, and you can export them simply by selecting the file format and clicking the Export to File button.
Here is a sample of an Excel file containing some Invoice transactions:
If you include the transaction detail lines then you get a record in the export file for each detail line, each including all of the information from the main record. This is a “flat file” representation, rather than having a master record for each transaction (such as an invoice) and then sub-records for each detail.
You have the ability to choose which fields are included in the export, by clicking the Select Fields button. Each of the fields are displayed, you can check the boxes for the ones to include. You can also rearrange the order that the fields will be displayed.
Note that the selections you make here are saved for the next time, so you don’t have to go through and make your selections each time you run the program.
Things to Improve
I always can find things that I believe can be improved in a program, but with this one the list is fairly short.
It would be very useful if the program could also export list information in addition to transactions. I’d like to have all of my exports done in one place, the same way. Also, as I stated earlier, QuickBooks doesn’t always do a good job of exporting list information.
When you are viewing the transactions you can resize the overall window, and scroll back and forth, but I would also like to be able to resize the individual columns (this is not a major problem, though).
You must select a filter to be able to get information. If you leave the reference filter blank the program will tell you that it couldn’t find any data, rather than finding all transactions as I had hoped. You also can’t combine the reference and date filters.
The help file is very brief. Just one page of information. I had expected a bit more from the help file, such as some more detail about how to use it the first time with a QuickBooks file, perhaps a bit of discussion about how records are show when you have transaction details, some explanations of details like the “include customer account no” box.
As i mentioned above, if you select the fields that you want to export, the program remembers those selections for the next time. It would be nice if there was a way to make some selections and NOT have the program remember them, so that I could make some changes for a particular export that wouldn’t affect my settings that I normally use.
I would also like to see some additional export formats, such as an Access database, but that is a more complicated proposition.
Things that I Like
The most important things that I like about this program is that it works accurately and it is very easy to use. Installation took moments, and it was very clear how to use the program to export transactions. What more can you ask for?
I also was very happy to note that the program included information from the custom fields that I used in my transactions, something that is complicated to handle with the QuickBooks programming interface.
So, in conclusion, if you need to export transaction information from your QuickBooks database, the Transaction Pro Exporter for QuickBooks is an excellent choice.
Charlie,
I have always created a QB report using filtering to narrow down transaction types, then exported the report to Excel. How does this application improve on the QB export process? Faster, easier, more flexible? We use QB on a fairly basic level so maybe this app is for more advanced requirements? It seems like I am missing something and I really hate to miss out on a good thing.
Karen, different tools are used for different purposes. This particular tool would get your raw data with very little filtering, just by date OR by reference number. Then you would do your additional filtering in whatever application that you have it in. It is very easy and fast, but doesn’t provide a lot of filtering. I use it to move data into a database of some sort that will be used for reporting, usually. I do the additional filtering in the database application.
Great article!
Can this be used to export transactions and, then, import them into another file, or not?
Laura: This product only exports transactions. If you want to import them into another company file you have to buy their import tool (which I haven’t used yet). However, if that is your task – to move transactions from one QB company file to another, a better approach is to use the Data Transfer Utility, which is designed to do just that. And the DTU works with lists as well. See my review of this at https://qbblog.ccrsoftware.info/2008/05/review-data-transfer-utility/
Thanks for clarifying this. I will read your review of the Data Transfer Utility. Thanks.
For $79.00, you can purchase from Big Red a tool that will allow the export of TRANSACTIONS and LISTS to excel, conversion to an IIF file, and the IIF file can be imported into another company.
It works, is accurate, and you can recoup your $79 quickly.
I do use products from Big Red Consulting (www.bigredconsulting.com), although I haven’t written a review yet. Note that there are two advantages of their tool over Baystate Consulting – the lower price and the fact that it works with both lists and transactions (Baystate Consulting only works with transactions).
Note that there are disadvantages, however. Baystate Consulting uses the programming toolkit provided by Intuit (the “SDK”). This is a very reliable method of importing things. Big Red Consulting uses the “IIF” import format, which is an older method no longer approved by Intuit (although it is still available). IIF is more error prone – it can clobber your QB company file (always make backups before importing with ANY tool). IIF cannot import all the information that QuickBooks uses, as it was “frozen” a number of years ago. So newer features aren’t supported.
Without knowing the details of the kind of data you want to import, I can’t say which is best. I tend to lean to Baystate Consulting in general. But Big Red Consulting has worked well for me in addition.
Charlie, you have a great blog, the most informative that I have seen anywhere. My question is how to move the invoices from 2 other computers using QB same company name. We have a server that hopefully everything will all be there, and the girls can all do invoices when it is all set-up right.
To move info from one QB company file to another I would recommend the Data Transfer Utility from Karl Irvin. See my article on this here: https://qbblog.ccrsoftware.info/2008/05/review-data-transfer-utility/
I do JIB (joint interest billing for oil well expenses)and would like to do this in QBKS but have not been able to figure this one out. This software might do the trick, but I don’t think so. Each investor has a % of the well and their expenses are calculated by the %. The owner has to create the JIB (invoice) for each investor according to their %. Now…I need to be able to enter the expenses that the owner receives for the well and create an invoice for each investor from there. Currently I have to calculate the % of the expense and breakdown the expense in a spreadsheet and then enter the info in QBKS. Do you know of another way or software to avoid double entry and save time.
I would like to know what one does with the A/R on the cash basis Balance Sheet which is from the inventory. My accountant does not like it and needs to know. Inventory is confusing with the cost averaging, etc. I wish it would just do simple in and out. Would the non-inventory solve this issue?
First of all thanks for your very informative blog. I work for Baystate Consulting and just wanted update this forum post to let you know that the newest versions of the Transaction Pro Importer and Exporter now have the ability to import and export lists from your QuickBooks company files.
In our Organization Accounting, we have set up a server to record our financial data via One Program. Now, we want to use QuickBooks, but we do not know about it can share recording through server. Can we open one company file with three people at the sametime via server and import other partial data from the far away branch to complete our report? If it is available, how much does it cost for 3 users, 4 users? How can we export the partial data recorded from the other far away branch that they have recorded into the program and bring to import with some data in the heardoffice? We are an NGO in a poor country do not have much money.
We are waiting to hearing from you.
Best Regards,
Saupha
Saupha, that is a bit off the topic of this discussion. Intuit has a networking document at http://support.quickbooks.intuit.com/support/pages/knowledgebasearticles/1009810
Please note that if your users are at separate physical locations networking doesn’t work well with QuickBooks.
Charlie- Great information, thanks for sharing. Do you know of anyone who sells a tool to import data into QB’s like Big Red but who uses “SDK”?
Also, do you know of anyone who sells a tool that enables a user to export Memorized Transactions out of QBs?
Thanks again!
The same company has an import tool. See my article on this at https://qbblog.ccrsoftware.info/2010/01/importing-quickbooks-transactions-with-transaction-pro-importer/
Memorized Transactions aren’t accessible. The SDK does not access them. However, if you open the Memorized Report List and click the Memorized Report button at the bottom, there is an export option (which I’ve not tried myself lately). Note that QB has a habit of scrambling memorized reports sometimes, so exporting them to save them is a good idea…
I need to generate sales history reports that include the inventory item costs. I need to be able to report sales with gross margins by item, customer, sales person, etc.
In QB, this would be the average cost when the item was sold (on an invoice). The current cost (today’s cost), or today’s average cost is not good enough because it may not be the actual average cost that was posted to the COGS general ledger account.
Does ProExporter, or other reporting tool, have the ability to export the average unit cost? On all the QB reports, the average cost is never on the list of items to include on the report.
Cliff, that is a bit of a tough nut to crack. It is very hard to pin down the cost of an item in relation to the sales price of the item if you are buying and selling that item multiple times. The average cost varies by date (even by time of day, technically). You have to capture the average cost of the item ON THE DATE of the sale. That can be done with the ODBC driver, I am not sure if it can easily be done with the Pro Exporter (you would have to ask them). I have a program that I’m testing internally (one that I’ve written) that can get the average cost of an item on a specific date. But to pull that out item by item to match your sales dates is going to be very laborious. You have to ask for the cost of the item on a particular date, item by item and sale by sale…
The Inventory Valuation Detail report does store the actual average cost that was posted to the COGS general ledger account, per sales invoice line item, and date. So the average cost for each item and sales invoice number is sitting right there in QB, already matched/associated.
But the QB Sales reports, which have SO, PO, and custom fields (that don’t show in the Inventory Valuation report), does not allow us to grab the average cost and include it in the Sales Report.
Worse case, I could probably export the Inventory Valuation Report (filtering on Invoice) and Sales by Customer Detail reports to Excel and try a VLookup, or some other method, to add the average cost to the Sales by Customer detail report.
I will call ProEporter to see if the Average Cost can be added to their sales reports.
I am really surprised more QB users are not screaming for the average cost in the Sales Reports. It seems so easy, since the data is already visible/available in QB.
Cliff, Have you had any luck with a report on average costs?? I have just found a 3rd party application – q2q.us – and was wondering if you had found anything yet before I purchase this.
thanks…
Justine, which particular app are you looking at? Karl Irvin (www.q2q.us) has many different applications.
The absolute simplest way to get this (but not the cheapest) is to get QQube from Clearify (see my review of it here http://www.sleeter.com/blog/2011/03/quickbooks-reporting-is-simple-with-qqube/) They put the COGS value for the transactions into whatever kind of trans that you are looking at. They make reporting to Excel a dream – no complicated lookups. Best way to get this data.
Charlie,
I recently switched from Quickbooks Pro 2011 as a purely accounting solution for my QuickSell 2000 POS system.
When we transitioned to the Quickbooks Enterprise 2012 platform, we lost all the transaction history from QuickSell 2000 as well as the history from Quickbooks Pro 2011.
This is a far reach, but do you know of any other third party import/export applications that might help with this situation?
If you can get the data out of the old system you could possibly use the Transaction Pro Importer, but then you have to reconcile all of the adjustments that this will make to inventory and the associated accounts. It would be a major chore to deal with, I would think.
Do you have a trial version?
I wonder which software is better for import, Transaction Pro or Axis Importer by Zed systems? Just watching the You Tube videos Axis looks like a better product to me. I haven’t used either and have to decide which one to use for a client job this afternoon so I goggled comparisons and there are none. This might be interesting for a future article.
Madeline, I’ve not done a comprehensive comparison between the products. I happen to know the people from US based Baystate Consulting (authors of Transaction Pro Importer) very well and I trust them very much. The product works extremely well.