The Credit Card Transaction Import reads an Excel file that you specify and then imports each row of that file as credit card transactions.
NOTE: This option can also be accessed from Manage > Unposted Credit Transactions.
Up to 13 columns of information can be imported. The order of these columns does not matter, but the column headers in the first row must contain the descriptions listed below. Some columns accept alternate labels for the column header. The alternate header names are indicated in the parenthesis. Required columns are indicated with an asterisk(*).
The highlighted items are only required if they are a part of your account structure.
- Date*
- Amount (Split Amount)*
- Vendor (Merchant, Description)*
- RegionNumber*
- SuperFundNumber*
- FundNumber*
- LocationNumber*
- CostCenterNumber*
- DepartmentNumber*
- AccountNumber (Expense)*
- AccountSub*
- DetailDescription
- Project
- Note (Notes)
- CreditCardUser
- Ref#
- Invoice#
- 1099 [use “short name” from table below]
NOTE: The header names are case-sensitive (e.g., “date” or “DATE” will not work, must be “Date”).
Only the account structure levels that you are using need to be specified. For example, if you are only using funds and department in your chart of accounts, then you only need to specify "FundNumber" and "DepartmentNumber" columns in your Excel file. Also, the AccountSub field only needs to be specified if you are using Sub Accounts.
The CreditCardUser column in Excel should be formatted in one of the following ways, omitting Titles, Middle Names, and Suffixes:
- [First Name] [Last Name]
- [Salutation] [Last Name]
- [Last Name], [First Name]
- [Last Name], [Salutation]
An example file is displayed below.
A template is provided if you prefer to use it. Make sure you delete the "asterisks" from the template header descriptions, which indicates required information. Also remove any unused columns from the spreadsheet that do not apply to your setup (e.g., SuperFundNumber, CostCenterNumber, AccountSub, and so forth). Download Credit Card Import Template.xlsx.
List of 1099 options:
| Short Name | Description |
| Int | Interest |
| Box 1 | Misc. Box 1 - Rents |
| Box 2 | Misc. Box 2 - Royalties |
| Box 3 | Misc. Box 3 - Other Income |
| Box 4 | Misc. Box 4 - Federal Income Tax Withheld |
| Box 5 | Misc. Box 5 - Fishing boat proceeds |
| Box 6 | Misc. Box 6 - Medical and health care payments |
| Box 8 | Misc. Box 8 - Substitute payments in lieu of dividends or interest |
| Box 10 | Misc. Box 10 - Gross proceeds paid to an attorney |
| Box 13 | Misc. Box 13 - Excess golden parachute payments |
| Box 14 | Misc. Box 14 - Nonqualified deferred compensation |
| Box 15 | Misc. Box 15 - State Tax withheld |
| NEC Box 1 | NEC Box 1 - Nonemployee compensation |
| NEC Box 4 | NEC Box 4 - Federal Income Tax withheld |
| NEC Box 5 | NEC Box 5 - State Tax withheld |
| R | Retirement - R - Used for non-taxable retirement |
| R2 | Retirement - R2 - Used for taxable retirement |
| R3 | Retirement - R3 - Used for capital gains |
| R4 | Retirement - R4 - Used for Federal tax withheld from retirement |
| R5 | Retirement - R5 |
| R9 | Retirement - R9 |
Steps to Import the Credit Card File
- The first step is to select a file. Click the Choose File button and then select your import file using the dialog box that appears.
- Confirm the filename is correct and then click the Import button.
- The file is imported and displays as a grid. Review the transactions for any incorrect information.
NOTE: Click the here link to reverse the signs for the transaction amounts.
- Click the Edit
icon to make changes to individual transaction or click the Delete
icon to delete a transaction. These changes do not affect the original Excel file that was used for importing.
- Another option is to click the Clear Credit Card Transaction Import button to clear all the transactions and then make corrections in the Excel file. After making corrections to the Excel file, select to import the file again.
- Invalid transactions can be viewed by clicking the View Invalid radio button. Invalid transactions occur when the information from the Excel file does not match with the fields in your Financials program. Other invalid transactions may include payments, which are normally entered on the Transactions > Update page. After an invalid transaction has been corrected, it appears as a valid transaction to be imported.
- After reviewing the valid and invalid transactions and making any corrections, click the Import button to finish the import process.
- The imported transactions appear on the Credit Card > Transactions page. At this point, you can process these transactions as you normally would.
Updated