Data Importer User Guide Section 5 - Transactions Importer

Data Importer User Guide for importing Sales Transactions, Purchase Transactions and "multi-line" Transactions

Written By Niall Conlon (Draft Writer)

Updated at June 8th, 2021

1. Introduction

The Transaction Importer is an import feature that allows you to bulk import Transactions such as Sales and Purchase Invoices, Payments, Receipts and Journals.

Warnings:

  1. Risk of Data Corruption: The data importer is a really powerful tool that should be used with extreme caution and its use should be limited to users who have received the correct level of training. Misuse of the tool can cause data corruption. 
  2. Processing transactions and importing simultaneously: Information can be imported at any time but it is very important to note that when you are using the Data Importer from Setup > Data Importer and are in the process of importing data, no other user can be logged into the system. This is because when an import is taking place and a user is inputting transactions, then this can cause transactions to become corrupted. Please ensure to log all other users out of the system before you import your transactions.
  3. If your company has received training and you are in possession of this training guide, any fixes in relation to the imported data required from us will incur charges.

 

If in doubt, or if you receive an error message that you cannot resolve, contact your Support team for help.

This article is concerned with one section of the Data Importer - Sales and Purchase transactions.

If you need any of the other types please go to that section

Chart of accounts

Trial Balance and Banks

Static Data

Opening Financial Position

2. Download Template to populate your information

Click on Setup>Data Importer> select Transactions Importer>Download Transactions Template File

Choose the 'Save as' option and save as a Microsoft Excel Macro-enabled file (.xlsm format) choose the Location you wish to save the file in and Rename if necessary.

Open the file.

Enable Editing

Enable Content

 

 NB: 

  • Use the latest Import template: The Import Templates are available for download within the Data Importer.  The template is available to download in file format. Any Excel version after 2007 is compatible.
  • Excel for Mac is not compatible with Transaction importer and that it will not Generate the data file.
  • Download template 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 GL and BI code setup. 
  • Use a freshly downloaded template each time because the new template will contain any changes that users have made in the system, for example, updates to the GL codes, Supplier (Vendor) /Customer information etc..
  

3. Populating the template

With the Transactions Importer you can import the following:

  1. Purchase transactions
  2. Sales transactions

NB: 

  • Please note that if you create PI/PN in Purchase Transactions and SI/SN in Sales Transactions tabs, it will respectively create Purchase Batch Invoice/Debit Notes and Sales Batch Invoice/Credit notes (Not Item invoices).
  • Only use Bank GL codes in the GL code (Column F), if you are raising a PP Purchase Payment or a SR Sales Receipt. 

The following is a checklist/advice that you should carry out before importing this file:

  • For help filling in the template, click on the + in the top left-hand corner of the template to show the guidance for each column.
  • Ensure that you populate the templates from the first row (row 5), and that no row is left blank in between Row 5 and header row.
  • Make sure that no special characters are used (e.g. apostrophes, ampersands, Irish/French/Spanish characters etc).
  • Check for blanks in the mandatory fields.
  • Ensure that when pasting data into the template that the "Paste as Values" option is used- this will eliminate any formulas that have been used to calculate those values. Any pivot tables or formulas must be excluded from this template.
  • Ensure that there are no extra columns added.
  • Ensure column headings are not changed or deleted.
  • Ensure that there are no totals added to the bottom of any worksheet.
  • Ensure that the tab names are not changed or deleted.
  • Ensure that no extra tabs are added.
  • Ensure that valid dates are entered.
  • The values need to have 2 decimals as this is what the Data Importer supports. Please round the values into two decimals before pasting them into the template.
  • Make sure to use correct GL Codes to avoid incorrect transaction types being applied and incorrect postings.
  • Check that the customer and supplier codes exist in the system and spelled correctly.  
  • When importing invoices/Credit notes for Customer/Supplier in a foreign currency, make sure you have a correct difference between Total amount and Base Currency Amount. The template will not apply an exchange rate to the imported value.
  • Always import in test entity first. To see if it will import as the correct transaction type.
  • We suggest importing in smaller chunks (approx. 1000 lines) as it will be easier for you to check once imported.  This will lesser the risks of the data importer to time out and partially import transactions.
  • The Maximum amount of lines in one invoice is 100 lines however we don’t advise going over 20-30 lines as over 30 lines, the invoice pdf will likely be distorted.   


  • Tips for overriding the dropdown cells: When you have a cell with a dropdown, the template will not allow you to type in, but you can copy the code only from another spreadsheet and “paste as value” in the template (example: V01, GEN or GL codes).
  • If you see a triangle in the top-left corner of any cell containing a drop-down list, it means what you have typed in or copied in doesn’t exist from the list of choices:

 There are 3 types of References in the system, see below screenshot of Transaction Browser:

