Transaction Pro Importer is often used to bring data from a spreadsheet into QuickBooks, which can include a list of names (such as customers, vendors or items) or transactions (like invoices, bills or checks). During the import process, the mapping step maps the data from Transaction Pro into the appropriate QuickBooks fields.
When mapping the spreadsheet data to the QuickBooks fields, two columns are available: the Import Column and the Default Value column. You may only use one of these two columns for each QuickBooks field. The Import Column will select data from your spreadsheet and the Default Value column will enter either the static value (the same name or number for every transaction) or a value from a formula that uses data from your spreadsheet and manipulates it before importing it into QuickBooks.
When using the Import Column, select the relevant column name from your spreadsheet for each of the QuickBooks fields in the mapping screen. QuickBooks fields with a star in front of the field name are required. The other fields are optional.
A static value could be:
- Bank Account Name or Number
- The same memo for every transaction
- Item Type (for list import of items that are all the same type)
- Journal Entry # or Invoice # to bring the entire spreadsheet in a single transaction
A formula will take data from the spreadsheet and modify it, such as:
- Combining two name fields (I.e. first and last names)
- Dividing Total by Quantity for Unit Price
- Adding Sub-Accounts to Existing Main Account or Jobs to Existing Customers
Formulas used in Transaction Pro Importer must follow strict guidelines:
- Data column names from your spreadsheet need to be surrounded with the square brackets [ ] and match the field names exactly
- You can’t mix addition and subtraction with multiplication or division transactions in the same formula
- Pay attention to spacing. In the formula =[First Name]& ” ” &[Last Name]
- No spaces between = and [ or between ] and &
- 1 space between & and “
- “ “ will add one space between first & last names
If you receive an error message when using a long formula, try testing a shorter formula. Then add an additional component to the formula and import. Continue adding components until you get an error message. Then you can troubleshoot what the problem is with the component that caused the error.
For complex formulas, you might find it easier to create the formula in your Excel sheet. Then import that column.
To learn additional information regarding mapping formulas, see our Knowledge Base article, Use the Static Value / Formula Column in Transaction Pro Importer, or contact our Customer Success Team at support@transactionpro.com.
