Personal Expense Credit Card > Data Import - New Specification

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:

ON THIS PAGE YOU WILL FIND:
1 CREATING A NEW SPECIFICATION 2 MATCHING THE FIELDS TO THE CREDIT CARD STATEMENT 3 FORMATTING THE FIELDS 4 ADDITIONAL FORMATTING OPTIONS - SCRIPTS 5 STATEMENTS WITH SEPARATE DEBIT & CREDIT COLUMNS

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

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

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.

In the bank reconciliation data import go to the settings tab > select the add new script parameter button
Complete the fields in the add data import script pop up (see script examples below) > click ok

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:

Deltek WorkBook