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 (Vendor) or Customer details for new companies and contains Customer or Supplier (Vendor) 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 Chart of Accounts (General Ledger codes, General Ledger Categories & General Ledger Sub Categories, VAT (Tax) Codes and BI Codes.
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 using this template
For the Chart of Accounts Data Importer you can import the following:
i) Chart of Accounts
ii) General Ledger Categories
iii) General Ledger Sub Categories
iv) VAT (Tax) Codes
v) BI Codes
4. Populating the Template
Below is a summary of the information that 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, General Ledger 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. Chart of Accounts Template
Go to Setup > Data Importer > Chart of Accounts
Click 'Download Chart of Accounts Template File' hyperlink as highlighted in Red below.
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 How to Use
The first tab provides the overview page for the workbook. By clicking on the worksheet names, you will be brought directly to that worksheet.
This page contains details of how the CSV file is to be generated (once all of the relevant details have been completed in the worksheets).
Your progress can be saved at any time by simply saving the workbook.
In order to generate the CSV format file that can be imported into the system, select 'Generate Data Upload File' in the "Generate Data File" tab. This automatically generates the .csv file that can then be uploaded to the system.
5.2 Chart of Accounts
This is where you populate the General Ledger codes (Chart of Accounts that you will use for your company). In this worksheet, you need to populate the General Ledger codes, General Ledger names, General Ledger Categories and General Ledger Sub Categories you wish to use. Once the data is populated in the Category/Sub Category/Departments worksheets (and the file is saved), that data will be available to be selected from the dropdown lists on the Chart of Accounts worksheet.
Code (Compulsory Field) – Populate with General Ledger account code. This code can be whatever code you wish and caters for both letters and numbers. Please don’t include spaces or special characters.
Name (Compulsory Field) - Populate with General Ledger account name.
Category ID (Compulsory Field) – Populate with Category ID applicable to the General Ledger code. The assignment of a Category to a General Ledger code is used for reporting purposes. The GL category should be selected from the dropdown list and will appear once the Categories have been populated in the Category worksheet.
Sub Category ID (Compulsory Field) - Populate with Subcategory ID applicable to the General Ledger code. The assignment of a Sub category to a General Ledger code is used for reporting purposes. A Sub Category can be re-assigned after import. This Sub Category ID must be selected from the dropdown list and will appear once they have been populated in the Sub Category worksheet.
BI Code Required (Compulsory Field) – This allows you to indicate whether BI codes are mandatory for the General Ledger codes.
Select BI Codes – If this General Ledger requires a BI code, then please nominate BI Code from the dropdown.
5.3 General Ledger Categories
Category ID (Compulsory Field) – Populate with General Ledger Category ID. The Category ID must be numeric. The Category ID is the number which will appear in the General Ledger Category listing screen. The Category ID cannot be amended once imported but may be deleted if no transactions are associated with it.
Category Name (Compulsory Field) – Populate with General Ledger Category name. The Category name can be amended after import.
Category Type (Compulsory Field) – Chose the Category type from the dropdown list available.
Sort Order (Compulsory Field) - This will allow you to select the order in which you want the Categories to appear in the Financial Statements. The Category Sort Order can be amended once imported.
5.4 GL Sub CategoriesSub Category ID (Compulsory Field) - Populate with General Ledger Sub Category ID. The Sub Cateogry ID must be numeric. The Sub Category ID is the number which will appear in the GL Sub Category listing screen. The Sub Category ID cannot be amended once it has been imported.
Sub Category Name – Populate with General Ledger Sub Category name. The Sub Category name can be amended after import.
Sort Order (Compulsory Field) - This will allow you to select the order in which you want the Sub Categories to appear in the Financial Statements. The Sub Category Sort Order can be amended once imported. It is also important to remember that the Categories to which the Sub Categories have been assigned to takes precedence over the sort order.
5.5 Tax Codes Tax Code (Compulsory Field) – Populate with a unique Tax code for each rate. The Tax code will appear on the Tax codes listing screen. The Tax code ID cannot be amended once imported.
Description – Populate with the Tax code description, for example “Standard Rate Not for Resale”. Tax description can be amended after import.
Rate (Compulsory Field) – Populate Tax rate. This rate must be populated as a decimal number, e.g. rate 10% must be entered as 0.10.
Standard – If the Tax rate is the standard rate for the region, populate with Y. One and only one Standard Tax Rate can be nominated. This cannot be amended once transactions have been entered into the company.
NOTE: When setting up a new company, the system will require you to nominate a series of GLs that are required by the system. This step needs to be taken each time the Chart of Accounts template is imported.
These accounts ensure the proper functioning of the system. Most of the Required accounts can be changed after they have been nominated, except the control accounts and the Tax accounts.
Even if you don’t have them/use their functionality in the system, these Required Accounts need to be nominated. The system will prompt you to add these after you have imported General Ledger Accounts.
NOTE: When you import the Chart of Accounts please follow all the steps of the import. If you miss or fail to follow all the steps properly, there is a risk that you might override data already existing in the system.
5.6 BI Codes
BI codes can be used to analyze information about the transactions entered into a company. They can be considered the 'Cost Centres' of the company and are relevant for Management Accounting. Code- Populate with BI Code code. Once the code has been imported, it cannot be edited. It can only be deleted if there are no transactions coded to it. Note that these codes can be assigned to various Dimensions if needed.
Description- Populate with BI code descriptions. They can be edited once they have been imported into the system.