Data Importer System Screen Guide

Data Importer Screen Guide

Written By Niall Conlon (Draft Writer)

Updated at April 25th, 2018

 

1. Introduction

The Data Importer is a tool used to import the following information:

(i) Opening Balances, for example, supplier and/or customer open items

(ii) Transactional Information, for example, payroll journals

(iii) Static information, for example, customer/supplier details including code, name and address, supplier bank details, contact information etc

(iv) General Ledger information, for example, Chart of Accounts including categories, subcategories, General Ledger account codes and descriptions

(v) Bank Information, for example, bank balances in the base and foreign currency

(vi) Stock Information, for example, stock items 

 The data importer consists of a number of steps which can be run from start to finish or individually.  A user has the option of importing all information from their previous system or importing static information such as customer/supplier codes, names, addresses, etc. 

The import tool provides an option to clear existing information or to add to existing system data. 

The import tool cannot accept data prepared from any other workbook format. It must be downloaded from the Setup > Data Importer menu option. You must select the required dataset you wish to populate. For example, if you want static customer and supplier information you choose the setup Template file. If you want opening balances for your customers and suppliers and bank unreconciled items, choose the Open Items Template file

The purpose of this document is to provide a guide for users to the steps required to complete the import steps after the file has been prepared in the correct template file

2. Data Preparation 

It is vital that the data is prepared appropriately before attempting to import.  There are 5 separate articles as below to assist users in preparing data using the Generic Import Template.

Data Importer User Guide Section 1 - Introduction and Chart of Accounts

Data Importer User Guide Section 2 - Trial Balance and Banks

Data Importer User Guide Section 3 - Static Data

Data Importer User Guide Section 4 - Opening Financial Position

Data Importer User Guide  Section 5 - Transaction Importer


Note - 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 are set during this routine.  These should be verified from the main system in advance of importing data.


3. Accessing and Initial screen 

The importer tool is located under menu option Setup > Data Importer.  The steps within the data importer can be followed from start to finish or individually depending on the requirements of the user.  For the purpose of this document, each step is outlined in sequence.  

Steps are divided into sections which are based on an area of the system, for example, General Ledger importer includes several steps covering the different types of General Ledger information a user can import. Below is the screen of the Upload setup data. This upload screen is very similar to the open items, transactions, and stock data upload screen and will be explained below.


To upload your .csv file which is created when you save your .xls file you have downloaded and worked on populating click on the Browse button. Upload the file you require. It is useful in this instance to see the date modified as this indicates when you last used it. Also, ensure to select the CSV version of the excel file you have been working on & not the .xls version.

When you successfully browsed to the location you have the .csv file in click on it and click the Green Upload Data button. The system will do an initial check to see all is in order. You will receive a message like the below.


4. Working on importing your data

You can then either click Next step which takes you to the very first screen in the data importer the GL Import > GL Categories screen or if you click close on the below screen you can click on which menu option you want to go to on the left-hand menu. If you wanted to import customers static information you could click close on the screen above and then click on Customers / Suppliers import > Customers.

When a step is successful the Data Importer will prompt you to move to the next step.  Steps are colour coded according to their status i.e. Green means the step was completed, Red means the step has not been completed, and greyed out means the step is not available for selection (because the data is not included in the Import Template).

A user can skip a step at any time by clicking on Skip step.  

When data from the Generic Template is uploaded and presented to the user in each of the steps, the user may edit the information at any time by clicking on Edit for the relevant record.

If errors occur when uploading the data from the Generic Template the error will be highlighted to the user.  They will be able to hover over the field to display further information on the error. The user will then need to make the corrections on the .xls file, regenerate the CSV file and upload it again. 

If a user logs out of a database and logs in again to continue with the import the user does not have to upload the import file again as the file is retained in the importer. 

Equally, if changes are made to the data in the Generic Template, the spreadsheet must be saved and uploaded to the system again. 

5. General Ledger Import  

The GL Import allows a user to import all information relating to the General Ledger.  

GL categories  

Imported GL Categories are displayed on the screen.  If required a user can edit the information by clicking on Edit.  TBC Edit needs to be explained a little better

Clear Existing Categories checkbox – if you wish to clear existing GL categories check this box.  If this box is not checked the importer will add to existing categories in the database.

To import GL categories click Apply

Once applied and imported successfully a message is displayed prompting the user to click Next for the next step 

GL Sub Categories  

GL subcategories are displayed on the screen.  If required a user can edit the information by clicking on Edit. Users can change the subcategory id, description and sort order at this stage.

