Journal Import (For Bank, Excel and Similar)

This guide aims to inform you of the basics in general ledger import. Often used to import bank statements into WorkBook.

Step by step guide

1. Creating a new import type

In this example we will be creating an import type for our bank statement.

Head into Finance & Admininistration – Journals – Journal entry. From here click the journal icon with an arrow as seen below.

This will bring up your import window and you will head to the second tab call “Setup of import”.

Specification: In here you will give it a name in “Specification” and choose the file type. The file type must be of the same type as the one you wish to import. (Types as .xls / .csv and similar are also supported)

Exclude lines: In the right hand side you can choose how many rows you wish to exclude. If you have headers on your columns, input 1, to exclude the first row, in which the headers are.

Field separator: Field separator is only relevant if you use the format .csv. In this case choose the same field separator as you have in your csv file e.g. “; : , .”.

Scripts: In the bottom part of this view various scripts can be input to aid the import. Here are two examples.
Call support if you need assistance with this.

CalcFields: This is useful if you want to summarize a debit and a credit column into 1 column as the Journal import only supports 1 column (+ and -), not debit and credit.
Script type: The most common setting for this is “Before default”.
Field Number: In the below example we chose our new summarized column, “Field Number”, to be 7.
Parameter: We chose to summarize column 4 and 5 into column 7. We want column 4 to be the positive number (Debit) and column 5  to be the negative number (Credit). It must be written as such “@7=@4-@5”

DeleteEmptyLines: This is useful if you have additional data at the bottom of the sheet you want to exclude. The system can be informed of certain columns that in which case they are blank, the row must be excluded. In the below example my focus column is 3 and 4 as they are the debit and credit columns in my sheet. If these are blank I do not want to rows which may have unnecessary text in them imported.
Script type: The most common setting for this is “Before default”.
Field Number: In the below example we chose the first of the two focus columns “3”.
Parameter: We chose to exclude the rows if the columns 3 and 4 are blank. This must be written as “3;4”.

2. Formatting your import type to match your spreadsheet.

Once the above is completed to you are ready to format your import type.

In this area you must first choose your newly created type in “Specification”.

Afterwards you can format each column as seen below. Field 1 represents column 1 in your spreadsheet, field 2 represents column 2 and so forth.

In this example we have the booking date in column 1 in our spreadsheet. We therefore choose booking date from the dropdown menu. In our spreadsheet our booking date is shown as dd/mm/yyyy (e.g. 23/04/1995), which is why we choose that specific formatting in the formatting line. If yours is represented as mm/dd/yyyy, dd/mm/yy, dd mm yyyy etc. please choose that.

The same principle continues for the other columns. For currency code it must match your spreadsheet AND your WorkBook. That means if you do not have USD in your WorkBook you cannot import USD.

For amount the story is similar. If your data in your spreadsheet shows as -1,234 and 1,234 choose the matching formatting in WorkBook. If you do not use the sign “-1,234” but instead use (1,234), choose accordingly. Do also pay attention to your decimal separators.

If you have other columns than the ones mentioned, you can always choose accordingly from the dropdown menu, as mentioned earlier.

In the event you want a default value to be input or input a column that does not already exist in your spreadsheet, you can use the Default row here you only choose from the dropdown to specify the data being input and leave the formatting blank. In the Default line, you then write manually the default data you want input. E.g. 14256 for account numbers or amount or USD for currency, 04/10/2016 for date and so forth.

Further reading

Was this article helpful? Useful Useless 4/7 found this article helpful.