Journals > Journal Import

When entering large volume of journals such as opening balance imports it can be useful to create the transactions in a spreadsheet and upload them to WorkBook. This article shows you how to prepare the import specification and the format in which to upload.

ON THIS PAGE YOU WILL FIND:

CHECK YOUR IMPORT SPECIFICATION

Go to the finance & administration module > general ledger sub-menu > select journals
Select an existing journal entry or create a new one from List tab
Then going to the journal entry tab > click the import data button

In the specification field > use the dropdown list to see if there is a specification which suits the type of journal you’re entering

Check through the list and see whether the fields you need are available in one of the specifications. If they are, continue to the Importing your Journal section

If not, please move on to Creating a New Import Specification below 👇

CREATING A NEW IMPORT SPECIFICATION

From List tab, selecting or creating a new journal entry
Go to the journal entry tab > click the import data button
In the journal entry data import pop-up> click on the Settings tab with the cog icon
Then click the create import specification button

Specification - give the specification a name
Exclude lines - if you have a title row (which is advised) enter 1
File type - this should be ‘excel WorkBook (.xlsx)' unless you’re using a csv file
Field separator - leave it blank if you’re selecting xlsx format in the File type or add a comma if you’re selecting csv format.

Back in the execute tab, you can now start matching the field data

There are a number of different options:

FIELD	DESCRIPTION	REQUIRED
Booking date	This is the date when the transaction will be posted in WorkBook.	Yes
Voucher number	Indicate how you want to assign the voucher number for the relevant transaction. There are 4 options: Automatic voucher number (New voucher number for each line): WorkBook will assign the next running number based on the system setup, each line will be assigned with a new number. New voucher number (same for all lines): WorkBook will assign the next running number based on the system setup, the same number will be used on all lines. Fixed voucher number (use provided default value): WorkBook will use the default number you set on this import specification. Voucher number from imported file: WorkBook will use the voucher number that have been provided in the uploaded excel/csv file.	Yes
Voucher date	This is the original date of the transaction.	Yes
Account entry type	Specify which type of account the entry comes under. F is for Finance D is for Debtor C is for Creditor H is for Holding account It can be set default directly in the import specification which will apply to all lines, or it can be provided in the excel/csv file.	Yes
Account number	This is corresponding to the option you provide in Account entry type above. If Finance type, this refers to GL account number If Debtor type, this refers to Debtor number If Creditor type, this refers to Creditor number If Holding account type, this refers to the holding account code that are set up in WorkBook.	Yes
Currency code	Specify the currency of the transaction. E.g. AUD, USD, SGD, etc.	Yes
Currency rate	This field allows specifying the exchange rate between the transactional currency and the company currency (base currency). This is only required if your transaction is in foreign currency. IMPORTANT: The currency rate in WorkBook is the rate for 100 units of new currency converting to the base currency. Refer to the article Global System Settings > Currency Codes and Rates to learn more about the currency rates setup in WorkBook.	No
Currency amount	The amount of the transaction in foreign currency (bearing in mind whether it’s - or +). This is only required if your transaction is in foreign currency.	No
Amount	The amount of the transaction (bearing in mind whether it’s - or +). This always be the amount based on company currency (base currency). E.g. AUD The company currency can be identified by going to Settings > Global system settings > Currency codes and rates > Company currencies tab.	Yes
Offset account entry type	Only required if you’re off-setting account on the same line. Specify the offset account type: F is for Finance D is for Debtor C is for Creditor H is for Holding account It can be set default directly in the import specification which will apply to all lines, or it can be provided in the excel/csv file.	No
Offset account	This is corresponding to the option you provide in Offset Account entry type above. If Finance type, this refers to GL account number If Debtor type, this refers to Debtor number If Creditor type, this refers to Creditor number If Holding account type, this refers to the holding account code that are set up in WorkBook.	No
Activity GST	If required, this needs to match your Activity GST setup. This usually be used in Europe where it is required specifying the deduction on certain tax.	No
AR/AP GST	Depending on the type of transaction you are importing (e.g. sales invoice, supplier invoice), this allows you to specify the VAT/GST code that is applied to the transaction. If required, this needs to match your AR/AP GST setup Refer to the article Finance > VAT/GST/Tax Settings to learn more about VAT/GST setup.	No
GST amount	Enter your GST/VAT amount. This always be the amount based on company currency (base currency). E.g. AUD.	No
Comment	The description of your transaction	No
Invoice number	Enter the original number of your transaction. E.g. Receipt number, original invoice number, reference number, etc.
Currency GST amount	Enter your GST amount in foreign currency in corresponding to the currency selected above. This is only required if your transaction is in foreign currency.	No
Due date	Specifying the due date of the transaction if you are importing invoices	No
Creditor no.	Your creditor number	No
Payment identification number	To specify the payment reference number if you are importing supplier invoice.	No
Bank reg. no.	To specify the supplier bank registration number if you are importing supplier invoice.	No
Bank account no.	To specify the supplier bank account number if you are importing supplier invoice.	No
Swift no.	To specify the SWIFT code of the supplier bank if you are importing supplier invoice.	No
IBAN no.	To specify the IBAN code of the supplier bank if you are importing supplier invoice.	No
Transaction type	Invoice Credit note Payment	No

Some fields such as dates and amounts will need the format specified, these will be highlighted by the orange triangle

CREATING YOUR IMPORT SPREADSHEET

Now that you’ve ascertained the fields you want in your import, you need to create an excel spreadsheet to match

These need to be in the exact order of the import specification. The import doe not pick up on the name of your field.

IMPORTING YOUR JOURNAL

Once you’ve prepared your spreadsheet:

From List tab, selecting or creating a new journal entry
Go to the journal entry tab > click the import data button
Specification - select the correct journal specification
File type - this should be ‘excel WorkBook (.xlsx)' unless you’re using a csv file
Source - choose your file
Your transactions will appear in the box

If there are no errors, click on the transfer data to current journal button
Your journal will now be populated with your transactions

Finance will appear in the Offset Account Type (unless you’ve specified it in your journal in order to offset the account on the same line), this won’t effect the journal and if you try to delete it, you may delete the whole transaction line.

Process your journal as usual
If there are errors, see below for some tips

TIPS FOR IMPORTING YOUR DATA

The import needs to match your specification exactly. Here are some tips if you’re getting an error:

FIELD	TIP
Date	In your excel spreadsheet, format your date to text and make sure it’s in the same format as the import specification (dd/mm/yyyy or mm/dd/yy)
Voucher no.	You can create your own voucher number but make sure that it has not been used in the system before.
Finance account types	Depending on the type of import, you may need to use numbers to identify your finance account types rather than words: 1 = Debtor 2 = Creditor 3 = Holding 4 = Finance
Account no.	Make sure that you only enter the account number as the import will fill in the rest of the account name
Currency	Check your currency codes in settings > global system settings > currency codes and rates. The code needs to match exactly.
GST/VAT	Check your GST/VAT codes in settings > finance settings > GST codes > AR/AP Codes. The code needs to match exactly.