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
Info
We highly recommend that the Trial Balance is imported together with the opening financial position template to avoid differences between the General ledger, and purchases and sales listings. See Opening Financial Position Data Importer for more details.
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.
- Before you can import the trial balance, all general ledger codes and BI Codes must be set up in the system.
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:
26.1 - Getting Started with Data Importers
26.9 - Importing Trial Balance and Banks Data
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.
- From the dropdown, select the template type:
- If you want to import only closing trial balance, select None.
- If you want to import opening trial balance and monthly movements, select the financial year that the financial movements will be imported into. If you want to bring monthly movements from multiple financial years, download separate templates for each financial year. The earliest year will be populated with the opening Trial Balance.
- 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:
- 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): Enter the GL accounts to include in your trial balance. Only add accounts that have balances or movements.
Note the following:- Do not use VAT purchase or VAT sales GLs. All values must be put against the VAT liability control account.
- Do not use the Retained Earnings Control Account. This is a system control account which will be used only for closing the financial year and auto-generating year end journals. To bring in the retained earnings from prior periods, you have two options:
- Use a designated GL account of type ‘balance sheet’ where the balance will remain as entered.
- Use one of type ‘P&L’ where the balance will be transferred into the retained earnings control account once the first year end routine is performed.
- Analysis Code (Optional): If the GL account requires a BI Code, select it from the dropdown. If the GL account requires multiple BI Codes, create multiple lines.
- There must be no formulas in the template. Only paste values.
- Each column must total zero.
- All values must be rounded to two decimal places.
-
Dr/(Cr) (Mandatory): In Column C, enter your opening balances from the previous year. All values must be in base currency. The debit value should be positive, and the credit value should be negative. There must be no blank cells in column C. If there is no balance, enter zero.
- If you are importing multiple financial years, only populate column C in the earliest financial year template. All subsequent years should then have zeros in this column. Later on, when you import this template, you will select the appropriate year.
-
Dr/(Cr) 1-12 (Optional):
- If you want to show annual rather than monthly movements, enter the total annual movement in the column O (the period 12 column).
- If you are bringing monthly movements for the year prior to going live in the system, enter these monthly movements in columns D-O. The total of the opening balance and monthly movements will give the closing balance as of the period before going live. For monthly movements in columns D-O if there are no values leave them blank.
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
This file will be used to import all the data you added.
- Before generating the CSV file, save the template as a separate file and delete all lines from the opening bank balances sheet. If you do not do this, the import will attempt to create the banks again and be rejected. Keep the original file with the bank balances as you will need to refer to this data later.
- 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.
Step Two: Import the Data File
Warning
Before you begin the upload and import process, ensure that all other users are logged out. If another user enters transactions during import, this can cause data corruption.
In addition, Go to Setup > Manage Financial Periods and check the current financial period. Re-open or close as required.
- Go to Setup > Data Importer > Start > Trial Balance and Banks.
- Click Browse to find your saved CSV template.
- 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 Trial Balance
Navigating GL Import and Transactions Import
Once you have imported the completed data file, you are ready to apply the data to the system via the GL Import and Transactions Import.
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.
- Go to Setup > Data Importer > GL Import > Trial Balance.
- 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.
- 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.
- Date: Select the date to import the Trial Balance (in dd/mm/yyyy format). If the date picker is not visible, expand the screen or reduce the zoom to select it.
- To import, click Apply. This will import the Trail Balance values for the selected date.
Apply Period Movement Data
If you included monthly movement data in your template, import them here.
- Go to Setup > Data Importer > Transactions Import > Transactions.
- If you are importing into an entity with the United States locale, check “Interpret the dates in the uploaded CSV file as US Date Format”. Otherwise leave it unchecked.
- Select whether the transactions will be reconciled or unreconciled. For monthly movements reconciled is recommended.
- Note, if you do not see these options and instead receive an error list, you will need to make the required changes to the template. 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. As you have already imported opening balances in column C, skip that step and proceed directly to the transactions import. Repeat until the import passes validation.
- Note, if you do not see these options and instead receive an error list, you will need to make the required changes to the template. 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. As you have already imported opening balances in column C, skip that step and proceed directly to the transactions import. Repeat until the import passes validation.
- To import, click Apply. Please note use only one click not double click on this step. This step will create Trial Balance Movements by posting journals in the database.
Final Clean Up
Warning
Always perform cleanup after a new import. This avoids the risk of duplicating data from the previous import in the new import.
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.
Next Steps
Now you can close the data importer screen or move to the next step of importing of foreign currency bank account balances. See the relevant section on the Transactions worksheet in Transactions Data Importer.
Info
When finished, check the "Trial Balance V Aged Debtors/ Creditors Reconciliation" report to make sure that the control accounts in the Trial Balance reconcile to the totals for debtors/creditors as of the date of Trial Balance import.
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.
