Items Data Importer

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

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 data or import 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 when setting up a company in the system, 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 Items lets you import the following: 

  • Non-Stock Items
  • Service Items
  • Stock Items
  • Item Locations 
  • Item Sub Locations
  • Item Groups 
  • Item Sub Groups

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.

Use separate templates for each company

You must download the Import Templates 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. 

Use newly downloaded templates each time

Use a newly downloaded template each time 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.8 - Importing Items

BI Code Importer‍ 

Bank Accounts Importer‍ 

Customer Data Importer‍ 
Supplier Data Importer‍ 

Chart of Accounts Data Importer‍ 

Trial Balance and Banks Importer‍ 

Opening Financial Position Importer‍ 

Transactions Data Importer‍ 

Delete

Prepare the Template

When completing a worksheet, always read the instructions provided.

  1. Go to Setup > Data Importer > Start > Items.
  2. Click the download link and save the file as a Microsoft Excel Macro-enabled file (.xlsm format).   


     
  1. Open the file. If Macros are not enabled, click Enable Content.
      
    Graphical user interface, application, Word Description automatically generated   
  2. Fill in the required fields, following the Guidelines for Data Entry, below. For details on each worksheet, see the relevant Items template section in this article. Mandatory fields are marked with an asterisk (*) on the column header, all other fields can be left blank. 
    • Non-Stock Items
    • Service Items
    • Stock Items 
    • Locations
    • Sub-Locations
    • Item Groups
    • Item Sub-Groups
  3. 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

Non-Stock and Service Items templates



Non-Stock and Service Items have the same data fields. Complete the following:

  • Item ID (Mandatory): Populate with the item code.  You cannot change the Non-Stock/Service after import. It is recommended that you use alphanumeric codes like ABC001, EDF123 for searchability of the data later. Do not use special characters in the code or description.
  • Description: Populate with the item description.  
  • Default Tax ID: Nominate the Tax code for this item.
  • Purchases GL Account: Nominate the purchase GL account associated with the item. If there is not one, nominate the default.
  • Sales GL Account:  Nominate the sales GL account associated with the item. If there isn’t one, nominate the default.
Delete

Stock Items template

 

Complete the following:
  • Item ID (Mandatory): Populate with the item code.  You cannot change the Stock code after import. We recommend using alphanumeric codes like ABC001, EDF123 for greater searchability later. Do not use special characters in the code or description.
  • Description: Populate with the item description. 
  • Item Group ID, Item Sub-Group ID:   Nominate groups and subgroups to divide stock items into. 
  • Item Cost: Populate with the purchase cost of the item. You cannot change the Item Cost after import.
  • Default Tax ID: Nominate the Tax code for this item.
  • Purchases GL Account: Nominate the purchase GL account associated with the item. If there is not one, nominate the default.
  • Sales GL Account:  Nominate the sales GL account associated with the item. If there is not one, nominate the default.
  • Stock Movement Account, 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.
  • Locations and Sub-Locations:  Nominate the existing item Locations and Sub-Locations. This will allow an accurate record to be kept of your stock items. The location and sub-locations in this list will appear in the relevant dropdown lists of the stock items.
    • Default Location ID: Populate with the stock location applicable to the item. Select this from the locations nominated in the dropdown list. You must enter a minimum of one location.
    • Default Sub-Location ID: Populate with the stock sub-location applicable to the item. Select this from the sub-locations nominated in the dropdown list. You must enter a minimum of one sub-location.
  • Active: Select the status of the stock item from the dropdown. 
Delete

Import Template

Delete

Info: Group Companies

Create individual templates for each subsidiary that you want to import into. Do not import Items into the Group Consolidation Entity.


Step One: Generate the Data File

When you have completed all worksheets correctly, go to the Generate Data File tab and click Generate Data Upload File to generate the CSV file.


This is the first stage of error validation. If there are errors, correct them and click Generate Data Upload File again.

You will find the CSV file in the same location as the macro-enabled Excel file. This file will be used to import all the data you added. During upload the system will check for format and that the CSV file is in the same location as the XLS file. You will receive confirmation if this is the case. 


Step Two: Import the Data File

  1. Go to Setup > Data Importer > Start > Items.
  1. Click Browse to find your saved template.



  2. Click Upload Data. If the file could not be uploaded, you will receive an error message. Make corrections in the original Excel file, generate a csv file again and upload it as before.
Delete

Import Locations, Sub Locations, Groups, Sub Groups, Defaults, and Items

Navigating Items Import

Once you have imported the completed data file, you are ready to apply the data to the system. The Items Data Importer lets you import all information relating to stock items including locations, sub-locations, groups, sub-groups, defaults, and items.


When a step is successful you will be prompted to move to the next step. You can skip any step by clicking Skip Step. Steps are colour-coded according to their status:

  • Green: Step completed.
  • Red: Step not completed.
  • Greyed out: Step unavailable for selection because the data is not included in the Import Template.

If you log out and log back in again to continue the import, you do not have to upload the import file again as it is retained in the importer. 

Locations, Sub-Locations, Groups, and Sub Groups Import

These all work in a similar manner.

  1. Go to Setup > Data Importer > Items and select the desired import option.
  2. The import data will appear on the screen. 
    • To amend any data, click Edit.
    • Clear existing data: If you wish to clear existing data, check this box. Leave unchecked to add to the data already in the system.
  3. To import, click Apply. If successful, you will receive confirmation.

Item Defaults  

  1. Go to Setup > Data Importer > Items > Stock Item Defaults.
  2. Stock item defaults are not included in the spreadsheet for import so you must set them now, but you can amend them at any time.
  3. Complete all mandatory fields using the dropdowns. 
  4. To import, click Apply. If successful, you will receive confirmation. 

Items  

Delete

Info

When importing stock items, locations, sub-locations, GL codes linked to the item must already exist. 

  1. Go to Setup > Data Importer > Items > Items.
  2. Stock items you wish to import are displayed on the screen.  
    • To amend any data, click Edit.
    • Clear existing stock items: If you wish to clear existing stock items, tick this box. Leave unticked to add to the groups already in the system.
  3. To import, click Apply. If successful, you will receive confirmation. 


Close the importer and check the imported data in the system. Items can be updated if needed in the system, they can also be deleted if there are no transactions saved against them.

Delete

Clean Up

Delete

Warning

Always perform cleanup after a new import. This avoids the risk of duplicating data from the previous import in the new import. Clean-up should only be completed when a data import is complete and the files are no longer required.


If you are satisfied with your import and would like to clear any previously uploaded template before future imports:

  1. Go to Data Importer > Validation > Clean Up.
  2. Click Clean Up.Graphical user interface, text, application, emailDescription automatically generated 
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
Delete