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 for company setup, including codes, names, addresses, contacts, and bank details.
- Transactional information such as invoices or payroll journals.
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 the Trial Balance and Banks lets you import the following:
- Trial Balance
- Opening Bank Balances
Guidelines for successful importing
Check database setup
Warning: During import, log out all other users
You can import data at any time. However, when you are using the Data Importer from Setup > Data Importer, no other user can be logged into the system. If another user enters transactions during import, transactions can become corrupted.
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
When adding to existing data, 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:
Customer Data Importer 
Supplier Data Importer
Chart of Accounts Data Importer
Guidelines for Preparing the Template
When completing a worksheet, always read the instructions provided.
- Go to Setup > Data Importer > Start > Trial Balance and Banks.
- If you want to import movements for the financial year, select the relevant year for which the movements will be imported.
- Click the download link and save the file as a Microsoft Excel Macro-enabled file (.xlsm format).
- If Macros are not enabled, click Enable Content.
- Fill in the required fields, following the Guidelines for Data Entry, below. See the sections below relating to each of these for guidance:
- Trial Balance
- Opening Bank Balances
- Save the file.
Guidelines for Data Entry
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 data 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.
Trial Balance Template
Complete the following:
-
GL Code (Mandatory): Select the GL account that you want to nominate in the Trial Balance. You only need to add accounts with balances.Delete
Info: Retained Earnings
You cannot import values directly into the Retained Earnings control account. Instead, set up a new posting type GL code with the same Category and Sub Category as the GL into which you want to import the retained earnings.
For example, if you want to bring in an opening Retained Reserves balance, set up a new posting GL account called 'Retained Reserves opening balances' and import it into this GL account. When the Financial Year is closed, the profits will be posted to the nominated retained reserves control account.
- Analysis Code: If the GL value is split by BI codes, enter the value of the GL corresponding to each BI code.
-
Dr/(Cr) (Mandatory) and Dr/(Cr) 1-12 (Optional): Enter the opening position in the Dr/(Cr) column. Later when you import this template, you will select the appropriate year. Debit values should be positive and Credit values should be negative. The total value should be zero.
- If you are not importing any earlier financial years, the Dr/(Cr) will be the figure as per the previous year end and the remaining 12 columns will be the trial balance movements per period. For subsequent years you import, enter zeros in the Dr/(Cr) column as previous figures will be carried forward automatically in the system.
- If you want to show annual rather than period movement, enter the total movement for the year in the period 12 column.
- If you don't want any period history, enter an opening Trial balance the Dr/(Cr) column. Then during import, you can specify what period it is for.
Opening Bank Balances Template
This template will update the existing bank accounts and allow you to create new bank accounts in the system. The opening balance needs to correspond with the trial balance values if they are for the same date.
Complete the following:
- GL Code (Mandatory): From the dropdown, nominate the GL A/C that represents the bank account. If you have already nominated it in the system, it will appear here. You must nominate all the bank accounts that you want to be set up in the system.
- Name: Populate with the relevant GL bank account.
- Currency Code (Mandatory): Populate the currency of the bank account. You must create the currency in the company before downloading this template. To create more currencies than the base currency of the system, go to Setup > Codes Maintenance > Currencies and set up the required currencies there.
- Bank Balance: Populate with the bank balance. Ensure that the bank balance is in the currency of the bank account, for example, if it is a US Dollar bank account then the balance needs to be in US Dollars. The US Dollar value then needs to correspond with the base currency value in Column C of the Trial Balance worksheet for that bank account. During import, select the date you want the balance to be for.
Import the Template
Info: Group Companies
Create individual templates for each subsidiary you want to import into. Do not import Trial Balances and Banks 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
- Go to Setup > Data Importer > Start > Trial Balance and Banks.
- Click Browse to find your saved CSV template.
- Click Upload Data.
Step Three: Validation
The import file will be validated. If there are any errors, an error message will appear, along with an error file.
- Download the error 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.
- 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).
- Correct all errors and re-import the file as described previously. You can make corrections directly in the error file.
Import Trial Balance
Navigating GL Import
Once you have imported the completed data file, you are ready to apply the data to the system.
Go to Setup > Data Importer > GL Import > Trial Balance.
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.
Apply Trial Balance Data
Info
GL accounts must already exist in the system before you import a Trial Balance. If GL accounts do not exist, you will receive an error message.
The Trial Balance will appear on the screen.
- Any errors will be displayed, along with an error message.
- You can click Edit to make changes.
- Date: Select the date to import the Trial Balance (in dd/mm/yyyy format).
- You will not be able to import to the retained reserves or sales and purchase tax accounts as these are system control accounts.
- For your retained reserves account, set up a new GL account called retained reserves opening balance account and import this figure to this GL account.
- Foy your sales and purchase tax accounts, sum their balances and import this figure to your tax liability account.
To import, click Apply.
DeleteClean Up
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:
- Go to Data Importer > Validation > Clean Up.
- Click Clean Up.
FAQs
I can't see the notes in the error file.
You can control the appearance of notes in Excel:
- Go to File > Options > Advanced.
- 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.
