BI Codes Data Importer

How to import dimensions, elements, and BI Codes

Written By Grainne Reidy (Super Administrator)

Updated at June 25th, 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 BI Codes importer will allow you to import dimensions, elements, and BI codes. You can choose from two different options:

  • Import BI Code Structure: This completely replaces the existing BI Code Structure (dimensions, elements, and BI Codes), deleting all transactions and adding a default BI code mapped to GL Codes.
  • Add to BI Code Structure: This adds additional dimensions, elements, or BI Codes to the existing BI Code Structure, keeping all data and transactions. It can also change the default BI code mapped to GL Codes. 

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: Data deletion

Using the Import BI Code structure template will completely remove all existing dimensions, elements, and or BI codes and all transactions in the database.

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.4 - Importing BI Codes

Chart of Accounts Data 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

Delete

Info

If you have "Centrally Manage Dimensions and Elements" checked in the Consolidation Manager, download the template from the Group Consolidation Entity. This way any new dimensions and elements will be available for selection at subsidiary level.

See: Using Multi-Company Consolidation‍ 


When completing a worksheet, always read the instructions provided in each worksheet.

  1. Go to Setup > Data Importer > Start > BI Codes.
  2. Click Next for either:
    • Import BI Code Structure: This completely replaces the existing BI Code Structure (dimensions, elements, and BI Codes), deleting all transactions and adding a default BI code mapped to GL Codes. 
    • Add to BI Code Structure: This adds additional dimensions, elements, or BI Codes to the existing BI Code Structure, keeping all data and transactions. It can also change the default BI code mapped to GL Codes.

       
  1. Click the download link and 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. Both template types contain the same worksheets. For details on each worksheet, see the relevant section below:
    • Dimensions
    • BI Codes
    • GL to BI Mapping (optional)
  4. Save the file.

Mandatory fields are marked with an asterisk (*) on the column header, all other fields can be left blank. 


If you only want to import one type of data (i.e., Dimension, element, or BI Code) then leave all the other sheets blank.

 

Delete

Info

It is possible to relabel BI Codes within the system at Analysis > BI Codes. If you do this, the label ‘BI Codes’ will be updated throughout the system, including in the data importer. 



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

BI Dimensions Template


Enter new dimension names in the headers and add as many element Codes and Names as required. Leave any unrequired columns blank.


Although you can create up to six Dimensions, we recommend creating fewer so that you can grow your coding system later.

Delete

BI Codes Template

 

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


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.

  • BI Code: The BI Code can be made up of its dimension elements separated by a dash, or according to your own coding needs. BI Codes cannot be edited once imported but the Dimension Elements associated with them can be changed. They can only be deleted in the system if there are no associated transactions. If there are associated transactions, they can be deactivated instead.
  • BI Code Description: Populate with BI code descriptions. They can be edited once they have been imported into the system.
  • Active: Select either yes or no.
  • Dimensions 1-6: Populate with the relevant Dimensions if you are using them in the BI Code. You can select both from those already in the system as well as those you entered in the Dimensions worksheet.
Delete

BI to GL Mapping Template

This worksheet is optional. The GL to BI Code mapping worksheet allows you to enforce BI Code use for transactions. The GL Account Code and GL Account Name columns will already be filled with existing GL Codes if present. 

  • In the Require BI Code (optional) dropdown, select yes or no. We recommend selecting yes only for P&L accounts.
  • You have the option to enter a default BI Code for the GL account codes entered in the previous column. We recommend leaving this blank unless you are certain that the same BI Code should always be used.
Delete

Import the Template

Step One: Import the Data File

Delete

Info: Group Companies

If you have "Centrally Manage Dimensions and Elements" checked in the Consolidation Manager, import the template into the Group Consolidation Entity. Then create copies of the template for each entity you want BI Codes in. For each of these templates, delete the Elements you added in the first worksheet as well as any BI Codes that are unneeded in the second template.

See: Using Multi-Company Consolidation‍ 


  1. Go to Setup > Data Importer > Start > BI Codes.
  2. Click Next for the relevant option.


     
  1. Click Browse to find your saved template.



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

Import BI Code Structure Template

If no errors are found with the imported file, the system will give you a final warning that the data is about to be deleted. This means that data will be permanently deleted, please note there is no backup taken, you should only proceed with this option if you are happy to delete the following:

  • All Transactions
  • Dimensions
  • Elements
  • BI Codes

Next you will be asked to either enter your login details.

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 BI Codes in the system. (BI Codes can be checked from Analysis>BI Codes or from Report Manager>Analysis Structure Mapping)

Delete

FAQs

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

You can control the appearance of notes in Excel:

  1. Go to File > Options > Advanced.
  2. Scroll down to the display settings. We recommend you select 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
 

Delete