Ref.: is the Audit Trail Reference and auto increments from 1 on all transaction types.

Int. No.: These have different auto-incremented number depending on which type of transactions (SI,SN,BP,BC..). It is the invoice number for the SI Sales Invoice and SN Sales Credit Note.

Ext. Ref.: Is a free text alphanumerical Reference given by the user. It is the invoice number for the PI and PN.

 Here is a guide on each tab [in points a) and b)] and how to generate the CSV file in point c):

4. Purchase Transactions Tab

 

The Purchases Transactions worksheet can be used to import the following data: (Mandatory fields denoted with an asterisk (*)):

Transaction Type (*): Nominate the transaction type from the dropdown: PI Purchase Invoice, PP Purchase Payment, PN Purchase Debit Note, PC Purchase Credit Journal, PD Purchase Debit Journal (see additional notes on each below).

Transaction Ref (*): This Ref must be numerical. 

  1. For PI and PN, it tells the system to group into one invoice but doesn’t show anywhere in AIQ. It can be 1, 2, and so on.  Those same Transaction Ref can be re-used in the next template as it won’t have any effect on the auto-incremented number generated by the system. 
  2. For PP, PC and PD, the Transaction Ref will be recognized in the entity as Int. No. 

Ext Ref (*): is the invoice number. It allows you to recognize the invoice in the Purchase Ledger. 

Description (*): Description Line

Date (*): 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 checkbox option: "Interpret the dates in the uploaded CSV file as US Date Format" will be available prior to import. 

GL Code (*):  type the General Ledger account code.

Supplier Code (*): type the Supplier (Vendor) code that relates to the transaction.

Bi Code: Select from the list of available Bi codes for each line if applicable.

Purchase Control A/C (*): Nominate the control account from the dropdown. This control account needs to be the same default control account nominated for the Supplier (Vendor). 

Tax CodeNominate the Tax code from the dropdown.

Tax GL account:  Nominate the Purchase Tax (VAT) account from the dropdown. 

Net Value: Amount without tax. If foreign supplier, this must be in the currency of the supplier.

Tax Value: Tax amount. If foreign supplier, this must be in the currency of the supplier.

Total (*): Total amount of invoice with Tax. If foreign supplier, this must be in the currency of the supplier. 

Base Currency Value (*): Amount in Entity’s Base currency.

 Validation warnings:

  • The template will not check if the Purchase Control A/C for a particular Supplier (vendor) is correct. It will import the data as imported. Make sure the control GL matches with the one nominated against supplier in the system.
  • The template will not check if you have imported the correct Purchase Tax GL account. If you input another GL code other than the VAT Purchase Tax default, it will incorrectly raise as a journal instead.   
 

 PI Purchase Invoice

If you want to create one invoice with 3 different lines, you need the same TRANSACTION TYPE, TRANSACTION REF, EXT REF, DATE AND SUPPLIER CODE (yellow highlighted columns):

Transaction importer>Purchase Transactions tab: 

  • Enter the Net, Tax, Total and Base Currency values as positive values. 
  • Transaction Ref is not taken into account as Int no is auto numbered by the system. 

 

