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.

• Unreconciled Bank Transactions:  This allows you to import all unreconciled bank transactions.
• Customer Open Items: This allows you to import open transactions.
• Supplier Open Items: This allows you to import open transactions.
• Opening Stock Position

Guidelines for successful importing

Check database setup

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.10 - Importing Opening Financial Position Data

BI Code Importer‍ 

Bank Accounts Importer‍ 

Customer Data Importer‍ 
Supplier Data Importer‍ 

Items Data Importer ‍ 

Trial Balance and Banks Importer‍ 

Chart of Accounts Data Importer‍ 

Transactions Data Importer‍ 

Prepare the Template

Make sure to read the information provided at the top of each worksheet/column. It provides supplementary information on how to complete the data. 

1. Go to Setup > Data Importer > Start > Opening Financial Position.
2. Click the download link and save the file as a Microsoft Excel Macro-enabled file (.xlsm format).
   
   
    
3. If Macros are not enabled, click Enable Content.
   
   
    
4. Fill in the required fields, following the Guidelines for Data Entry, below. It is not mandatory to complete all worksheets. See the sections below relating to each of these for guidance:
   • Unreconciled Bank Transactions lets you import all unreconciled bank transactions.
   • Supplier (AP) Open items and Customer (AR) Open items let you import outstanding balances or detailed open transactions.
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, select data from the list.  

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.  

Unreconciled Bank Transactions Template

The Bank Unreconciled Transactions allows you to import all unreconciled Bank transactions. These can be imported into a suspense account that will not affect the balance on the bank account itself (as this will already be included in the Trial Balance figure). 

You can also select another GL account (for example an opening balance account) which will affect the balance on the GL account.

 

Complete the following:

• Bank Code (Mandatory): From the dropdown, select the GL bank account.
• Reference (Mandatory): Enter the transaction reference. This will appear in the bank reconciliation screen and cannot be blank. The reference will usually be a receipt or payment reference number.
• Date (Mandatory): Populate with the transaction date. This must be before the go-live date.
• Description (Mandatory): Populate with a transaction description. This assists with bank reconciliation.
• Bank Amount (Mandatory): Enter the transaction amount in the bank account currency. Enter payments as negative values and receipts as positive values. 
• System Currency Amount (Mandatory): Enter the value of the transaction in base currency.
• GL Code: The GL code field is not mandatory. We recommend leaving it blank to avoid affecting the bank GL balance in the general ledger. The system will use the import suspense account for the contra that performs the triangulation.

Customer (AR) Open Items Template

All fields are mandatory and there must be no blank values. Complete the following:

• Customer Code (Mandatory): Nominate the Customer Account from the dropdown.
• Reference (Mandatory): Populate with the transaction reference. The reference will appear in the customer ledger. This will be an invoice number, credit note, or payment reference. If you are only bringing in open balances, use a reference to recognise this.
• Date (Mandatory): Populate with the transaction date. This could be the original date or the date of the opening trial balance. This must be before the go-live date. 
• Description (Mandatory): Populate with the transaction description.

Next, enter the financial figures. All values must be rounded to two decimal places (run the ROUND formula to check) and there must be no rows with zero values.

• Customer Balance (Mandatory): Enter the invoice/payment amount in the customer account currency. Outstanding invoices should be positive. Credit notes or outstanding payments should have negative values.
• Base Currency Balance (Mandatory): Enter the invoice or payment amount in entity base currency. 

For base currency customers, both amounts will be the same. For foreign currency customers, the amount will differ. Corresponding Customer and Base Currency Balances must have the same signs. 

Supplier (AP) Open Items Template

All fields are mandatory and there must be no blank values. Complete the following:

• Supplier Code (Mandatory): Nominate the Supplier Account from the dropdown.
• Reference (Mandatory): Populate with the transaction reference. The reference will appear in the supplier ledger. This will be an invoice number, credit note, or payment reference. If you are only bringing in open balances, use a reference to recognise this.
• Date (Mandatory): Populate with the transaction date. This could be the original date or the date of the opening trial balance. This must be before the go-live date. 
• Description (Mandatory): Populate with the transaction description.

