Introduction

The Data Importer uses predefined Excel templates to clear or add data to the system. The Import Templates are available in Office 97-2003 and Office 2007 formats. 

The Data Importer can import: 

• Static information such as Chart of Accounts, BI Codes, Banks, and Items.
• Existing Supplier (Vendor) or Customer records for company setup, including codes, names, addresses, contacts, and bank details. 
• Transactional information such as invoices or payroll journals.

Chart of Accounts Import

The Data Importer for the Chart of Accounts lets you import the following: 

• GL Categories
• GL Sub Categories
• GL Accounts
• Tax Codes
• System Accounts 
• System Defaults 

There are two different options available in the Chart of Accounts importer:

• Import Chart of Accounts: This completely replaces the existing Chart of Accounts, deleting all data and transactions.
• Add to Chart of Accounts: This adds a set of new codes to the existing Chart of Accounts, keeping all data and transactions.

Designing a Chart of Accounts

A COA must be tailored to your industry-specific needs. For your reference, here are some generic examples relating to common industries:

• Hospitality COA.xlsx
• Not for profit COA.xlsx
• Renewables COA.xlsx
• Tech COA.xlsx
• Wealth Management COA.xlsx

Guidelines for successful importing 

Check database setup

Use separate templates for each company

You must download the Import Templates from each company individually. We do not recommend importing data using a template from a different company as this company may have a different setup. 

Use newly downloaded templates each time

When adding to existing data, use a newly downloaded template to import as it will contain any changes made in the system, for example, updates to the General Ledger codes, Supplier defaults, or Customer information. Make sure you download the Import Templates from the correct database.

See: 

 26.1 - Getting Started with Data Importers

26.2 - Designing a Chart of Accounts

26.3 - Importing a Chart of Accounts

BI Code Importer‍ 

Bank Accounts Importer‍ 

Customer Data Importer‍ 
Supplier Data Importer‍ 

Items Data Importer ‍ 

Trial Balance and Banks Importer‍ 

Opening Financial Position Importer‍ 

Transactions Data Importer‍ 

Preparing Templates

When completing a worksheet, always read the instructions provided.

1. Go to Setup > Data Importer > Start > Chart of Accounts.
2. Click Next for either:
   • Import Chart of Accounts: This lets you download an empty file. Importing it will completely replace the existing Chart of Accounts, deleting all data and transactions. This is useful if you want to completely replace the existing chart of accounts with your version from another system. Either choose a blank template for first imports or a prepopulated template to view and check existing customer data. 
   • Add to Chart of Accounts: This lets you download a copy of your existing chart of accounts to use as a basis for your changes. You can add new codes to the existing Chart of Accounts while keeping all data and transactions intact. 
     
     

3. Click Download File. Save the file as a Microsoft Excel Macro-enabled file (.xlsm format). 
     
4. Open the file. If Macros are not enabled, click Enable Content.
    
    
5. Add the accounts needed, ensuring that you populate all mandatory fields, following the Guidelines for Data Entry below. 
6. For details on each worksheet, see the relevant section of this article.
   • Import Chart of Accounts: Using this will completely remove all existing data, including system accounts and defaults, so complete and fully check all worksheets in the template including:
     • GL Categories
     • GL Sub Categories
     • GL Accounts
     • Tax Codes
     • System Accounts Check: In this worksheet set up all the system accounts that must be present before you can use the system, such as control and tax accounts.
     • System Defaults Check: In this worksheet select all the default settings for customers, suppliers, items, and general ledger accounts.
   • Add to Chart of Accounts: This lets you import additional codes. Leave any unused sheets blank. This contains the following worksheets: 
     • GL Categories
     • GL Sub Categories
     • GL Accounts
     • Tax Codes
7. Save the file.

Guidelines for Data Entry

Follow these guidelines before you import the data. The data must be prepared appropriately before attempting to import.

General:

• The import tool cannot accept data prepared from any other workbook format. It must be downloaded from the Setup > Data Importer. 
• Download the template from the entity that you want to import data into.
• Complete each field according to any requirements provided in the worksheet.
• Do not change column headings or worksheet names. 
• Do not add or delete any columns in the existing worksheets.
• Do not leave any mandatory columns or rows blank.
• Where there are dropdowns, always select an option.  

Formatting non-numerical data: 

• Do not use special characters (e.g., apostrophes, ampersands, accents, etc.).
• Save non-numerical data according to the requirements example, for example, save dates using the date format.
• Ensure that dates are valid.

Formatting numerical data: 

