Journals > Journal Import

Owned by Kat Grinter, created
Last updated: Jun 10, 2024
7 min read

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:

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

image-20240515-052229.png

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

image-20240515-052504.png

  • 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

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 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

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.

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

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

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

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 image-20240529-041738.png

  • 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

  • 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

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:

D = Debtor
C = Creditor
H = Holding
H = 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.

Related articles

 

© Tangram 2022. All rights reserved.