Clear Existing Sub Categories – if you wish to clear existing GL subcategories check this box.  If this box is not checked the importer will add to the existing subcategories. To import GL subcategories click Apply. Again once applied and imported successfully a message is displayed prompting the user to click Next for the next step 

Tax Codes  

Tax codes you wish to import are displayed on the screen.  If required a user can edit the information by clicking on Edit 

Clear existing tax codes – if you wish to clear existing tax codes check this box. If the box is not checked the importer will add to the existing tax codes 

To import tax codes Click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for the next step 

Note: Clearing tax codes which are associated with GL accounts, customer accounts, supplier accounts and transactions will clear the transactions and the tax codes from the database.  

Departments  

Departments you wish to import are displayed on the screen.  If required a user can edit the information by clicking on Edit 

Clear existing departments – if you wish to clear existing departments to check this box.  If the box is not checked the importer will add to the existing departments.

To import department click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step 

GL Account Defaults 

A user must set the GL account defaults.  GL defaults are assigned to GL codes.  A user can amend GL defaults at any stage.  Defaults are not included in the template spreadsheet.  Within GL default screen fields which are flagged with an asterisk are mandatory and must be populated by a user with the information available from the drop-down list. 

To update click Apply. 

Once applied and updated successfully a message is displayed prompting the user to click Next for the next step  

GL Accounts  

GL accounts you wish to import are displayed on the screen.  If required a user can edit the information by clicking on Edit. 

If an error occurs with the data the error is displayed on screen e.g. “Category Not Found” is displayed as the text against the GL code.  To fix the error create a new category in the system or click on Edit and assign an existing category.

Clear existing GL accounts – if you wish to clear existing GL accounts check this box.  If the box is not checked the importer will add to the existing GL accounts 

To import GL accounts Click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for the next step 

Australian Tax Setup 

Please note for Australian Companies the tax code setup is not complete until the Australian Tax Setup step is run.  

This step allows a user to import the relevant GL account codes associated with each Tax code, the GL accounts which should be debited and credited when a tax code is used are imported. 

The tax codes must already exist. 

Tax codes, tax rates, tax type and GL accounts are displayed on the screen.  If required a user can edit the information by clicking on Edit 

To import the information Click Apply 

One applied and updated successfully, the importer will move onto the next step. 

Required Accounts  

The system maintains a list of Required Accounts, which are necessary for the automated posting of transactions in the system.  It is imperative that these accounts are created and mapped correctly.  

To assist you in this please follow the tips below

(i) Each required account should be mapped to 1 and only 1 GL account in your GL

(ii) If you do not have an entry in your GL account for a required account please set it up

(iii) For the Stock and FX revaluation required accounts please set up 2 GL for each 1 for P&L and 1 for Balance sheet

The list of Required Accounts covers the accounts necessary for a variety of transactions, including Control Account, i.e. Accounts Receivable Control, Accounts Payable Control, and Retained Reserves.  These Control Accounts are special accounts that are only updated by the system from transactions recorded in a subsidiary ledger (Debtor and Creditor) or during Year End routines (Retained Reserves). Direct journal postings to these Control Accounts are prohibited. 

To import the Required Account the GL code must exist. 

A list of Required Accounts and the GL code are displayed on the screen. If necessary a user can edit the information by clicking on Edit.  If an error occurs with the data a message is displayed on screen advising an error has occurred, to view the error detail hover over the highlighted error field. 

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for the next step 

Trial Balance  

To import a Trial Balance (TB) the GL accounts must exist in the database.  If accounts do not exist an error message is displayed.

The Trial Balance you wish to import is displayed on the screen.  If required a user can edit the information by clicking on Edit.   If an error occurs with the data the error is displayed on screen e.g. GL account not found.  The GL accounts must be imported before the TB  

Date – Select the date you wish the TB to be imported (in dd/mm/yyyy format)

Please note you will not be able to import to the retained reserves or sales and purchase tax accounts as these are system control accounts. To overcome this you can sum your balances for the sales and purchases tax account and add these to the tax liability account and import this figure to your tax liability account.

Similarly, for your retained reserves account please set up a new GL account called retained reserves opening balance account and import this figure to this GL account

To import click Apply

Once applied and imported successfully a message is displayed prompting the user to click Next for the next step 

6. Banks Import  

The Bank import step allows a user to import all information relating to their bank details, including un-reconciled bank items.

Bank Account Defaults 

A user must set the bank account defaults.  Bank defaults are assigned to bank accounts.  A user can amend bank defaults at any time.  Bank defaults are not included in the spreadsheet for import.   