This will show as one Batch Invoice with 3 lines under Purchases>Batch Invoice Grid

 

AIQ system will create an auto-numbered “Int no” starting from 000001, 000002 incrementing only for Purchase Invoices.

Validation warning:  If you re-import the file as it, you will see an error message saying that you have already used this Ext Ref (Column C).

 

PN Purchase Debit Notes

Transaction importer>Purchase Transactions tab: 

  • Transaction Ref is not taken into account as Int no is auto numbered by the system. AIQ system will create an auto numbered “Int no” starting from 000001, 000002 incrementing only for Purchase Debit Notes.
  • Enter the Net, Tax, Total and Base Currency values as negative values.

 

Purchases>Batch Invoice Grid:

Remember to allocate the PN to the PI via Purchases>Payments & Allocation screen if necessary.

PP Purchases Payments

Transaction importer>Purchase Transactions tab: 

  • The system will use the Transaction Ref as Int No.
  • Leave following columns blank: Tax code, Tax GL Code, Net Value and Tax Value.
  • Enter Total Value in the supplier currency with negative values
  • Enter Base Currency value with negative values
  • Enter a bank GL code in the GL code (Column F)

 Double entry from General>Transaction Browser Grid:

 NB:

  • IN THIS TAB, YOU CAN ONLY IMPORT PAYMENTS for suppliers in the same currency as the currency of the bank. If your supplier is EUR, you can only import PP in a EUR bank account.
  • If the suppliers’ currency is different from the currency of the bank, either request for TRANSACTION tab training or use the Bank payment import. Once your bank statement is imported, you can go to Payments & Allocation screen to create PPs to match to PIs.
  • Remember to allocate the PP to the PI via Purchases>Payments & Allocation screen.

 Validation warning:  The system will not check if the Int No (Transaction Ref in template) or the Ext Ref have already been used. 


PC Purchases Credit Journal

Transaction importer>Purchase Transactions tab: 

  • The system will use the Transaction Ref as Int No.
  • Leave following columns blank: Tax code, Tax GL Code, Net Value and Tax Value.
  • Enter Total and Base Currency values as positive values. 

 Double entry from General>Transaction Browser Grid:

Validation warning:  The system will not check if the Int No (Transaction Ref in template) or the Ext Ref have already been used. 


PD Purchases Debit Journal

Transaction importer>Purchase Transactions tab: 

  • The system will use the Transaction Ref as Int No.
  • Leave following columns blank: Tax code, Tax GL Code, Net Value and Tax Value.
  • Enter Total and Base Currency values as negative values. 

 Double entry from General>Transaction Browser Grid:

Validation warning:  The system will not check if the Int No (Transaction Ref in template) or the Ext Ref have already been used. 

5. Sales Transactions Tab

 

The Sales Transactions worksheet can be used to import the following data: (Mandatory fields denoted with an asterisk (*)):

Transaction Type (*): Nominate the transaction type from the dropdown: SI Sales Invoice, SR Sales Receipt, SN Sales Credit Note, SC Sales Credit Journal, SD Sales Debit Journal (see additional notes on each below).

Transaction Ref (*): This Ref must be numerical. 

  1. For SI and SN, this tells the system to group into one invoice but doesn’t show anywhere in AIQ. It can be 

1, 2, and so on.  Those same Transaction Ref can be re-used in the next template. 

  1. For SR, SC and SD, the Transaction Ref will be recognized in the entity as Int. No.  

Ext Ref (*): This is a secondary Ref provided by the user. It allows you to recognize the invoice in the Sales Ledger. Note that this is NOT the Invoice no, the Invoice no is the Int no and is auto allocated. More details below.

Description (*): Description Line

Date (*): 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 checkbox option: "Interpret the dates in the uploaded CSV file as US Date Format" will be available prior to import. 

GL Code (*):  type the General Ledger account.

Customer Code (*): type the Customer code that relates to the transaction.

