Bank Accounts Data Importer

How to import bank accounts

Written By Grainne Reidy (Super Administrator)

Updated at March 14th, 2024

Introduction

The Data Importer lets you populate predefined Excel templates and import them into AccountsIQ. It can clear 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.

Delete

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.


During the Chart of Accounts import, any GL codes assigned as banks for customers or suppliers will be created as Bank Accounts. If you need to create further bank accounts, you can do this using the Bank Importer.

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.

Use separate templates for each company.

You must download the Import Templates from each company individually. We do not recommend importing 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 to import 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‍ 

Chart of Accounts Data Importer‍ 

Customer Data Importer‍ 
Supplier Data Importer‍ 

Items Data Importer ‍ 

Trial Balance and Banks Importer‍ 

Opening Financial Position Importer‍ 

Transactions Data Importer‍ 

Delete

Prepare the Template

When completing a worksheet, always read the instructions provided in each worksheet.

  1. Go to Setup > Data Importer > Start Bank Accounts.


     
  2. Click the download link and save the file as a Microsoft Excel Macro-enabled file (.xlsm format).      
  3. Open the file. If Macros are not enabled, click Enable Content.

    Graphical user interface, application, Word Description automatically generated 
  4. Fill in the required fields, following the Guidelines for Data Entry, below. For details on the worksheet, see:
    • Bank Accounts: Mandatory fields are marked with an asterisk (*) on the column header, all other fields can be left blank.
  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, always select 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.  
Delete

Bank Account Template

 

Fields marked with an asterisk (*) on the column header are mandatory, all other fields can be left blank.

  
Field Notes Details
Bank GL A/C Code* Enter the GL Account code.  The GL Account must be set up in the General Ledger before it can be entered here.
Only GL accounts with an account type of posting can be added as bank accounts
Bank A/C Name* Use numbers and letters, up to 50 characters** Used to identify Bank Accounts in the Bank, Customer and Supplier screens.
Bank Account name can be different from the Bank GL Account Code Description. We recommend it contains the name of the Bank for Customer clarity.
Currency Code* Enter the currency code as set up in Setup>Codes Maintenance>Currencies It is not possible to change the currency of the bank once it is set up
Address 1 Use numbers and letters, up to 50 characters** Text entered here will appear in sales invoices in the Bank Details section
Address 2
City
County/State
Country
Phone
Bank Account Code Enter 8 numeric characters with no spaces or separators. Bank Details (either Bank Account Code & Sort Code or IBAN & SWIFT) will be used in the payment file produced by Supplier Batch Payments (depending on the online payment file type selected)
Sort Code Enter 6 numeric characters with no spaces or separators.
IBAN Use numbers and letters to enter IBAN (International Bank Account Number).
Use IBAN checker (www.iban.com) to validate the IBAN before entering it in the system. The system will not check the validity of this data
BIC/Swift Code Use numbers and letters to enter BIC/ Swift Code. Enter between 8 and 11 characters depending on your Bank BIC/ Swift Code.
Online Payment File Type (Enter File Type Number)

(Note, this column is hidden in the template. Double-click in the header to reveal it.)

Enter the number of the required Payments File Settings.

The list of Online Payment File Types is liable to change. The current list can be viewed from Bank>Bank List>Payments File Settings>Type

Used to configure the correct bank payment file type for batch payments from this bank account.

If your bank has more than one option file, you can test them in your sandbox with different payment runs to see which is successful.
Online User ID (Note, this column is hidden in the template. Double-click in the header to reveal it.)

Use numbers and letters to enter Online User ID.
This is a unique user ID assigned to you by your bank to submit either payment or Direct Debit files.

Used for batch payments from certain bank payment file types
Group Online Bank Payments for Reconciliation (Yes/No)
Enter Yes or No in this field. If left blank this field will default to "Yes" (Note, this column is hidden in the template. Double-click in the header to reveal it.)

Select Yes, to group any online payments processed through Supplier Batch Payments as one lump amount in the bank reconciliation screen. 

Select No, to show each payment within a batch as a separate lines in the bank reconciliation screen. 


 

Reconcile this Bank (Yes/No) (Note, this column is hidden in the template. Double-click in the header to reveal it.)
Enter Yes or No in this field. If left blank this field will default to "Yes"
 
 
Overdraft limit (Note, this column is hidden in the template. Double-click in the header to reveal it.)
Enter any overdraft amount in numeric characters
The overdraft limit will be taken into account and the system will warn you if you are about to exceed this with a supplier batch payment

**The following special characters are allowed as separators in this field:

  • hyphen "-"
  • underscore "_"
  • forward slash "/"
  • coma ","
  • full stop "."
Delete

Import Template

Step One: Import the Data File

Delete

Info: Group Companies

Each Entity that you want to import banks into should have its own import file. Then, the data for each should then be compiled to create an import file for the Group Consolidation Entity.


  1. Go to Setup > Data Importer > Start > Bank Accounts.  
  1. Click Browse to find your saved template.


     
  2. Click Import.

Step Two: Validation

The import file will be validated.

  1. If there are any errors, an error message will appear, along with an error file. Download this 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. 
  2. 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).
  3. Correct all errors and re-import the file as described in step two above. You can make corrections directly in the error file.

Step Three: Check the Imported Data in the system.

You will receive a confirmation message if the import is successful. Close the importer and check the imported data in the system. If any accounts have been set up as Bank Accounts in error, these can be deleted from the system as long as no transactions have been posted to that account. 

Delete

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.Graphical user interface, text, applicationDescription automatically generated
Delete