Within bank default screen fields which are flagged with an asterisk are mandatory and must be populated by a user with the information available from the drop-down list.    

To update click Apply. 

Once applied and updated successfully a message is displayed prompting the user to click Next for next step  

Bank Accounts 

Bank accounts you wish to import are displayed on the screen. If required a user can edit the information by clicking on edit.  If an error occurs with the data a message is displayed on the screen to view the error hover over error fields highlighted 

Date – select the date you wish to import the bank balances 

Clear existing banks – if you wish to clear existing bank check this box.  If this box is not checked the importer will add to existing bank accounts. 

FX Values on bank accounts explanation needed here

To import click Apply

Once applied and imported successfully a message is displayed prompting the user to click Next for the next step 

Unreconciled Bank Items 

To import un-reconciled transactions the GL bank account must exist.  If the GL accounts do not exist the import will fail. 

Un-reconciled items you wish to import are displayed on the screen.  If required a user can edit the information by clicking on edit.  If an error occurs with the data an error message is displayed on the screen to the view the error detail hover over error fields highlighted.

Unreconciled bank items are imported to assist you in reconciling your bank account. They do not affect any balance on the system

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for the next step  

Note: To ensure un-reconciled transactions do not modify any existing bank balances unreconciled transactions are imported into an Importing Suspense Account. 

Customer & Supplier Import 

Customer & Supplier import allows a user to import all information relating to customers and suppliers, including open items. Open items can be outstanding invoices or a balance at a point in time. It is possible to import multiple open items

Customers/Suppliers – Possible Errors/Issues  

When importing customers and suppliers the following information must already exist in the system: 

Tax Code the customer/supplier is assigned to.  If the/a tax code does not exist the importer will display an error at which stage the user can edit the customer/supplier record and re-assign a tax code 

Bank Account the customer/supplier is assigned to.  If the/a bank account does not exist the importer will display an error at which stage the user can edit the customer/supplier record and re-assign a bank account 

Foreign Currency – If a customer/supplier is assigned to a foreign currency, the currency must exist in the system.  If the current does not exist the importer will display an error at which stage the user can edit the customer/supplier record and assign a currency which exists.

Price Categories  

The price categories you wish to import are displayed on the screen.  If required a user can edit the information by clicking on edit.   

Clear existing price categories – if you wish to clear existing price categories check this box.  If the box is not checked the importer will add to the existing price categories 

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step 

Customer Defaults  

A user must set the customer defaults.  Customers defaults are assigned to customer accounts when a new customer account is created defaults are applied.  Customer defaults are not included in the spreadsheet for import. Customer defaults can be amended at any time. 

Within customer default screen fields which are flagged with an asterisk are mandatory and must be populated by a user with the information available from the drop-down list.    

To update click Apply. 

Once applied and updated successfully a message is displayed prompting the user to click Next for the next step  

Customers    

Customers you wish to import are displayed on the screen. If required a user can edit the information by clicking on edit. .  If an error occurs with the data a message is displayed on the screen to view the error hover over error fields highlighted 

Clear existing customers – if you wish to clear existing customers, check this box.  If the box is not checked the importer will add to existing customers 

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step 

Supplier Defaults 

A user must set the supplier defaults.  Suppliers defaults are assigned to supplier accounts when a new supplier account is created defaults are applied.  Supplier defaults are not included in the spreadsheet for import. Supplier defaults can be amended at any time. 

Within supplier default screen fields which are flagged with an asterisk are mandatory and must be populated by a user with the information available from the drop-down list.    

To update click Apply. 

Once applied and updated successfully a message is displayed prompting the user to click Next for next step  

Suppliers 

Suppliers you wish to import are displayed on the screen. If required a user can edit the information by clicking on edit. .  If an error occurs with the data a message is displayed on the screen to view the error hover over error fields highlighted 

Clear existing suppliers – if you wish to clear existing suppliers, check this box. 

If the box is not checked the importer will add to existing suppliers 

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step 

Customer Open Items 

Note: When importing customer open item the customer must exist 

Customer open items are displayed on the screen.  If required a user can edit the information by clicking on edit.  If an error occurs with the data a message is displayed on the screen to view the error hover over error fields highlighted 

Clear existing customer open items – if you wish to clear existing customer open item check this box.  If the box is not checked the importer will add to the existing open items.

Data format – If data provided has dates in US format instead of European format check this box to change to European format. 