Next, enter the financial figures. All values must be rounded to two decimal places (run the ROUND formula to check) and there must be no rows with zero values.

• Supplier Balance (Mandatory): Enter the invoice or payment amount in the customer account currency. Outstanding invoices should be negative. Credit notes or outstanding payments should have positive values.
• Base Currency Balance (Mandatory): Enter the invoice or payment amount in entity base currency. 

For base currency suppliers, both amounts will be the same. For foreign currency suppliers, the amount will differ. Corresponding Supplier and Base Currency Balances must have the same signs. 

Opening Stock Position Template

Complete the following:

• Item ID (Mandatory): Nominate the Stock Item from the dropdown.
• Location:  Nominate the location of the item from the dropdown. You can nominate a stock item on two rows if the location ID is different.
• Sub-Location: Nominate the sub-location of the item from the dropdown.
• Quantity: Enter the quantity of the stock item that is available at that location. This will represent the quantity on-hand in the system.
• Average Cost

Import the 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. 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. 

Import Unreconciled Bank Items, Customer Open Items, and Supplier Open Items

Once you have imported the completed data file, you are ready to apply the data to the system via the Banks Import and Open Items Import. 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. 

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

Unreconciled Bank Items

1. Go to Setup > Data Importer > Banks Import > Unreconciled Bank Items.
2. Unreconciled items you want to import appear on the screen. If an error occurs with the data, an error message will appear on the screen. 
   • You can click Edit to make changes.
   • If any errors cannot be corrected on screen, you must first go to Validation > Clean Up and click on Clean Up. This will remove any unneeded temporary data, avoiding the risk of duplicating data from a previous import into a subsequent one. Then correct in the original Excel template, generate the CSV file again and follow the upload and import steps again. Repeat until the import passes validation.
3. To ensure that no bank balances are affected in the system, keep 9999IMP as the importing suspense account. 
4. To import, click Apply. If successful, you will receive confirmation.

Customer Open Items

1. Go to Setup > Data Importer > Open Items Import > Customer Open Items.
2. Customer Open Items you want to import appear on the screen. If an error occurs with the data, an error message will appear on the screen. 
   • You can click Edit to make changes.
   • If any errors cannot be corrected on screen, you must first go to Validation > Clean Up and click on Clean Up. This will remove any unneeded temporary data, avoiding the risk of duplicating data from a previous import into a subsequent one. Then correct in the original Excel template, generate the CSV file again and follow the upload and import steps again. Repeat until the import passes validation.
3. If you want to clear existing customer open items, check Clear existing customer open items. If you do not check this, the importer will add to the existing open items.
4. If the data provided has dates in US format instead of European format, check Data Format to change to European format. 
5. Suspense account is checked by default. To prevent control account modification, all opening items are posted to a Suspense Account whose code will appear on the screen. To change the balance to post to a Control Account, uncheck.
6. To import, click Apply. If successful, you will receive confirmation.

Supplier Open Items

1. Go to Setup > Data Importer > Open Items Import > Supplier Open Items.
2. Supplier Open Items you want to import appear on the screen. If an error occurs with the data, an error message will appear on the screen. 
   • You can click Edit to make changes.
   • If any errors cannot be corrected on screen, you must first go to Validation > Clean Up and click on Clean Up. This will remove any unneeded temporary data, avoiding the risk of duplicating data from a previous import into a subsequent one. Then correct in the original Excel template, generate the CSV file again and follow the upload and import steps again. Repeat until the import passes validation.
3. If you want to clear existing supplier open items, check Clear existing supplier open items. If you do not check this, the importer will add to the existing open items.
4. If the data provided has dates in US format instead of European format, check Data Format to change to European format. 
5. Suspense account is checked by default. To prevent control account modification, all opening items are posted to a Suspense Account whose code will appear on the screen. To change the balance to post to a Control Account, uncheck.
6. To import, click Apply. If successful, you will receive confirmation.

Final Clean Up

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

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.