In this post we will provide an overview of how to import journal entries into QuickBooks using Transaction Pro Importer. On the first screen of Transaction Pro you will select the browse button to select your import file. In this example we are using the sample import files that are installed with the program and are located in c:\my documents\01 Transaction Pro Importer 5.0\sample files We will also select an import type of journal entry. Then select the options button if you want to select any options.
On the file contents screen you will see the data in your import file in a grid format. Select the next button.
The next screen in Transaction Pro Importer is the mapping screen. If the map does not load select the load map button and navigate to the following file: c:\my documents\01 Transaction Pro Importer 5.0\journal entry map.dat Required QuickBooks Fields are depicted in red in the first column (RefNumber, Account). Select the next button to advance to the next screen.
Review the table below to see all the fields available on a journal entry import and where these fields reside in QuickBooks. You can also review the entire data dictionary here.
|QuickBooks Field||QuickBooks Form - Field Description||Notes||Required Field||Type/Length|
|RefNumber||General Journal Entries - Entry No.||If option, let QuickBooks auto assign numbers is not selected.||Y||Char 11|
|Transaction Date||General Journal Entries - Date||Date|
|Account||General Journal Entries - Account||If using account names include accounts and sub accounts separated by a colon. If using account number just provide the lowest sub account number.||Y||Char 31|
|Debit||General Journal Entries - Debit||Numeric|
|Credit||General Journal Entries - Credit||Numeric|
|Memo||General Journal Entries - Memo||Char 4095|
|Name||General Journal Entries - Name||Char 209|
|Class||General Journal Entries - Class||Char 31|
|Billable||General Journal Entries - Billable||The following values can be used: Y or N, T or F, 1 or 0.||Char 1|
|Is Adjustment||General Journal Entry - Adjusting Entry|
|Currency||General Journal Entry - Debit/Credit||Char 64|
|Exchange Rate||General Journal Entry - Exchange Rate||Numeric|
This next screen shows how the data will be mapped to your QuickBooks fields. The QuickBooks fields are listed at the top of the grid and the data follows in rows immediately below. Review this data to make sure that data is mapped correctly. Select the next button.
On the last screen of Transaction Pro you select how any new items that do not exist in your QuickBooks company file will be set up by making selections from the drop down menus. Please note this last screen needs to be completed even if you are not mapping data to the items item field. Select the finish button to start the import.
Advanced tips and options:
To make the import easier there are several options that you can select on the advanced tab of the Transaction Pro Importer.
(1) Does your import file contain only one column of numbers with the debits expressed as positive number and the credits expressed as negative number you should select the option to: “Specify a single column for Debit/Credit instead of separate columns.”
(2) Does your import file only contain debits or credits and does not balance? Would you like Transaction Pro to post any out of balances amounts to a specific account? If this is the case then select the option: Journal Offset Account – Out of balance posted to …”
(3) Does the accounts in your import file not match the accounts you have set up in QuickBooks? If so you could enable the option: “Cross reference account description for account name…” Then you would go into QuickBooks pre-import and populate the description field on your accounts with a matching value to what is in your import file.
Other blog posts to review:
Journal Entries Tips and Trick: http://blog.baystateconsulting.com/index.php/2010/10/03/journal-entry-import-tips-and-tricks/
Formatting Accounts: http://blog.baystateconsulting.com/index.php/2010/10/21/how-to-format-customers-items-classes-and-accounts-in-your-import-file-for-use-with-transaction-pro-importer-for-quickbooks/
If you do not already have a copy of Transaction Pro Importer for QuickBooks you can request your free demo version here.