QUESTION

 

Can the Journal Entry that the third-party payroll processor provides each payroll be imported into QuickBooks Online using 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 QuickBooks Online. 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 QuickBooks Online using TPro Importer.

 

STEPS TO IMPORT

 

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

 

 

 

CHOOSE IMPORT TYPE: Select JOURNAL ENTRIES as the import type by clicking on the box, continuing to the next window.

 

 

UPLOAD FILES: On this window, either DRAG AND DROP into the gray box your file to import or click in the gray box to BROWSE AND ATTACH your import file. Importer can import any flat file format into QuickBooks Online such as .csv, .txt, and Excel.

 

BEFORE ATTACHING FILE

 

 

AFTER ATTACHING FILE

 

 

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

 

Click SELECT FILE AND SHEET in the lower right-hand corner to proceed to the next window.

 

SELECT FILE AND SHEET: If the workbook has multiple worksheets (tabs) and/or if multiple files files have been attached, a list of the all available options to import will be displayed. Only one worksheet in one workbook can be imported at a time. Therefore, select the workbook and worksheet combination that contains the data that to import by clicking the SELECT SHEET button next to the correct one.

 

 

MATCH COLUMNS: This screen maps the data from the import file to the corresponding QuickBooks Online field. It tells Importer what columns in the import file contains what data for available QuickBooks Online fields.

 

Any field that has a blue asterick (*) is a required field that must either be included in the import file or have a default value assigned to the field.

 

Each field has two options for mapping. Either column can be used 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 COLUMN column.

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

 

Available fields to import are:

 

• REFNUMBER:  Required field. It is the number (can be alphanumeric) that is assigned to the imported Journal Entry in QuickBooks Online. 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.

• TXNDATE:  Ending date of Payroll or date of Payroll Check, whichever is desired. If no date is imported, then QuickBooks Online 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 Default Value column for TxnDate to apply the same date to the entire import.

• PRIVATENOTE:  Imports into Memo field in QuickBooks Online for the Journal Entry. A Default Value can be entered, if needed.

• ISADJUSTMENT:  Specifies if Journal Entry is an adjusting entry. Acceptable values to check the box in QuickBooks Online are 1, true, t, yes, or y. Acceptable values to not check the box in QuickBooks Online are 0, false, f, no, or n. If nothing is imported, defaults to be unchecked.

• CURRENCY:  Available if multi-currency is activated for the QuickBooks Online Company. If nothing is imported, defaults to base currency set in QuickBooks Online Account Settings.

• EXCHANGERATE:  Available if multi-currency is activated for the QuickBooks Online Company.

• ACCOUNT:  General Ledger Account for the detail line. Must already exist in QuickBooks Online prior to importing, and it must match QuickBooks Online 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 using Account Numbers, the TPro option ENABLE ACCOUNT NUMBERS must be selected.

  • 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

• LINEAMOUNT:  Dollar amount to be imported for the detail line.  Positive numbers are imported as Debits, and negative numbers are imported as Credits. Debits and Credits must equal.

• LINEDESC:  Any comment explaining the entry for the detail line, if needed.

• ENTITY:  Customer, Vendor, or Employee Name. Must already exist in QuickBooks Online 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 QuickBooks Online Account Settings. Each detail line of the Journal Entry can have a different Class assigned.

• LOCATION: Available if Locations is activated in QuickBooks Online Account Settings. No matter what title is selected for Locations in QuickBooks Online Account Settings, the title Location will be displayed in TPro for the field. Only Location may be imported per Journal Entry.

 

Below shows how the sample data might be mapped to import a Journal Entry for the Payroll data.

 

 

Once the mapping has been completed, click SAVE MAPPING OPTIONS on the left-hand side of the window. The map can be saved as any name. An infinite number of different maps for each import type can be saved. The different maps are accessed by selecting the drop-down button for CHOOSE SAVED OPTIONS.

 

 

Click PREVIEW AND IMPORT to proceed to the next window.

 

PREVIEW AND IMPORT: This window provides a preview of the data to be imported, showing what data is importing into which QuickBooks Online field.

 

Any data row may be deselected by clicking the box in the first column for the row, removing that data from the import process.

 

Additional OPTIONS are available for the import, if needed.

 

To select OPTIONS, click the button above the import data.

 

 

The OPTIONS that should be considered are:

 

• ENABLE ACCOUNT NUMBERS ON TRANSACTIONS: If importing Account Numbers and not Account Names, this option must be selected.

• DO NOT AUTOMATICALLY ADD ACCOUNTS FROM YOUR IMPORT DATA THAT ARE NOT IN QUICKBOOKS: This option is not necessary for a Journal Entry import as QuickBooks Online by default requires that all General Accounts already exist in QuickBooks Online prior to import.

• DO NOT AUTOMATICALLY ADD LOCATIONS FROM YOUR IMPORT DATA THAT ARE NOT IN QUICKBOOKS: This option will stop Importer from creating a new Location if it encounters a Location that does not currently exist in QuickBooks Online. Instead, that record will fail to import, allowing the Location to be manually setup in QuickBooks Online.

• DO NOT AUTOMATICALLY ADD CLASSES FROM YOUR IMPORT DATA THAT ARE NOT IN QUICKBOOKS ONLINE: This option will stop Importer from creating a new Class account if it encounters a Class that does not currently exist in QuickBooks Online. Instead, that record will fail to import, allowing the Class to be manually setup in QuickBooks Online.

 

Be sure to click the SAVE button at the bottom of the OPTIONS window to save the options that were selected.

 

 

Return to the TRANSACTION TO IMPORT by clicking on the tab along the top of the window.

 

Click IMPORT in the lower right-hand corner to begin the import into QuickBooks Online.

 

A confirmation window will appear to confirm that the import should be processed. Click YES to import or NO to return to Importer to make changes.

 

 

If the data imports without any issues, the data will move to the IMPORTED TRANSACTIONS tab with a message displayed in green along the top saying the import was successful.

 

An import log may be downloaded by clicking on either the EXPORT TO EXCEL tab or SEND TO EMAIL tab.

 

If Importer finds any issues with the import data while validating with your QuickBooks Online data, a message will appear along the top of the TRANSACTION TO IMPORT tab in red, explaining what issue has been found.

 

Be sure to click on one of the highlighted cells (highlighted either red or yellow) to receive the specific error message.

 

 

It could be possible that some data will import, and other data will not. If this is the case, all data that imported successfully will be moved to the IMPORTED TRANSACTIONS tab, and only the data that failed to import will be found on the TRANSACTION TO IMPORT tab.

 

Changes can be made directly to the import data on the TRANSACTION TO IMPORT tab, correcting any data as necessary. Remember that when importing a Journal Entry, all General Ledger Accounts must exist in QuickBooks Online prior to importing.

 

Once the data that did not import has been corrected, click IMPORT again to import only the data on the TRANSACTIONS TO IMPORT tab. The data on the IMPORTED TRANSACTIONS tab will not re-import.

 

Verify in QuickBooks Online that the Journal Entry imported correctly. The most recent transactions entered or imported into QuickBooks Online  can be located by clicking on the magnifying glass in the upper right-hand corner of the QuickBooks Online window.