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
• Customer Open items
• Supplier (Vendor) Open items
• Opening Stock (Inventory) 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: 

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. See the sections below relating to each of these for guidance:
   • Unreconciled Bank Transactions
   • Supplier (AP) Open items
   • Customer (AR) Open items
   • Opening Stock Position
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): Populate with the transaction reference. The reference will appear in the bank reconciliation screen.
• Date (Mandatory): Populate with the transaction date. Dates should be in the European format dd/mm/yyyy. If the date is in US format (mm/dd/yyyy), ensure that you format the date column to the European format before importing.
• Description (Mandatory): Populate with a transaction description.
• Bank Amount (Mandatory): Populate with the transaction amount. If the bank account is a foreign currency bank account, populate with the foreign currency value. If transactions are payments, populate the values as negative figures. Otherwise use positive figures.
• System Currency Amount (Mandatory): Populate with the value of the transaction in the system/base currency.
• GL Code: Leave blank to populate the balance in the system suspense account (recommended). Otherwise populate with another GL account code.

Customer (AR) Open Items Template

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.
• Date (Mandatory): Populate with the transaction date. Dates should be in the European format dd/mm/yyyy. If the dates are listed in US format (mm/dd/yyyy) the system importer process will provide the following checkbox option:

"If the data provided has dates in US format (mm/dd/yyyy) instead of European format (dd/mm/yyyy) please check this box."

• Description (Mandatory): Populate with the transaction description.
• Customer Balance (Mandatory): Populate with the value of the transaction. If the customer is a foreign currency supplier, populate with the foreign currency value. Enter customer invoices as positive values and payments as negative.
• Base Currency Balance (Mandatory): Populate with the value of the transaction in the system currency/base currency. The total of the system currency value for all Customer open items should match exactly to the value for the relevant Accounts Receivable Control account in the imported/to-be-imported Trial Balance.

Supplier (AP) Open Items Template

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.
• Date (Mandatory): Populate with the transaction date. Dates should be in the European format dd/mm/yyyy. If the dates are in US format (mm/dd/yyyy) the system importer process will provide the following checkbox option:

"If the data provided has dates in US format (mm/dd/yyyy) instead of European format (dd/mm/yyyy) please check this box."

• Description (Mandatory): Populate with the transaction description.
• Supplier Balance (Mandatory): Populate with the value of the transaction. If the supplier is a foreign currency supplier, populate with the foreign currency value. Enter supplier invoices as negative values and payments as positive.
• Base Currency Balance (Mandatory): Populate with the value of the transaction in the system currency/base currency. The total of the system currency value for all Supplier open items should match exactly to the value for the relevant Accounts Payable Control account in the imported/to-be-imported Trial Balance.

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

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.

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

Unreconciled bank items are imported to assist you in reconciling your bank account. They do not affect any balance in the system. To ensure un-reconciled transactions do not modify any existing bank balances unreconciled transactions are imported into an Importing Suspense Account. 

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. To view the error detail, hover over the error fields highlighted.
   • To amend any data, click Edit.
3. 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 will appear on the screen. 
   • If an error occurs with the data, an error message will appear on the screen. To view the error detail, hover over the error fields highlighted. To amend any data, click Edit.
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 Debtors (AR) 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 will appear on the screen. 
   • If an error occurs with the data, an error message will appear on the screen. To view the error detail, hover over the error fields highlighted. To amend any data, click Edit.
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 Debtors (AR) 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.

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.