Chart of Accounts Data Importer

Data Importer User Guide Introduction and for importing your Chart of Accounts

Written By Grainne Reidy (Super Administrator)

Updated at March 14th, 2024

Introduction

The Data Importer lets you populate predefined Excel templates and import them into AccountsIQ. It can clear or add to existing system data. 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, VAT (Tax) Codes, and BI Codes.
  • 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.
Delete

Info

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.

Any user can have access to the data importer if their user profile is set with that permission. For more on user permissions, see Managing Group and Entity Users‍. User role then determines the range of available imports. Admin users have access to the full suite, while Users only have access to a subset.


 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.

Guidelines for successful importing 

Check database setup

Before importing data, ensure that the client database has been created correctly using the company setup wizard and that the correct currencies, financial year, and client locale were selected. Verify this in the main system in advance of importing data.
 
Delete

Warning: Follow all steps carefully

When you import the Chart of Accounts, follow all the steps exactly. 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.

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: 

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‍ 

Delete

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.
    • 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.

  1. Check that the relevant download link is selected and click Download File. Save the file as a Microsoft Excel Macro-enabled file (.xlsm format). 
      
  2. Open the file. If Macros are not enabled, click Enable Content.
     
    Graphical user interface, application, Word Description automatically generated 
  3. Fill in the required fields, following the Guidelines for Data Entry, below. 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 contains the following worksheets: 
      • GL Categories
      • GL Sub Categories
      • GL Accounts
      • Tax Codes
        Add the accounts needed, ensuring that you populate all mandatory fields. You can import additional codes to one or more worksheets within the template. Do not delete or change any of the sheets or headings in the template, just leave any unused sheets blank.
  4. 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.  
Delete

General Ledger Categories Template

Graphical user interface, application, table, ExcelDescription automatically generated


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.

Delete

GL Sub Categories Template

Graphical user interface, application, table, ExcelDescription automatically generated


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.
Delete

GL Accounts Template

Graphical user interface, text, application, tableDescription automatically generated


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
Delete

Tax Codes Template

 Graphical user interface, text, applicationDescription automatically generated


Delete

Info

Do not delete Tax Code V99 as it is used for importing purposes. The Standard field must be set to "No" and the Exclude From VAT Return field must be set to "Yes."


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.
Delete

System Accounts Check Template

TableDescription automatically generated


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.

Delete

Info

The Tax Liabilities System Account listed here impacts the VAT Return process.


  • 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.
Delete

System Defaults Check Template

TableDescription automatically generated


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.

Delete

Info

Complete all defaults even if you will not be using them in the system.


  • 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).

Delete

Import the Template

Delete

Info: Group Companies

Create one master template with all COA to import into the Group Consolidation Entity. To import into subsidiaries, this can be copied and, if necessary, edited.


Step One: Import the Data File

  1. Go to Setup > Data Importer > Start > Chart of Accounts.
  2. Click Next.

    Graphical user interface, text, application, emailDescription automatically generated

     
  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 deleted. This means all data will be permanently deleted, you should 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

Enter your login details to confirm the step including the company ID, your username, and password.

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 really important to check that you are happy with your imported data before starting to use the system. 


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, 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.

Delete

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.

    Graphical user interface, text, applicationDescription automatically generated

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). 

Delete