QUESTION

 

Can the Journal Entry that the third-party payroll processor provides each payroll be imported into QuickBooks Desktop (QBDT) using Rightworks Transaction Pro (TPro) Importer?

 

RESOLUTION

 

Yes, if the third-party payroll processor can provide a flat file type such as Excel, csv, or txt, then TPro Importer can import the data into QBDT. If the payroll processor provides an IIF file and cannot provide the other file layouts, the IIF file can be open in Notepad. After opening the IIF file in Notepad, remove the Header row(s) and Footer row(s) and save the updated file as a txt (tab-delimited) type. The txt file can then be imported into QBDT using TPro Importer.

 

STEPS TO IMPORT

 

For this example, the data shown below will be used:

 

 

Open Transaction Pro Importer and select the file to import into QBDT by clicking BROWSE and selecting the file from its saved location.

 

Importer can import any flat file format into QBDT such as .csv, .txt, and Excel. Depending on the file type being imported, the first window of Importer will vary.

 

If importing a .txt or .csv file, the option to select the FIELD delimiter will be displayed. The options are Comma, Tab, or Pipe. Select the one that corresponds to the import file.

Typically, COMMA is used for .csv files and TAB for .txt files. Leave RECORD as CR/LF.

 

 

If importing an Excel file, the option to select the worksheet (SHEET) will be displayed. Select the worksheet that contains the data to import.

 

 

NOTE: If any changes are made to the import file after it has been attached it to Importer, the file will need to be re-attached for Importer to recognize the changes.

Select JOURNAL ENTRY as the IMPORT TYPE.

Click OPTIONS button.

The OPTIONS on the BASIC tab to consider are:

• LET QUICKBOOKS AUTO-ASSIGN JOURNAL ENTRY NUMBERS: During import, QBDT will assign the next Journal Entry number following the last manually entered Journal Entry. If the auto-assign option is used, note that the Reference Number is still needed for the import.

• DO NOT ADD NEW ACCOUNTS TO QUICKBOOKS:  This option is not necessary for a Journal Entry import because QuickBooks requires that all General Ledger Accounts already exist in QBDT prior to importing a Journal Entry.

• REFNUMBER: This is a required field. It is the number (can be alphanumeric) that is assigned to the imported Bill in QBDT. It is also the field that allows Importer to know where a transaction begins and ends, which allows Importer to group detail lines together that belong on the same transaction.

• GENERAL TRANSACTION LOG:  This options should be selected for all imports. It will generate a log after the import is finished that will show what transactions imported and any that failed and the reason why they failed.

 

 

The OPTION on the ADVANCED tab to consider is:

• SPECIFY A SINGLE COLUMN FOR DEBIT/CREDIT INSTEAD OF SEPARATE COLUMNS:  DBy default, a Journal Entry imports with Debits in one column and Credits in another column with both Debits and Credits being entered as positive numbers under their respective columns. However, if Debits and Credits are in a single column in the import file, then option will change the mapping to allow for a single column, recognizing positive numbers as Debits and  negative numbers as Credits.

 

Information on all other OPTIONS can be found in the HELP file included with Importer.

 

Click SAVE to save the OPTIONS selected.

 

 

 

Click NEXT to proceed to Window 2 of Importer.

Window 2 of Importer displays how the application is reading the file that is being imported.

 

If the file type is .csv or .txt and on the second window of Importer the data does not appear in columns, then the wrong FIELD delimiter has been selected. Click BACK, select a different FIELD delimiter, and click NEXT to return to Window 2 of Importer. See images below for an example of data not lining up into columns correctly and data lining up correctly.

 

INCORRECT

 

 

CORRECT

 

If the import file has column headers as the first row, be sure that the box located in the lower left-hand corner is checked for FILE HAS FIELDS NAME IN THE FIRST ROW.

 

On Window 2, any rows from the file that should not be imported can be removed by clicking on the row and selecting DELETE SELECTED ROWS. It will not remove the data from the file, only from the import.

 

Click NEXT to proceed to Window 3 of Importer.

 

If mapping this file for the first time or if another import has previously been performed, a message will appear that says that the saved map does not match the import file. Click OK.

 

Window 3 of Importer is the mapping window. It is where Importer is told what columns in the import file contains what data for available QBDT fields.

 

Any fields that are in red are required fields that must either be included in the import file or have a static value assigned to the field.

 

Each field has two options for mapping - FILE COLUMN or STATIC VALUE. Data can be entered in either column but never both.

 

• The first column is to map the info that is in the import file. The import file column headers will be available on the drop-down menu for each field under the IMPORT FILE COLUMNS column.

• Any data that is not in the import file, needs to be included in the import, and has the same value for imported transactions can be entered as a static or default value under the STATIC VALUE/FORMULA column.

 

The available fields are:

 

• REFNUMBER: Required field. It is the number (can be alphanumeric) that is assigned to the imported Journal Entry in QBDT. It is also the field that allows Importer to know where a transaction begins and ends, which allows Importer to group detail lines together that belong on the same transaction. If the import is one large Journal Entry, a default value can be assigned for the RefNumber to group all lines onto the same transaction.

• TRANSACTION DATE: Ending date of Payroll or date of Payroll Check, whichever is desired. If no date is imported, then QBDT will default the date to the date that the import was performed. If no date is in the import file, a date can be entered in the Static Value column for Transaction Date to apply the same date to the entire import.

• ACCOUNT:  General Ledger Account for the detail line. Must already exist in QBDT prior to importing, and it must match QBDT exactly (spelling, punctuation, capitalization, spacing, etc).

  • The import data should include either the Account Name or Account Number but not both.

  • If posting to a sub-account and using Account Numbers, the import should include only the sub-account to which to post. 

  • If posting to a sub-account and using Account Names, the import must include the entire path with each level separate by a colon with no space before or after the colon.

    • EXAMPLE: Main Account:Sub Account:Sub Account 2

