Data Importer User Guide Section 3 - Static Data

Data Importer User Guide for importing Customers, Suppliers, Items, Item Locations, Item Sub-Locations, Item Groups and Item Sub-Groups

Written By Niall Conlon (Draft Writer)

Updated at June 8th, 2021

1. Introduction

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 Chart of Accounts, VAT (Tax) Codes and BI Codes.

If you need any of the other types please go to that section

Chart of accounts

Trial Balance and Banks

Opening Financial Position

Transactions Importer


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 Static Data Data Importer you can import the following:

i) Customers

ii) Suppliers

iii) Item Locations and Sub Locations

iv) Item Groups and Sub Groups

v) Items


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.

General Checks

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. Static Data Template

Go to Setup > Data Importer > Static Data

Click 'Download Static Data 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 Customers


Code (Compulsory Field) – Populate with the customer code.  The customer code cannot be amended after import. It is recommended that you use alphanumeric codes like ABC001, EDF123 which facilitates easier searching for users inputting, searching for transactions and records and running reports. Avoid the use of special characters in the code and the description of the customers.

Name (Compulsory Field) – Populate with the customer name.

GL Sales Account (Compulsory Field) - If the sales account associated with this customer is different than the default sales account, please nominate it.

GL Control Account (Compulsory Field) - If multiple control accounts are used for debtors, please make sure that each customer has the appropriate control account nominated. This field cannot be changed after the import.

Currency Code (Compulsory Field) – Populate with the customer currency code. If currency code is not populated the base currency code (default currency) is applied.  The currency code cannot be amended after import.

Bank Code (Compulsory Field– Populate with the GL bank account number associated with the customer.  If a bank code is not assigned a default bank code from customer defaults is assigned.

Tax Code (Compulsory Field) – Populate with the Tax code assigned to the customer. If a Tax code is not assigned the importer will assign the default tax code from customer defaults

The following fields are non-Compulsory but are recommended to populate where possible (rather than populating manually 'one-by-one' in the system):

Tax Number – Populate with the customer’s Tax number.  The tax number is stored in the customer master file.

BI Code - Populate with the BI code the customer is assigned to. This can be changed after import.

Price Category ID - If you wish to have a price Category for items for customers, please select it here.

Bank Sort Code, Bank A/C, IBAN, BIC / Swift - Populate your customers' bank details here if you wish.

Customer Details:

Address 1 – Populate with address line 1 of the customer.

Address 2 – Populate with address line 2 of the customer.

City – Populate with the city of the customer.

County/State – Populate with county/state of the customer.

Post Code – Populate with the post code of the customer.

Country – Populate with the country of the customer.

Phone – Populate with the phone number of the customer.

Fax – Populate with the fax number of the customer.

Email – Populate with the email address of the customer.  The email address is the address which the system will default to when emailing documents.


5.2 Suppliers


Code (Compulsory Field) – Populate with the supplier code. The supplier code cannot be amended after import. It is recommended that you use alphanumeric codes like ABC001, EDF123 which facilitates easier searching for users inputting, searching for transactions and records and running reports. Avoid the use of special characters in the code and the description of the suppliers.

Name (Compulsory Field) – Populate with the supplier name.

GL Sales Account (Compulsory Field) - If the sales account associated with this supplier is different than the default sales account, please nominate it.

GL Control Account (Compulsory Field) - If multiple control accounts are used for debtors, please make sure that each supplier has the appropriate control account nominated. This field cannot be changed after the import.

Currency Code (Compulsory Field) – Populate with the supplier currency code. If currency code is not populated the base currency code (default currency) is applied. The currency code cannot be amended after import.

Bank Code (Compulsory Field) – Populate with the GL bank account number associated with the supplier. If a bank code is not assigned a default bank code from supplier defaults is assigned.

Tax Code (Compulsory Field) – Populate with the Tax code assigned to the supplier. If a Tax code is not assigned the importer will assign the default tax code from supplier defaults

The following fields are non-Compulsory but are recommended to populate where possible (rather than populating manually 'one-by-one' in the system):

Tax Number – Populate with the supplier’s Tax number. The tax number is stored in the supplier master file.

BI Code - Populate with the BI code the supplier is assigned to. This can be changed after import.

Price Category ID - If you wish to have a price Category for items for suppliers, please select it here.

Bank Sort Code, Bank A/C, IBAN, BIC / Swift - Populate your suppliers' bank details here if you wish.  This is particularly helpful if using the Supplier Batch Payments feature.

Supplier Details:

Address 1 – Populate with address line 1 of the supplier.

Address 2 – Populate with address line 2 of the supplier.

City – Populate with the city of the supplier.

County/State – Populate with county/state of the supplier.

Post Code – Populate with the post code of the supplier.

Country – Populate with the country of the supplier.

Phone – Populate with the phone number of the supplier.

Fax – Populate with the fax number of the supplier.

Email – Populate with the email address of the supplier. The email address is the address which the system will default to when emailing documents.


5.3 Non Stock/ Service Items

Code (Compulsory Field– Populate with the item code.  The Non-Stock/Service code cannot be amended after import. It is recommended that you use alphanumeric codes like ABC001, EDF123 which facilitates easier searching for item inputting in item transactions like invoices. Avoid the use of special characters in the code and the description of the items.

Name – Populate with the item description. This can be edited at any time.

GL Purchase Account  Nominate the purchase GL account associated with the item; if there isn’t one, nominate the default.

GL Sales Account - Nominate the sales GL account associated with the item; if there isn’t one, nominate the default.

Default Tax ID – Nominate the Tax code to be applied to the item.


5.4 Stock Items 


Item Code (Compulsory Field) – Populate with item code.  The Stock code cannot be amended after import. It is recommended that you use alphanumeric codes like ABC001, EDF123 which facilitates easier searching for item inputting in item transactions like invoices. Avoid the use of special characters in the code and the description of the items.

Name – Populate with the item description. This can be edited at any time.

GL Purchase Account  Nominate the purchase GL account associated with the item; if there isn’t one, nominate the default.

GL Sales Account - Nominate the sales GL account associated with the item; if there isn’t one, nominate the default.

Default Tax ID – Nominate the Tax code to be applied to the item.

Stock movements and Stock Control GL - Nominate the GL account defaults for Stock Movement in the P&L and Stock Control in the Balance Sheet. These can be the same as the ones nominated in the Required System Accounts.

Default Location ID – Populate with the stock location applicable to the item. Select this from the locations nominated into the dropdown list. A minimum of one location is required in the system.

Default Sub-Location – Populate with the stock sub-location applicable to the item. Select this from the sub-locations nominated in the dropdown list. A minimum of one sub-location is required in the system.

Item Cost – Populate with the cost that the item was purchased for. Item cost can be amended after import.

Active – Select from the dropdown the status of the stock item. This can be amended after items have been imported.


5.5 Locations and Sub-Locations

Nominate the existing item Locations and Sub-Locations. This will allow an accurate record to kept of your stock items. Locations and sub-locations can be updated after the import has been done. The location and sub-locations in this list will appear in the relevant dropdown lists of the stock items.

5.6 Item Groups and Sub-Groups

Nominate the groups and subgroups of items that stock items should be divided into.