The Data Importer is a feature in which you can populate predefined Excel templates with a wide range of information that can then be brought in or imported into the system.
For example, it is used to bring in existing Supplier or Customer details for new companies and contains Customer or Supplier Codes, names, Addresses, Contacts, bank details and more. More complex static information such as Chart of Accounts, VAT (Tax) Codes, BI Codes and more can all be imported. In addition to this, transactional information such as invoices or payroll journals can also be populated in the Excel template formats, then validated and imported.
If you wish to use this tool please contact your local support team. If you have not used this before you will have to undergo training and assistance to ensure you have the knowledge and skills to use the tool effectively.
This article is concerned with one section of the Data Importer - the Trial Balance and Banks.
If you need any of the other types please go to that section
2. Important points to note
i) Processing transactions and importing simultaneously
Information can be imported at any time but it is very important to note that when you are using the Data Importer wizard from Setup > Data Importer and are in the process of importing data, no other user can be logged into the system. This is because when an import is taking place and a user is inputting transactions, then this can cause transactions to become corrupted. So, please ensure to log all other users out of the system before you import your transactions.
ii) Use the latest Import template you need
The Import Templates are available for download within the Data Importer. The template is available to download in file format Office 97-2003 and Office 2007. It is important to note that the Import Templates need to be downloaded from each company individually. It is not recommended to import data using a template from a different company as this company may have a different setup. It is important to use a freshly downloaded template each time because the new template will contain any changes that any users have made in the system, for example, updates to the General Ledger codes, Supplier defaults, Customer information and more.
3. Types of information that can be imported
For the Trial Balance and Banks Data Importer you can import the following:
i) Trial Balance
ii) Trial Balance Monthly Movements
iii) Opening Bank Balances
4. Populating the Template
Below is a summary of the information which can be populated in each worksheet. The column headings include a user-comment advising what information should be populated in each column.
Please note column headings or worksheet names must not be changed, added or deleted. Please do not add any new columns to the existing worksheets.
i) Make sure to read the information provided at the top of each worksheet/column - it provides supplementary information on how the data should be completed;
ii) Make sure that you download the Import Templates from the correct database;
iii) Ensure that the correct currencies, financial year and client locale are set during the creation routine, or through the main system before attempting to import data;
Download the appropriate template from the Data Importer, save it as a file on your desktop or required location, fill the required fields and save it. Use the tab ’’Generate Data File’’ to generate the CSV file ('Generate Data Upload File'). This CSV is the file you need to upload to the Data Importer in order to import the data you have populated.
This will ensure necessary format checks are carried out, but most importantly that the CSV file used by the importer is created correctly and in the same location as the .xls file. A message will appear at the top of the spreadsheet informing you if this is the case
i) Ensure that you populate the templates from the first row (row 5), and that no row is left blank.
ii) Make sure that no special characters are used (e.g. apostrophes, ampersands, Irish/French characters etc).
iii) Check for blanks in the Code columns.
iv) Non-numeric data should be saved according to the requirements example: date should be saved as date format into the Excel spreadsheet.
v) Make sure that you complete each field according to the requirements provided.
vi) Make sure that the numerical information is correlated with the type of the transactions.
vii) Make sure that the information is selected from the dropdown list, for example, GL codes.
viii) Ensure that there are no totals added to the bottom of any worksheet.
ix) Ensure that valid dates are entered and the financial information is accurate.
x) Ensure that when pasting data into the template that the ‘Paste Values’ option is used - this will eliminate any formulas that have been used to calculate those values.
xi) The values need to have 2 decimals as this is what the Data Importer supports. Please round the values into two decimals before pasting them into the template (easily achieved using the =ROUND() Excel formula).
5. Trial Balance and Banks Template
Go to Setup > Data Importer > Trial Balance and Banks
Click 'Download Trial Balance and Banks Template File' hyperlink as highlighted in Red below.
Note - If you want to import movements for the financial year, please also make sure to select the relevant year for which the movements will be imported.
Choose the 'Save as' option and choose the Location you wish to save the file in and give it an appropriate file name.
Open the file.
If Macros are not enabled please enable them.
5.1 Trial Balance
Code (Compulsory Field) – Once this template has been downloaded, you will be able to select from the dropdown the GL account that you want to nominate in the Trial Balance. Only accounts with values are required on the Trial Balance tab.
Please note you cannot import values directly into the Retained Earnings control account. If you wish to do so please set up a new posting type GL code with the same Category and Sub Category as the GL you wish to import the retained earnings into. For example, if you want to bring in an opening Retained Reserves balance, set up a new posting GL account called 'Retained Reserves opening balances' and you will then be able to import into this GL account. When the Financial Year is closed, then the profits will be posted to the nominated retained reserves control account.
BI Code – If the GL value is split by BI codes, enter the value of the GL corresponding to each BI code.
Dr/(Cr) (Compulsory Field) - Debit values should be positive and Credit values should be negative. The total value of this column should be zero. Dr/Cr in column C is used to import opening trial balance values.
Movements - Nominate GL movements if required for each account. Debits should be positive values and Credits should be negative values. The sum of this column should be zero. Dr/Cr from column D to column O are used to import monthly Trial balance movements.
When this template is imported, a date needs to be nominated, which will be the date when the opening Trial Balance (Column C) will be applied.
This worksheet will update the bank accounts existing into the system and allow you to create new bank accounts in the system. The opening balance needs to correspond with the trial balance values if they are for the same date.
GL Code (Compulsory Field) – Nominate from the dropdown the GL A/C that represents the bank account (if you have already nominated them in the system they will already be listed in this tab). You have to nominate all the bank accounts that you want to be set up in the system in the Bank tab.
Name – Populate with the relevant GL bank account.
Currency Code (Compulsory Field) – Populate the currency of the bank account. This currency needs to be created in the company previous to download this template. To create a currency besides the base currency of the system, go to Setup > Codes Maintenance > Currencies and set up the required currencies here.
Bank Balance – Populate with the bank balance. Ensure that the bank balance is in the currency of the bank account i.e. if it is a US Dollar bank account then the balance needs to be in US Dollars. The US Dollar value then needs to correspond with the base currency value in Column C of the Trial Balance worksheet for that bank account. You will be asked when you upload the file for the date you wish the balance to be updated.