• DEBIT:  Dollar amount to be debited for the detail line. Reminder that Debits and Credits must equal.

• CREDIT:  Dollar amount to be credited for the detail line. Reminder that Debits and Credits must equal.

• MEMO:  Any comment explaining the entry for the detail line, if needed. A Static Value can be entered to apply the same comment to all detail lines for the Journal Entry.

• NAME:  Customer, Vendor, or Employee Name. Must already exist in QBDT prior to import. Used if the Journal Entry detail lines needs to reference a specific name. If importing a liability, it can reference the corresponding tax agency or vendor to which the liability is to be paid.

• CLASS:  Available if Classes is activated in QBDT Preferences. Each detail line of the Journal Entry can have a different Class assigned.

• BILLABLE:  Specifies if Journal Entry amount is to be billed to a Customer. Acceptable values to marked amount as billable are 1, T, or Y. Acceptable values to not mark the amount as billable are 0, F, or N. If nothing is imported, defaults to be not billable. NOTE: If amount is billable, then Name becomes a required field to specify which Customer is to be billed for the amount.

• IS ADJUSTMENT:  Specifies if Journal Entry is an adjusting entry. Acceptable values to mark Journal Entry as an adjusting entry are 1, T, or Y. Acceptable values to mark the Journal Entry as not an adjusting entry are 0, F, or N. If nothing is imported, defaults to not being marked as an adjusting entry.

• CURRENCY:  Available if multi-currency is activated for the QBDT Company. If nothing is imported, defaults to base currency set in QBDT Preferences.

• EXCHANGE RATE:  Available if multi-currency is activated for the QBDT Company.

• HOME CURRENT ADJUSTMENT:  Available if multi-currency is activated for the QBDT Company.

• AMOUNTS IN HOME CURRENCY:  Available if multi-currency is activated for the QBDT Company.

 

 

Once mapping has been completed, click SAVE AS along the bottom to save the map for future use. The map may be saved as any name in any location. It will be saved as a .dat file. If the map does not appear during a future import for any reason, click LOAD MAP and select the saved map to reload it. It is also suggested to save a second copy of the map in a separate location as a back-up, just in case.

 

Click NEXT to proceed to Window 4 of Importer.

 

Importer will perform a validation of the data in the file compared to the field to which it is mapped between Windows 3 and 4.

 

Importer will look to see if General Ledger Account Numbers and Names being imported currently exist in QBDT. If any issues are found during the validation, a message will appear to identify what issues were found.

 

 

Click OK to see what fields do not validate.

 

 

Click BACK to fix the mapping, if needed.

 

Click BACK twice to return to the data review window and change any data, if necessary. Changes on this window will not make the changes to the import file, only the import.

 

Click BACK three times to return to the first Window of Importer to re-attach the import file, if changes have been made directly to the import file.

 

Once the data is fixed, click NEXT the same number of times that BACK was clicked to return to the validation step so that Importer can re-check the validation of the corrected data. If no issues are found, Importer will continue to Window 5 of Importer.

 

NOTE: If the validation has previously been performed during this Importer session, a message will appear asking if the existing mapping grid should be overwritten.

 

• If NO is selected, it will add this import data to the last data that was validated.

• If YES is selected, it will remove the data from the last validation so that only the current data that is being imported will be re-validated. Always click YES.

 

 

Window 5 of Importer contains the setup of the default values that QBDT will use if a new Item must be setup during import.

 

If it has been selected to not allow Importer to setup new Items during an import, no new Items will be created. However, the setup of this window is still required.

 

The suggested selections (if new Items are not being setup) are to select OTHER CHARGE as the Item Type and select a General Ledger Account for ACCOUNT such as Miscellaneous, Other, or Ask My Accountant.

 

 

Be sure that SAVE ALL SETTINGS is checked in the lower right-hand corner before clicking FINISH. This will ensure that the settings on this fifth window of Importer will be saved with the map.

 

Click FINISH.

 

A message confirming that it is okay to begin the import will appear. Click OK.

 

 

While the import is processing, a count of the import will be displayed in the lower left-hand corner of Importer.

 

If any issues are encountered during the import, a message will appear. Since the option to generate a log at the end of the import has been selected, YES may be clicked to ignore future messages during the import.

 

Once the import is finish, one of two messages will appear.

 

If the data imported without any issues, the message will display saying that the transactions were imported.

 

 

Click OK to display the Import Log.

 

When the log opens, it is suggested to check the box in the lower left-hand corner to FILTER FOR RECORDS THAT DID NOT IMPORT to be certain no records failed to import.

 

To save the log, click SAVE in the lower right-hand corner, and the log will be saved as an Excel file in the location that selected.

 

NOTE: Once the log is closed, it cannot be retrieved within Importer to view or save later.

 

 

If the data imported with some records failing to import, the message will display saying that the transactions imported with exceptions.

 

 

Click OK to display the Import Log.

 

When the log opens, check the box in the lower left-hand corner to FILTER FOR RECORDS THAT DID NOT IMPORT to display the records that failed to import.

 

Under the STATUS column, it will display a reason that the data was rejected.

 

Click SAVE in the lower right-hand corner to save the file as an Excel file in the location that selected.

 

NOTE:  Once the log is closed, it cannot be retrieved within Importer to view or save later.

 

The saved log file will contain all the records that were imported but will filter to display the failed records only so that the errors may be researched and re-import, if necessary.

 

 

Verify in QBDT under Journal Entries that the data imported correctly.