Employee Expenses charged to the company credit card may be imported into QuickBooks Desktop (QBDT) using Transaction Pro (TPro) Importer as a Credit Card Charge import type.

 

TPro Importer does not require a specific file layout to import into QBDT. However, if you wish to view or use the TPro sample file for importing a Credit Card Charge, the Sample File worksheet may be downloaded HERE. The sample file will be used for this article.

 

To begin the import, launch Transaction Pro and select to IMPORT.

 

 

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 CREDIT CARD CHARGE as the IMPORT TYPE.

 

Click OPTIONS button.

 

The suggested OPTIONS on the BASIC tab are:

 

• DO NOT ADD NEW ACCOUNTS TO QUICKBOOKS:  If importing General Ledger Accounts on the Bill, this option will not allow a new Account to be setup in QBDT if Importer finds an Account in the import file that does not currently exist in QBDT.

• DO NOT ADD NEW VENDORS TO QUICKBOOKS:  This option will not allow a new Vendor to be setup in QBDT if Importer finds an Vendor in the import file that does not currently exist in QBDT.

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

 

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

 

Click SAVE.

 

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.

 

For the mapping shown below, the following sample data file is being used:

 

 

The suggested fields to map at minimum to import the Credit Card Charge information:

 

• CREDIT CARD ACCOUNT: This is a required field. It is the General Ledger Account of the Credit Card Account to which the charge was made. It must match QBDT exactly (spelling, punctuation, capitalization, spacing, etc).

  • If using Account Numbers, 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

• PAYEE: This is a required field and will be the Vendor to which the purchase was charged.

• TRANSACTION DATE: This is the Date that will be assigned to the Credit Card Charge. This is not a required field; however, if no Date is provided in the import file or as a static value, then the Date will default to the Date that the import is performed.

• REFNUMBER: This is a required field. It is the number (can be alphanumeric) that is assigned to the imported Credit Card Charge 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.

• EXPENSES ACCOUNT: This is the Expense Account in QBDT. It must match QBDT exactly (spelling, punctuation, capitalization, spacing, etc).

  • If using Account Numbers, 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

• EXPENSES AMOUNT: This is the dollar amount for the expense detail line that was reimbursed. QBDT will automatically add all detail lines together for the total of the Credit Card Charges.

• EXPENSES MEMO: Any comment or memo explaining the reason for the expense.

• EXPENSES CLASS:  If using Classes in QBDT, then the Class will need to be mapped to this field for the transaction.

• EXPENSES CUSTOMER: This field is not required. This specifies the Customer or Customer and Job for which the expense was incurred. If the billable field is marked as Y for being reimbursable, this is the Customer or Customer and Job to which the expense will be billed.

• EXPENSES BILLABLE: This field is not required. This indicates if the expense that was charged should be billed to a Customer.

 

 

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.

 

To read about using Aliases when importing Credit Card Charges, check out this Knowledge Base article.

 

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 Vendors being imported currently exist in QBDT since the OPTIONS to not allow new Accounts and Vendors has been checked. If any issues are found during the validation, a message will appear to identify what issues were found.

 

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 Credit Card Charges that the data imported correctly.