• Use the ‘Paste Values’ option when pasting data into the template. This will eliminate any formulas used to calculate those values.
• For negative financial figures, use a minus sign.
• Values must be two decimals. Round the values to two decimals before pasting them into the template (use the =ROUND () Excel formula).
• Ensure that there are no totals added to the bottom of any worksheet.
• Ensure that the financial information is accurate.  

General Ledger Categories Template

Fields marked with an asterisk (*) on the column header are mandatory.

• Category Code*: Use numbers and/or letters up to 50 characters. You can use a dash (-), underscore (_), or full stop (.) as separators within the code but not at the start of the code. Spaces are not permitted.
• Category Description*: Use numbers and/or letters up to 50 characters. Spaces are permitted.
• Reporting Sort Order*: 
  • Import Chart of Accounts: Only use this in the Import Chart of Accounts template. Use only numbers to indicate category order for your reports (Profit and Loss, Income and Expenditure Statement, or Balance Sheet). 
  • Add to Chart of Accounts: The reporting sort order will be defined on import when any new categories will be added sequentially after the existing categories in the system.
• Category Type*: The category type determines where the accounts linked to this Category will appear in the financial reports, such as Profit & Loss, or Balance Sheet. Select one of the following:
  • Operating Revenue
  • Non Operating Revenue
  • Direct Costs
  • Non Operating Expenses
  • Non Current Assets
  • Current Assets
  • Current Liabilities
  • Non Currently Liabilities
  • Equity and Reserves

At least one account must be set as Equity and Reserves. Do not edit values or add any new entries to the list.

GL Sub Categories Template

Fields marked with an asterisk (*) on the column header are mandatory.

• Sub Category Code*: Use numbers and/or letters up to 50 characters. You can use a dash (-), underscore (_), or full stop (.) as separators within the code but not at the start of the code. Spaces are not permitted.
• Sub-Category Description*: Use numbers and/or letters up to 50 characters. Spaces are permitted.
• Reporting Sort Order*: 
  • Import Chart of Accounts: This is only used in the Import Chart of Accounts template. Use only numbers to indicate in what order categories should appear in your reports (Profit and Loss/ Income and Expenditure Statement or Balance Sheet).
  • Add to Chart of Accounts: The reporting sort order will be defined on import. Any new sub-categories will be added sequentially after the existing sub-categories in the system.

GL Accounts Template

Fields marked with an asterisk (*) on the column header are mandatory. 

• Category Description*: Enter the GL Category Description. This must already exist in the system or be present in the GL Category worksheet and must have the Category Type of "Equity and Reserves".
• Sub-Category Description*: Enter the GL Sub Category Description. This must already exist in the system or be present in the GL Sub Category worksheet.
• GL Acc Code*: Use numbers and letters, up to 50 characters. You can use a dash (-), underscore (_), or full stop (.) as separators within the code but not at the start of the code.
• GL Acc Name*: Use numbers and letters, up to 50 characters. You can use a dash (-), underscore (_), or full stop (.) as separators within the code but not at the start of the code.
• System Account*:
  • Import Chart of Accounts: Do not edit this field. This field will be populated automatically from the System Account Check Sheet. All accounts will be created as posting accounts unless otherwise specified in the System Accounts Check
  • Add to Chart of Accounts: Select the Account type from the list. Do not add or edit entries in the list.
    • Posting
    • Cost Accrual Account
    • Deferred Revenue Account
    • Prepayment Account
    • Revenue Accrual Account
    • Intercompany Creditors Control
    • Intercompany Debtors Control
    • Trade Creditors Control
    • Trade Debtors Control

Tax Codes Template

Fields marked with an asterisk (*) on the column header are mandatory.

• Tax Code*: Use numbers and letters, up to 50 characters. You can use a dash (-), underscore (_), or full stop (.) as separators within the code but not at the start of the code.
• Tax Code Description*: Use numbers and letters, up to 50 characters. You can use a dash (-), underscore (_), or full stop (.) as separators within the code but not at the start of the code.
• Rate*: Use a number only without a .percentage sign. It can be up to one decimal place.
• Goods or Services: Select ‘Goods’ or ‘Services’ from the dropdown. This will default to services if left blank.
• Standard:(Only used in the Import Chart of Accounts template, not the Add to Chart of Accounts template.) Enter ‘Yes’ to set the VAT code as Standard. You can only set one VAT code as Standard. This will default to "No" if left blank.
• Exclude From VAT Return: Enter ‘Yes’ to exclude transactions with this VAT code from the VAT Return. This will default to 'No' if left blank.
• EU Trade: Enter ‘Yes’ to set the VAT code to 'EU Trade & Reverse Charge'. This will default to 'No' if left blank.
• Partial Reclaim Rate: Use numbers only without a .percentage sign. Enter Partial Reclaim Rate for a VAT code. This refers to the rate that you can reclaim.