Note - To prevent Debtors (AR) control account modification all opening items are posted to a Suspense Account (suspense account GL code is displayed on screen), the suspense account box is flagged as default.  If you wish to change the balance to post to a Control Account remove the check from this box

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step 

Supplier Open Items 

Note: When importing customer open item the customer must exist 

Supplier open items are displayed on the screen.  If required a user can edit the information by clicking on edit.  If an error occurs with the data a message is displayed on the screen to view the error hover over error fields highlighted 

Clear existing supplier open items – if you wish to clear existing supplier open item check this box.  If the box is not checked the importer will add to the existing open items.

Data format – If data provided has dates in US format instead of European format check this box to change to European format. 

Suspense Account – As Previously explained earlier

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step 

Customers/Suppliers – Possible Errors/Issues   

When importing customers and suppliers the following information must already exist in the system: 

Tax Code the customer/supplier is assigned to.  If the/a tax code does not exist the importer will display an error at which stage the user can edit the customer/supplier record and re-assign a tax code 

Bank Account the customer/supplier is assigned to.  If the/a bank account does not exist the importer will display an error at which stage the user can edit the customer/supplier record and re-assign a bank account 

Foreign Currency – If a customer/supplier is assigned to a foreign currency, the currency must exist in the system.  If the current does not exist the importer will display an error at which stage the user can edit the customer/supplier record and assign a currency which exists.

7. Stock Import 

Stock import allows a user to import all information relating to stock items including locations, sub-locations

Stocktake is not completed through the data importer but through the stock > Stock take feature in the system

Stock Locations   

Stock locations you wish to import are displayed on the screen.  If required a user can edit the information by clicking on edit. 

Clear existing locations – if you wish to clear existing locations check this box. 

If the box is not checked the importer will add to the existing locations 

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step 

Stock Sub Locations  

Stock sub locations you wish to import are displayed on the screen.  If required a user can edit the information by clicking on edit. 

Clear existing sub locations – if you wish to clear existing sub locations check this box.  If the box is not checked the importer will add to the existing sub locations 

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step 

Stock Item Groups  

Stock item groups you wish to import are displayed on the screen.  If required a user can edit the information by clicking on edit. 

Clear existing item groups – if you wish to clear existing stock item groups, check this box.  If the box is not checked the importer will add to the existing stock item groups

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for next step

Stock Item Defaults  

A user must set the stock item defaults.  Stock items defaults are assigned to stock items, when a new stock item is created defaults are applied.  Stock item defaults are not included in the spreadsheet for import. Stock item defaults can be amended at any time. 

Within the stock item default screen fields which are flagged with an asterisk are mandatory and must be populated by a user with the information available from the drop-down list.    

To update click Apply. 

Once applied and updated successfully a message is displayed prompting the user to click Next for next step  

Stock Items  

Note: When importing stock items; locations, sub-locations and GL codes which the stock items are assigned to must exist. 

Stock items you wish to import are displayed on the screen.  If required a user can edit the information by clicking on Edit 

Clear existing stock items – if you wish to clear existing stock items check this box.  If the box is not checked the importer will add to the existing stock items.

To import click Apply 

Once applied and imported successfully a message is displayed prompting the user to click Next for the next step.

8. Transaction Import  

The transactional importer allows for both one-off and periodic import of transactions.  It is imperative that before you commence populating and importing transactions into the .xls file you undergo training. Please contact your local support representative who can arrange this

During the import routine, a number of checks will be made against the data to be imported, to verify that the data is correct, e.g. the GL account or Customer account exists, and to verify the integrity of the data, e.g. that the element of the transaction balance.

Transactions 

Provided the data checks are passed the system will prompt the user to import the transactional data.    

To update click Apply. 

Once applied and updated successfully a message is displayed prompting the user to click Next for the next step 


9. Validation 

When data is imported the importer will complete a series of checks in the background and validate the data.  If an error occurs with the data the error is displayed on the screen. 

The balances imported are also displayed on screen i.e. Debtors balance 

Clean Up  

When data is imported temporary import files are created in the database.  These files remain until a clean-up is performed.  To remove the files simply run the Clean Up step.  

Clean up should only be completed when a data import is complete and the files are no longer required.

Purge Transactions  

Purge transactions provide a user with the option of removing all data from the system.   Purge Transactions step will remove both imported transactions and transactions entered manually (i.e. by a user).  Purge transactions will delete all posted transactions from the system and reinitialize the Company to a blank Company

All Static information is retained such as GL codes, categories & subcategories, departments, customers, and suppliers

Note:  Once transactions are Purged the transactions cannot be recovered.  The company will be blank of all posted transactions.