Bi Code: Select from the list of available Bi codes for each line if applicable.

Sales Control A/C (*): Nominate the control account from the dropdown. This control account needs to be the same default control account nominated for the Customer.

Tax Code: Nominate the Tax code from the dropdown.

Tax GL account:  Nominate the Sales Tax (VAT) account from the dropdown.

Net Value: Amount without tax. If foreign customer, this must be in the currency of the customer.

Tax Value: Tax amount. If foreign customer, this must be in the currency of the customer.

Total (*): Total amount of invoice with Tax. If the Customer is in foreign currency, then populate with the foreign currency value.

Base Currency Value (*): Amount in Entity’s Base currency 

 Validation Warnings:

  • If you put letters in Transaction Ref, it will be imported and it will end up as the Int No/Inv No, however it will not be possible to print the PDF invoice. You will see an error message: “no data”.
  • If you re-import the file with the same ext. ref. or transaction ref., the system will not check if these have already been used. 


SI Sales Invoice

Same as in Purchase, if you want to create one sales invoice with several lines, you need the same TRANSACTION TYPE, TRANSACTION REF, EXT REF, DATE AND CUSTOMER CODE

Transaction importer>Sales Transactions tab: 

  • Enter the Net, Tax, Total and Base Currency values as positive values. 
  • Transaction Ref: This tells the system to group into one invoice but doesn’t show anywhere in AIQ. It can be 1, 2, and so on.  Those same Transaction Ref can be re-used in the next template.

 This will show as one Batch Invoice with 3 lines under Sales>Batch Invoice Grid

  PDF print out of the invoice:

 

Intercompany:

-If you are using an Intercompany Customer, it will send the corresponding intercompany Purchase Invoice for acceptance to the linked receiving entity. At arrival, the PI Ext Ref will be validated as it needs to be unique in that database.

Auto-Numbering:

- AIQ system will create an auto-numbered “Int no” which will be the invoice number. Go to Setup>Company details & Settings>Settings>Under Customer Numbering> untick Auto Invoice Numbering. For this demo, I have set to 000001 so the next available invoice number will be 000002.  Then Tick the box again to save the number>save. This also applies to Sales Credit notes.

 

 If SI Auto-numbering is OFF, Transaction Ref will be the Int. No. which represents the Sales Invoice No. See screenshots below:

 

NB: If you are planning to have an integration with the Sales Ledger, you won’t be able to have the auto-numbering off.


SN Sales Credit Notes

Transaction importer>Sales Transactions tab: 

  • Transaction Ref: This tells the system to group into one invoice but doesn’t show anywhere in AIQ. It can be 1, 2, and so on.  Those same Transaction Ref can be re-used in the next template.
  •  Enter the Net, Tax, Total and Base Currency values as negative values.

 Sales>Batch Invoice Grid:

Note that it has taken the next available number as set up under Setup>Company details & Settings>Settings>Under Customer Numbering> Auto Credit Note Numbering set to 000001.

Remember to allocate the SN to the SI via Sales>Receipts & Allocation screen if necessary.


SR Sales Receipts

Transaction importer>Sales Transactions tab: 

  • The system will use the Transaction Ref as Int No.
  • Leave following columns blank: Tax code, Tax GL Code, Net Value and Tax Value.
  • Enter Total Value and Base Currency value with negative values
  • Enter a bank GL code in the GL code (Column F).

 Double entry from General>Transaction Browser Grid:

NB

  •  IN THIS TAB, YOU CAN IMPORT RECEIPTS for customers in the same currency as the currency of the bank. If your customer is EUR, you can only import SR in a EUR bank account.
  •  If you customers’ currency is different from the currency of the bank, either request for TRANSACTION tab training or use the Bank payment import. Once your statement is imported, you can go to Receipts & Allocation screen to create your SRs and match to SIs.
  • YOU CAN IMPORT RECEIPTS INTO DIFFERENT BANKS - IF APPLICABLE PLEASE ENSURE THAT THE RELEVANT FX RATES FOR TRANSACTIONS ARE FACTORED INTO THE VALUES CORRECTLY. 
  • Remember to allocate the SR to the SI via Sales>Receipts & Allocation screen if necessary.

 Validation warning:  The system will not check if the Int No (Transaction Ref in template) or the Ext Ref have already been used. 


