Importing Credit Cards as Expense Reports

Connected System Requirements for Expense Report File Import:

-Connected 11 or greater

-Available for all Connected configurations

-Requires setup of Expense Codes and Default selections, prior to use.

The following article explains how to import a single Expense Report from a file.  For example, a file may be downloaded with all expenses for a credit card for a statement period.  

Please Note: All other documentation relating to setup and using Expense Reports is contained in the Accounts Payable section of the CONNECTED User Guide, under the Help menu within the application.

ARTICLE CONTENTS

• Getting Ready to Import Expense Reports
• Import File Format
  • Expense Details Import File Format
  • Expense Map File Format
• Importing from a file to an Expense Report
• Importing Expense Reports Expanded
  • Attaching Receipts to Expense Reports
  • Payments are not an Expense
  • Keep Map Files Updated
  • Default Description as Mapping

Getting Ready to Import Expense Reports

Before a file can be imported, an "Unmapped Expense Code" must be defined in the A/P Defaults screen, under the "Expense Reports" tab.  This is required if already using Expense Reports before attempting an import.

Note: It is recommended a designated Expense Code be made for unmapped expenses. By doing this, unmapped expenses can easily be identified and edited on the unposted Expense Report.

The following screen shows the A/P Defaults screen and an example of an "Unmapped Expense" in the Expense Codes setup window.

Import File Format

When importing an individual expense report, there are two seperate files required.

1) Expense Details: This is the file based on the download of the Credit Card Statement.  It will be unique each time.

2) Expense Code Map: This is the file that contains a map in the event that the category label is not the same as the expense code.  This file will evolve over time and be reused each time an import is done.

In either case, the import file requires either Comma Separated Value (.csv) or Tab Delimited text file (.txt).  Each file and specific order of columns is explained in the following sections.

Expense Details Import File Format

The following explains the simple file format for the "Expense Details" portion of the import.  The order of the columns is as follows:

(1) Expense Date (required, must be within current accounting year)

(2) Expense Code (required, must be an active, non-automotive expense code)

(3) Description (255 characters, if left blank it will be auto filled with the expense code default description)

(4) Amount (required, must be a positive amount)

Sample format shown below:

How to handle Special Characters:
Import data that contains special characters such as single/double quotes and carriage(hard) returns, cannot be directly imported.

Option 1 - Remove all characters that fall into this category.

Option 2 - They can be replaced with substitute strings using a text editor application.  If string substitution is used, during import, the strings will then be replaced with the original characters.

- Substitute /dqt/ for double quotes, /sqt/ for single quotes, and /rtn/ for carriage returns. (the forward slashes must be included

NOTE: Payments made to a credit card should be removed and not included with the import file. Payments do not need to be recorded on expense reports, as they have already recorded in Accounts Payable.

Expense Map File Format

A map file is only required when the category or "expense" in the Expense Report import file do not correspond to the expense codes in CONNECTED.  For example, a credit card statement that is downloaded from a bank, may contain specific codes that are used by the bank.  A map file will link those codes, to the correct code in CONNECTED.

The map file must be a comma (.csv) or tab-delimited text file (.txt) with the columns in the following order:

(1) CONNECTED Expense Code (required, must be a valid, non-automotive expense code)

(2) Import File expense Description (required, this is the code that would appear in a downloaded credit card statement for example)

Sample format shown below:

Importing from a file to an Expense Report

To import from a file into an Expense Report:

1) Open the Expense Reports window from the CONNECTED Desktop or Accounts Payable menu.

2) Select "Add from Import" from the Actions menu, as shown in the following screen.

3) The following "Import Expense Report Details" window appears.

4) Enter the "Vendor", "Date", and "Comments" for the import.  

NOTE: Only Vendors that have been marked as and "Expense Report" vendor, in the Vendors window, can be selected.

NOTE: The "Date" will be used for the general ledger allocation for all expenses imported into a single expense report.

5) After completing the three fields, click the "Select Import File" button and select the file to be imported.  

6) Click the "Select Map File" button and location the map file to be used.  The following screen shows when both files have been selected, with the file path of each displayed after selection.

7) Click the "Import" button to proceed.  If there are any unmapped expenses, the following message will appear.  Click "Yes" to proceed and allocate unmapped to the specific expense code designated OR click "No" to abort and update the map file with the new items before repeating the process.

8) If proceeding with unmapped codes, the following prompt will appear to confirm if unmapped codes for the import should be exported to a file for reference.  Click either "Yes" or "No" to proceed.

9) Once the import is complete an OK message will appear, followed by an "Import Results Report".  If the import had any errors, it would be reported on this report.

Once the Expense Report is imported, it will be not be marked as "Approved", even if "Auto-Approve" has been selected in the A/P Defaults. Unapproved Expense Reports cannot be posted and therefore provides an opportunity to fix unmapped codes. 

The following screen shows an imported Expense Report, which includes one unmapped expense line for "MISC", highlighted in green.

Importing Expense Reports Expanded

Attaching Receipts to Expense Reports

CONNECTED has a handy Quick Note feature, that can be used to bulk attach documents like receipts to Expense Reports.

Quick Notes for Adding Files and Short Notes

Attaching Files and Documents in Connected

Payments are not an Expense

Any payment activity that is included in a downloaded file from a bank (credit card statement) needs to be removed from the import file. A payment is not an expense, and has already been recorded (or will be) in the Accounts Payable module via Payments.

Keep Map Files Updated

The best way to make sure expense report importing is accurate and efficient is to continue to work on a master "map" file over time, adding new ones as they come up.  

Default Description as Mapping

If a category is not provided in an import file, the Description will be used as the default category.