When an import project recurs, such as regularly importing invoices or bills from a third party, it helps to create a Transaction Pro template, especially if the data has to be “massaged” before importing it into QuickBooks. For example, the exported data might include a few columns that you need to combine into a single field and import into a description or memo field in QuickBooks, or you might need to use Excel formulas to adjust numbers before importing.
Transaction Pro has some formula capabilities in the mapping stage but, depending on your needs, you may find it easier to do the “heavy lifting” in Excel. (You can use this sample Transaction Pro template to follow along.)
Setting Up Your Template
Start by moving the exported data from the third-party source and pasting it into a Raw Data worksheet tab. Then copy all the columns from the Raw Data worksheet to the same columns in the Transaction Pro worksheet tab. Leave the Raw Data untouched, so you can refer to the original data if questions arise.
You’ll notice the Transaction Pro worksheet has a few formulas built in that allow us to paste data in the columns to the left of the formulas. After pasting the data on the Transaction Pro worksheet tab, you may have to adjust the formulas (columns J & K) to cover more (or less) rows, depending on the number of rows from the new data brought in.
In column J, there is a formula combining the size and color data into a single field so you can import that into a memo or description field in QuickBooks. Column K is a VLookup formula. You can export all the Customer names from QuickBooks to an Excel list and paste them on the Look Up page in the worksheet. This formula compares the customer name in your exported data and verifies that the same EXACT name exists in your QuickBooks file. If it doesn’t, you’ll see a #N/A error message, which means it won’t import correctly into QuickBooks.
When this happens, ask your third-party vendor to change the customer name in their database so future exports correctly match customers in QuickBooks. The other options are:
- Change the names in QuickBooks to match third-party source names or,
- Change the names in your Transaction Pro worksheet to match QuickBooks names before each import.
Whenever importing names (Customers, Vendors, Inventory Items or Services, Chart of Account names, etc.) into QuickBooks, the names MUST MATCH EXACTLY. Otherwise, you can either have Transaction Pro add them as new names or receive an error message during the import.
Next, make a copy of your worksheet with formulas intact but no data and rename it. Then you can open it, paste your data into it and save it with a new name so your template stays intact.
You can use the Transaction Pro Test Import tab to paste a few rows or transactions from the Transaction Pro worksheet. Then run a small import to make sure the data is importing correctly into QuickBooks in the right fields. If there are any issues, delete the test import from QuickBooks, fix the issues in your mapping or worksheet, and import the test page again. If all looks good, you can either delete the test import from QuickBooks again, or just delete those rows from your Transaction Pro worksheet and import the rest of the Transaction Pro worksheet. If you are importing a large amount of data (more than 500 rows), you can break up your import into multiple pages, to import in batches.
Use the Notes tab to list the steps for the import process, either as a reminder or to assist someone new who’s handling the import.
