There is no built-in function to transfer transactions from one QuickBooks File to another one (alternatively: Batch Enter Transactions feature in QuickBooks Accountant and Enterprise), but there are several tools available in the market that facilitate this process, among them are the following:
- Transaction Pro Importer & Exporter 6.0 by Bay State Consulting
- Transaction Copier by Big Red Consulting
- Data Transfer Utility by Karl Irving CPA (FEATURED HERE)
At some point, I will compare and contrast them four, but the time being i’ll show you Data Transfer Utility (DTU) because it is very simple to use.
First you open up the QuickBooks file from which you want to bring transactions from (Source), then load DTU which will look like this:
Note that DTU is based on Microsoft Access, so you must have it installed in order to use it. If you do not have Access, you can download a FREE Access Runtime Engine from the Microsoft website.
Step 1, click on ADD/CHANGE for the source file, you will get the familiar window asking you to grant Access to DTU to get into the QB file:
Step 2, click on CHANGE under “Select Type of Data to Export”, which will take you to this screen:
In the bottom right, you will see: “Pass 1, Pass 2, Pass 3, and Pass 4”, because it is recommended that you import certain items in order and 4 passes are recommended to transfer an ENTIRE QuickBooks File. For example: Pass 1 transfers all the Lists, Pass 2 transfers all the transactions except Deposits, Bill Payments and Customer Deposits, Pass 3 will bring only Bill Payments and Customer Deposits, and Pass 4 Brings Deposits.
Why this way? This is to prevent errors from linked transactions.. you Must first have an invoice, then payment, and lastly a deposits; if it comes in out of order it breaks. Hope that makes sense to you.
Anyway, I will do a simple example and just bring BILLS and BILL PAYMENTS. This means that I need 3 passes.
First, Pass 1: Vendor List. Click on Pass 1, Clear All, then just choose the Vendors and Vendor Type
Click on Close Form, Click on ALL DATES and Then on EXPORT DATA.
Then, switch over to the Destination QuickBooks File, then in DTU, go to OPEN DESTINATION COMP, it will ask you to grant access to the File in QuickBooks, and after you agree go back to DTU and click on IMPORT DATA
You may get error messages about setting up a company e-email address, turning on sales tax, and multiple units of measure.. if that is the case, go ahead and follow the instructions to make it work.
After the import is complete, you can click on 6c. VIEW IMPORT REPORT to inspect how it went, and see if any errors occurred in the process.
Perfect… the on to Pass 2: Bills. In DTU click on CLOSE CONNECTION, and in QuickBooks Switch back to the Source Company File in, and back in DTU go to CHANGE under Select Data to Export, Click on Pass 2, Clear All, then just choose just the bills.
Then you click on Close Form, and click on EXPORT DATA. Note: Every Bill, Item Receipt, and Vendor Credit from the same vendor needs to have a different reference number! Karl makes a FREE add-on utility that automatically adds numbers to blank ref number bills.
Anyway, now we are ready to import the Bills, so we repeat the same steps from before, switch back our QB company file to the destination file, back in DTU click on OPEN DESTINATION COMP and then IMPORT DATA
Assuming that your Chart of Accounts and the Items list contains the exact ones being used here, then you would be OK. If not, go back to PASS 1, and make sure the Chart of Accounts and Item List is included in that import. Same goes if you are job costing with Customer names or Using Classes. This should all be part of that PASS 1 we were discussing earlier, if not there will be ERRORS during the importing.
At the end , you should see a report with no errors, like this:
And Lastly, Pass 3: Bill Payment Checks and BillPayment Credit Card Charges:
Follow the same steps in export and import the data.. and you should be finished.
Now, you can pull a transaction detail report on both company files to make sure 100% adds up.
If numbers are OFF, then these could have been an error message during the import/export process and it was ignored.
So, what do think of this utility?
Cons: The interface is a but hard navigate for first timers working with Karl Irving Tools, there are tons of error checking which makes the user feel that the process takes forever.
Pros: The interface is consistent with other Karl Irving Tools, so its not too difficult to get used to. The error checking on this program is phenomenal, there is nothing more frustrating than importing 99% of the data.. because almost always you have to start all over again anyway even for a susceptibly “measly” 1% variance.
All and all, I like this tool very much, I have used if it for several projects. it has a $169 price tag which is one of the least expensive in the market for that it does.
Karl, has working demos of almost all his tools at q2q.us, so you are welcome to test it yourself.
-Hector Garcia
Advanced Certified QuickBooks ProAdvisor
hector@garciacpa.com
