Last Update 08/09/2017
Occasionally, someone needs to import transactions into QuickBooks. If the transactions exist somewhere else, say another software or Excel, it is often more time efficient to import rather than re-key the information by hand. This is useful when there is another software creating invoices for example. Moving from another accounting software to QuickBooks is a prime use of this procedure.
Another scenario is when journal entries are created by an accountant like the Controller or CFO of an organization and they use Excel to create them. Then it is up to the accounting or bookkeeping staff to get those journal entries into QuickBooks.
There are special utility software packages created just for this purpose. Transaction Pro Importer is one. Transaction Pro Importer (TPI) will read transactions in Excel, and import them into QuickBooks. Invoices, bills, checks, journal entries and more can be imported. In fact, most QuickBooks transactions can be imported in this way. Payroll transactions are a notable exception.
Above is an Excel spreadsheet with two transactions we will import into QuickBooks.
Below is the initial screen of Transaction Pro Importer.
The three arrows denote settings in the TPI software created by the user when the initial import is being setup.
The first tells TPI where the spreadsheet containing the data to be imported is located.
The second tells TPI which worksheet within the workbook it should read. In this case, it is sheet 1.
The third is a selection from a dropdown menu listing the supported QuickBooks transactions. Bill has been selected.
In its second screen, TPI identifies the worksheet with data and displays that data for confirmation.
Much of the work is done in this third screen. The list of selections, matching QuickBooks fields to Excel worksheet columns is too long to show in the graphic. But the general idea is in the screenshot.
Two columns can be used for the data to be imported. Either QuickBooks fields can be linked to columns in Excel. Or, a static piece of information can be used to fill in the appropriate field for all transactions.
In this case, the due date will be 01/28/2019 for all transactions. If you could see all the way to the last entry on the list, you would see that the accounts payable account is selected as a static entry for all the transactions as well.
There are another couple of screens (not shown) where TPI tests the import and asks for an account setting for any items it may need to create. This would be helpful if we were importing transactions with items rather than accounts.
Once those screens are passed and TPI is given the command to Finish, the screen showing the import log appears.
This log can be saved as an Excel sheet. If there are any errors and there are many transactions, it may be a good idea to save the log file. In it, you can find the reason for errors, make necessary corrections, and import those entries.
As you can see from the QuickBooks transaction report, our two entries from Excel are now valid Bill transactions in QuickBooks.
Purchase and download the tool for $199 at the TPI website
Advanced Data Dictionary, Sample Import Templates, and Sample Map Files
Hector Garcia, CPA
Certified Advanced QuickBooks ProAdvisor
954-414-1524
hector@garciacpa.com
2 Responses
I use QuickBooks Pro 2015 to do bookkeeping for a veterinary clinic. In the past I have been recording all sales invoices, receipts and credit card statements manually. I am able to export sales invoices and cash receipts from the clinic’s software into excel and I am looking for an option to import this file into QuickBooks. I have been searching internet for the easiest solution and instructions, but I feel overwhelmed with the amount of the information available. Would you be able to suggest what would be the best option for me?
Thanks a lot.
you can convert the excel (csv file) to .qbo and import via bank feeds.
http://bit.ly/CSVtoQBO