System Accounts Check Template

Only the Import Chart of Accounts template contains this worksheet, not the Add to Chart of Accounts template.

System, Posting Default, and Control Accounts are used for several automated posting routines in the system.

• Required: You must link accounts marked as 'Required' to a GL code to complete your import.
• Multiple GL Allowed: You can set up multiple GL codes for accounts marked as 'Multiple GL Allowed'. To do this, copy the existing line from the template and insert the copied cells into a new row below. 
• Link to GL Code: Do not edit any fields other than 'Link to GL Code'. Enter the GL Account Codes exactly as they appear in the GL Accounts Sheet. There can be no duplication of GL Codes.
• Check: Any errors or missing mandatory accounts will be highlighted as 'Invalid'. Link a valid GL code to any 'Invalid' accounts before importing your data.

System Defaults Check Template

Only the Import Chart of Accounts template contains this worksheet, not the Add to Chart of Accounts template.

System Defaults are used to populate mandatory fields when creating new Customers, Suppliers, and Items. They can be changed later in the system, if necessary. The system defaults check worksheet shows if you have mapped these accounts correctly. Errors will show if you have not.

• Required: You must link system defaults marked as 'Required' to a valid code (GL Account or VAT Code) to complete your import.
• Check: Any errors or missing mandatory accounts will be highlighted as 'Invalid'. Link a valid GL code to any 'Invalid' accounts before importing your data. 
• Link to Code: Do not edit any fields other than 'Link to Code'. Enter the Code exactly as they appear in either the GL Accounts or VAT Codes Sheets

GL Codes linked to the default bank accounts (customer and/or supplier defaults) will be created as bank accounts automatically in the system. If you need additional Bank Accounts, you can import them using the Bank Importer, or create them in the system (Banks > Add New Bank).

Import the Template

Step One: Import the Data File

1. Go to Setup > Data Importer > Start > Chart of Accounts.
2. Click Next.
   
   
   
    
3. Click Browse to find your saved template.
4. Click Import.

Step Two: Validation

The import file will be validated.

1. If there are any errors, an error message will appear, along with an error file. Download this file. It includes a new sheet, Import Errors, and a copy of the import file. This shows a summary of all errors found during validation, together with a link to the fields causing the error and the actual error message. 
2. Click on each link to see details of the error, cells causing the error will be highlighted in red with a note showing further details (hover over the highlighted cell to see the detailed message).
3. Correct all errors and re-import the file as described in step two above. You can make corrections directly in the error file.

When all errors are corrected, confirm the following:

Import Chart of Accounts:

The system will give you a final warning that all data is about to be permanently deleted. Only proceed with this option if you are happy to delete all the following:

• Transactions
• Customer and Supplier Accounts
• Items
• Categories
• Sub-Categories
• GL Accounts
• Tax Codes
• Dimensions and Elements
• BI Codes
• System Accounts 
• Default Settings

Next you will be asked to enter your login details or, if you use SSO, follow the process outlined here:

1. Click Verify Identity.
    
2. Enter the OTP that was sent to your email.
   
3. Click Continue to confirm the import.
   

Add to Chart of Accounts:

No data is deleted when using the Add to Chart of Accounts option. If you include any existing data in the template, the template will not be imported, and duplicated accounts will be highlighted in the error file.

Step Three: Check the Imported Data in the system

You will receive a confirmation message if the import is successful. Close the importer and check the imported data in the system, including system accounts and defaults. It is important to check that you are happy with your imported data before starting to use the system. To do this download a 

Check that the suppliers are correct by downloading a prepopulated template. If you need to make changes to a small number of fields, you can do this directly in the system. However, if you need to make changes to all accounts, it may be better to re-import the whole Chart of Accounts, but you can only do this if you have not started to use the system as all data will be deleted when you choose this option.

FAQs

I can't see the error messages in the error file.

You can control note's appearance in Excel:

1. Go to File > Options > Advanced.
2. Scroll down to the display settings. We recommend selecting the Indicators only, and comments and notes on hover option. This lets you see detailed notes on errors when you hover over highlighted cells.
   
   

How do I make changes to existing accounts?

Currently, there is no way to import changes to existing codes via the data importer. If you need to make changes to a small number of fields, you can do this directly in the system. However, if you need to make changes to all accounts, it may be better to re-import the whole Chart of Accounts (bearing in mind that all data, including any transactions entered, will be deleted at this point).