SC Sales Credit Journals

Transaction importer>Sales Transactions tab: 

  • The system will use the Transaction Ref as Int No.
  • Leave following columns blank: Tax code, Tax GL Code, Net Value and Tax Value.
  • Enter Total and Base Currency Total values as negative values. 

 

Double entry from General>Transaction Browser Grid:

Validation warning:  The system will not check if the Int No (Transaction Ref in template) or the Ext Ref have already been used. 


SD Sales Debit Journals

Transaction importer>Sales Transactions tab: 

  • -The system will use the Transaction Ref as Int No.
  • Leave following columns blank: Tax code, Tax GL Code, Net Value and Tax Value.
  • Enter Total and Base Currency Total values as positive values. 

 Double entry from General>Transaction Browser Grid:

Validation warning:  The system will not check if the Int No (Transaction Ref in template) or the Ext Ref have already been used. 

6. Generate Data File, Upload to the system & Create transactions

Once you have filled in the template, go to the GENERATE DATA FILE tab, and click on the Generate Data Upload File button.

This is the first stage of Error Validation. If there is any error, correct and click on Generate Data Upload File again.

If there are no errors in the spreadsheet, the CSV file will be generated. You will see the below window:                                         

You will find the CSV file in the same location as the macro-enabled excel file.

 
Upload to the system

To upload the file, you go to Setup>Data Importer>Transaction Importer>Browse and select csv file of the transaction importer template you have just generated. Then Click on Upload Data.  


NB: Do not double click on Upload Data as it can bring in the information twice. 

 Click on Close NOT Next Step.


Create the transactions

Click Data Importer> Transaction Import>Transactions

This is the second stage of Error Validation.

If any errors, follow the steps to correct errors in the template>Save>Generate Data File>Transaction Importer>Browse> select csv >Upload Data. If in doubt, contact support team.

If there is no error at this stage, you will see the above window, tick Reconciled if you want to import your transactions as reconciled, so they do not appear in the bank reconciliation screen. This is usually used if you are importing historical data that has already been previously reconciled. Otherwise, click un-reconciled.

This is particularly relevant if the GL code includes a bank GL (For PP Purchase Payments or SR Sales Receipts).

Then click Apply.

You will see a progress bar.

NB: If you have a connection time out after you click Apply, once your connection is back do not click again. This will import your transactions twice.

7. Clean Up & Check Transactions are imported successfully

Always go to the clean up step to delete the temporary files you uploaded.

Please Click on Data Importer>Validation>Clean Up if you are satisfied with your import and would like to clear any previous uploaded template before a new import.

NB: BEWARE to not confuse with Purge Transactions as this will delete all transactions. (only applicable for old COA importer entities)

 

Check transactions in the system


Th3se are the windows to check that will tell if your transactions have been imported correctly:

Transaction Browser

Edit one or more transactions by clicking on General>Transaction Browser>select one of the transaction ref.

If you see the double entry transaction screen below, correctly, with all headings and without any distortion, it means the transactions have been imported successfully.


Bank reconciliation

Check that your SR or PP appear correctly in bank reconciliation if you clicked import as un-reconciled or don’t appear if you click reconciled.  


VAT Management screens

Check that your SI/SN and PI/PN appear in the VAT Management screens under the Sales and Purchases tabs.

Corrections

If you realised that you have imported some of data with errors, only minor changes can be done. Refer to Transaction browser guide.  

If there are too many edits to make, it is suggested to import reversal invoices or journals to net off the initial import and re-import with correct data. 

8. Other transaction types for import like Journals

Please see the following help article for import of transactions like Wages Journals

GL Importing from Excel