We have started this “How to” page in order to provide more specific instructions for each transaction type or list that can be imported into QuickBooks using Transaction Pro Importer (“TPI”) for QuickBooks. If you do not see your transaction type listed yet – it will be shortly.
However, before you start reviewing these individual posts here are answers to the most commonly asked questions regarding TPI.
What kind of file do I need to use Transaction Pro Importer?
Although the importer is very flexible and allows mapping fields from any Excel file, text file, MS Access database or ODBC compliant database to QuickBooks fields, the file must follow some basis rules. More information on import file guidelines can be found below.
1. Flat file: The file must be a flat file with repeating data from common rows. This means that if you have an invoice file, certain fields like the customer name and invoice number should repeat over and over again for the same transaction. See example below.
The file has two transactions (1000,1001) where transaction 1000 has two line items and 1001 has one line item. Note that the customer name repeats along with the matching order number and only the item, quantity and price changes. It doesn’t matter how many fields are in the import file, or whether or not the file has column headers for each field, as TPI allows you to select just the fields you want to use for mapping to QuickBooks. In this example the file has field headers (Order Number, Customer, etc) but if the file doesn’t contain field headers, TPI would just display default field headers as Field 1, Field 2, Field 3 etc.
Several transaction types also allow for multiple items or expenses accounts on one line. The transaction types that support mutiple items/expenses are listed below.
- Bills (on the advanced options tab)
- Checks (on the advanced options tab)
- Invoices (on the e-Commerce options tab)
- Sales Orders (on the e-Commerce options tab)
- Sales Receipts (on the e-Commerce options tab)
For the transaction type credit memos in addition to the one item the following fields can be on the same line: shipping, handling, discount, sales tax.
2. File formats supported:
- Excel Files
- Text Files
– Delimiters (shows where one field starts and ends): comma, tab or pipe
– Record separators (shows where one row/record ends): CRLF (carriage return line feed) or ENTER key at the end of each line
- Microsoft Access Queries or Tables
- ODBC Database Queries or Views
- Microsoft Money files
- Quicken files
3. Multiple Line Transactions: TPI determines when a transaction starts and ends by the information mapped to the QuickBooks field RefNumber on the mapping screen. For instance, in the example above you would map the QuickBooks “RefNumber” field on the mapping screen to the Order Number Column. This would tell TPI that the first two lines which have the same RefNumber 1000 should be imported to the same invoice. Watch a video demonstrating this concept on our YouTube channel here: http://www.youtube.com/baystateconsulting#p/a/u/1/EXFg7v2ngcY
4. Sample Import Files: Sample import files for each transaction type are installed with the software. The sample import files are located in c:\my documents\01 Transaction Pro Importer 5.0 directory.
5. How do I do the mapping?: Essentially, you are just matching the import file columns to the QuickBooks fields on the Transaction Pro Importer mapping screen. We have a detailed mapping tutorial here.
What are the limitations of the free demo software?
With the free demo you can import your own data into any QuickBooks company file. During the free trial we use a random transaction date and append -TPI5 to any new list items. An easy way to test the product is to create a backup of your live QuickBooks data base and use this backup to run all your tests.
Having difficulties connecting to the QuickBooks company file?
There are many reasons why you might not be able to connect to your QuickBooks company file. You can read more about this on our support forum.
Having trouble getting started?
Review the Quick Start Guide in the help file. A shortcut to the help file was created on your desktop when your installed TPI 5.0.