When entering large 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
Go 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
Go to the journal entry tab > click the import data button
In the journal entry data import pop-up> click on the tab with the cog
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 using xlsx or add a comma for csv
Back in the execute tab, you can now start matching the field data
There are a number of different options:
FIELD | DESCRIPTION | RECOMMENDED? |
|---|---|---|
Booking date | This is the date the transaction will be posted at | Yes |
Voucher number | Used when entering creditor invoices | |
Voucher date | Used when entering creditor invoices | |
Account entry type | You can specify which type of account the entry comes under whether it’s: | Yes |
Account number | Finance type - GL account number | |
Currency code | This needs to match the code in your WorkBook system | |
Currency rate | 100.00 if the currency is your native currency or the currency rate for foreign currency | |
Currency amount | Use this if it’s a foreign currency | |
Amount | The amount of the transaction (bearing in mind whether it’s - or +) | Yes |
Offset account entry type | If you’re off-setting the account on the same line, enter the type here | |
Offset account | Finance type - GL account number | |
Activity GST | If required, this needs to match your Activity GST setup | |
AR/AP GST | If required, this needs to match your AR/AP GST setup | |
GST amount | Enter your GST amount | |
Comment | The description of your transaction | Yes |
Invoice number | Used when entering invoices | |
Currency GST amount | Enter your currency GST amount if required | |
Due date | Used when entering invoices | |
Creditor no. | Your creditor number | |
Payment identification number | If required | |
Bank reg. no. | If required | |
Bank account no. | If required | |
Swift no. | If required | |
IBAN no. | If required | |
Transaction type | Invoice |
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:
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), 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 no. but make sure that it’s unique. |
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 |
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. |