Transaction Pro Importer (TPI) is a powerful and versatile tool for importing data into QuickBooks®. TPI is an essential tool for getting data into QuickBooks since not many third-party systems directly import or sync with QuickBooks.
There are many specialized apps that help you run your business effectively: web shopping cart apps, inventory trackers and bill payment systems, to name a few. They often have financial components that need to be entered into QuickBooks. However, most of them do not sync with QuickBooks automatically. So, you could be stuck manually entering your data into QuickBooks. However, TPI enables you to export a file from another application and into QuickBooks.
While most apps generally provide a good file for importing, sometimes the format is wrong for QuickBooks. With Transaction Pro, you can customize your import file by developing a template in Excel that you can use repeatedly without having to redo your changes. You can create a template by adjusting the mapping directly in Transaction Pro Importer.
Developing an Excel Template
Here is the process of developing a template and directly mapping in TPI.
Let’s assume we have an import file for checks that looks like this:
Several items need to be fixed here. For example, the name field for vendors is not formatted correctly, and the total purchase amount is not correct. Also, you want a special message on the bill, and “Contractors” is the bank account used for all the transactions.
Setting up a Simple Excel Template
For both QuickBooks Online and QuickBooks Desktop, you have the option of setting up an Excel template to import your data. If you import often from your third-party app, setting up a template will save you lots of time and ensure you have a positive import experience each time. An Excel template can be basic or complex.
Below are some simple steps to follow to generate a simple template for import:
When finished, your worksheet will look like the image below. It’s ready to be mapped in TPI. When mapping into QuickBooks, use your new fields:
Mapping directly in Transaction Pro
You can also make an Excel template for QuickBooks Desktop, but it’s not necessary for simple changes. Transaction Pro for QuickBooks Desktop allows you to include formulas in the “Static Values/Formula” column.
All the formulas you need for each field appear in the mapping screen of TPI. You cannot use Excel functions here, but you can use simple text and math functions. The field names need to be in brackets.
Here is how your import map in QuickBooks Desktop will look with the necessary changes:
Here is what your check will look like in QuickBooks Desktop.
In Transaction Pro for QuickBooks Online, you only have the option of entering a static field to your import. Formulas are not yet allowed. You can add “Checking” to the accounting field.
Testing your customization
This is very important. When creating formulas, or using a customized template, it is always a good practice to import just one or two transactions prior to importing a whole batch. In both the Online and Desktop versions of QuickBooks, Transaction Pro allows you to select the records you want to import. That makes them easy to delete it in QuickBooks if they don’t import as expected. Make sure you review all of the lines related to a transaction to test. With Transaction Pro for QuickBooks Office, you can just check or uncheck the rows you want to import.
With Transaction Pro for QuickBooks Desktop, you delete the rows you do NOT want to import. This does not affect your import worksheet.
So, there you have it, some tricks to make your import process more effective and productive. If you get stuck, any of the Transaction Pro consultants will be happy to help.