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.
  
  

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

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‍ 

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

3. Open the file. If Macros are not enabled, click Enable Content.
     
      
4. 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
5. 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.  

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.

Stock Items template

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

Import Template

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.

Step Two: Import the Data File

1. Go to Setup > Data Importer > Start > Items.

2. Click Browse to find your saved template.
   
   
   
   
3. 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.

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  

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.

Clean Up

1. Go to Data Importer > Validation > Clean Up.
2. Click Clean Up. 

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.