The credit card data import specification needs to be setup before you can import your credit card bank statement. This specification needs to match your statement export exactly.
ON THIS PAGE YOU WILL FIND: |
|---|
CREATING A NEW SPECIFICATION
Before creating the new setup, download your credit card statement in the standard format
In Workbook: finance & administration module > Export, Import & Maintenance sub-menu> personal expense credit card
Go to the import file tab > and click the ‘import’ button
The data import box will open
Click on the settings tab > the create import specification button
Complete the following fields:
No | Field | Description |
|---|---|---|
1 | Specification | This is the name of the new credit card. E.g. National Bank CC |
2 | File type | Select the file type of your credit card statement. E.g. CSV or Excel |
3 | Exclude lines | This tells Workbook which lines to ignore at the top of your credit card statement, e.g. your header lines. If there are 5 header lines, enter 5 |
4 | Field separator | If you’re importing a CSV file, enter a comma here. If it’s an excel file, the field will lock so nothing is required |
MATCHING THE FIELDS TO THE CREDIT CARD STATEMENT
Once you’ve set up the above fields > go back to the execute tab > and select your newly created specification from the dropdown
Now you’re ready to set up the fields to match your credit card statement
When entering the fields some of them will require formatting. See notes below
Fields available:
Field name | Description | Required | Formatting |
|---|---|---|---|
Credit Card No. | This field is very important as it matches the credit card mapping that you’ve already set. If you have multiple cards in your credit card statement, it allocates the reconciler to the transaction. If you have a single card and the statement doesn’t include the credit card no. you can default this field to match the reference on your mapping. You will also need to create a specification per card so that it defaults correctly. | YES | NO |
Creditor Info | See note below in blue | NO | NO |
Payment Info | An optional field for reference purposes only | NO | NO |
Employee ID | See note below in blue | NO | NO |
Expense date | The date of the transaction | YES | YES |
Expense description | Description that will appear on the expense entry | YES | NO |
Currency code | An optional field for reference purposes only | NO | NO |
Currency ID | An optional field for reference purposes only - will need to match the currency ID in Workbook | NO | NO |
Currency amount | An optional field for reference purposes only | NO | NO |
Amount | The local currency amount of the transaction. If you have a credit and debit column, a script will need to be added. See script information below | YES | NO |
Activity ID | See note below in blue | NO | NO |
Unique transaction ID | See note below in blue | ||
Location | An optional field for reference purposes only | NO | NO |
Country | An optional field for reference purposes only | NO | NO |
Merchant category code | See note below in blue | NO | NO |
Merchant code | See note below in blue | NO | NO |
Expense time | An optional field for reference purposes only | NO | NO |
Currency rate | An optional field for reference purposes only | NO | NO |
Import currency code | See note below in blue | NO | NO |
Import currency ID | See note below in blue | NO | NO |
Tax code | See note below in blue | NO | NO |
Tax code ID | See note below in blue | NO | NO |
Expense entry type | See note below in blue | NO | NO |
Expense entry type ID | See note below in blue | NO | NO |
GST location | See note below in blue | NO | NO |
Above fields in blue - these fields wouldn’t commonly be available on a credit card statement but you can add them prior to import if you wish, just make sure the values match Workbook exactly.
Field 1 needs to match column A in your bank statement, Field 2 matches column B etc.
FORMATTING THE FIELDS
If a field needs a format selected it will come up with a yellow triangle
Click on formatting > and enter a value from the dropdown box to match your bank statement export E.g. dates: dd/mm/yyyy or numbers: -1,234.56/1,234.56
Now that you’ve setup the specification, you can import your credit card file and check that the information matches correctly
EXAMPLE
ADDITIONAL FORMATTING OPTIONS - SCRIPTS
Scripts are used when additional formatting is required on an import. You can use them to delete extra lines at the bottom of the import or calculate 2 fields
Standard script usage:
CalcFields
Used to calculate an amount from other columns and insert it into a different column. A typical example of this would be a file with debit and credit amounts in separate columns (@1 and @2 for example), but the import only has a single amount field, so there is a need to combine them.
Function: Inserts a calculated value into the specified field.
Sequence: Not important.
Script type: After field.
Field number: Not used.
Parameter: @TargetField = calculation. @field to use values from the import. For example, @3 = @1 - @2 to combine debit and credit into a single amount column in field 3. Field 3 would be mapped to Amount, while field 1 and 2 are not mapped to anything.
More information on separate debit and credit columns is available below
DeleteEmptyLines
Lines can be deleted with this. The import can stop on its own at the last line, but if a file contains empty lines among the data then that can cause errors for some fields such as dates.
Some lines that look empty can also simply be blank in excel, and these may also be wrongly imported. This can cause an import to be overly long and encounter issues as well.
This script checks if a line is empty in certain fields and deletes it if so. It only allows checking within the first 16 fields.
Function: Checks specific fields (2 minimum) to see if they are empty and deletes the line.
Sequence: Not important.
Script type: Before default.
Field number: Not used.
Parameter: Field;Field;... So for example if you want to delete lines where field 3, 5 and 6 are empty you would write this: 3;5;6
ReverseNumericValue
Used to convert positive amounts to negative, or negative amounts to positive. This is useful if an external document only provides expense values as negative, for example, but the expense values need to be positive for the expense entry import.
Function: Reverses the numeric value in a field..
Sequence: Not important.
Script type: Before default.
Field number: Not used.
Parameter: @field to be converted. So @5 to reverse the value in field 5.
STATEMENTS WITH SEPARATE DEBIT & CREDIT COLUMNS
The credit card import only allows for a single ‘amount’ column. If your credit card statement has a debit column and credit column:
In a field with nothing in it on your credit card statement, enter the ‘amount’ option
Use the ‘Calcfields’ instructions from above
Example:
Credit card statement:
Script:
Fields:
