Using bank statement imports

Bank Statement Imports have been improved with a set of new features. Imports now handle more file formats (multiple footer lines for Westpac files, Line Feeds as line separators) to help make importing bank statements easier.

Setting up a Bank Statement Import Format

There are several pieces to setting up the Bank Statement Import. To begin:

1. Open the Bank Account card
2. Locate the Bank Statement Import Format field and select New or go to the Full list and Edit an existing record.
3. Create a Bank Export/Import Setup record and enter (for example):
   • Code: CBA
   • Name: Commonwealth Bank
   • Direction: Import
   • Processing Codeunit ID: 1248 (or 1247 for processing Gen. Journal Lines)
   • Processing XMLport ID: 1220 (allows importing of CSV files)

Setting up a Data Exchange Definition

1. Lookup of the Data Exch. Def. Code field from the Bank Export/Import Setup record.

   The lookup page for Data Exchange Definition has been improved in this App for easier navigation. 

   

2. select New or go to the Full list and Edit an existing record, and enter the fields as shown below:
   • File Type: Variable Text (you can also import XML or Fixed Text)
   • Type: Bank Statement Import
   • Reading/Writing: XMLport
   • Ext. Data Handling: Codeunit
   • Column Separator: Comma (or Tab or Space or Semicolon)
   • Header Lines: If header lines exist in the file, enter the number of lines (usually 1)
     

1. You can now define multiple Footer Lines. When using multiple footer lines, the Footer Tag field should contain multiple commas separated tags (one for each line).
2. If your source file contains lines LF (Line Feed) as a record separator, then you should enable Use LF as Record Separator (This typically occurs if you are using a Linux or OS X).

Create the Column Definitions

1. Locate the Posting Line Definitions area and enter Default as the Code value, along with the number of columns that you will be importing.
2. Scroll down to the Posting Column Definitions area and enter each column as a new line. You will need to fill out:
   • Column No.
   • Name
   • Data Type
   • Data Format (Ensure that for month, capital M's are used. If a 'mm' value is used instead of 'MM', all months are set as January).
   • Data Formatting Culture
     

Add Field Mapping for Line Definitions

Once this has been done, you need to map these columns to the actual fields which reside against the Bank Acc. Reconciliation Lines. To do this:

1. Open the Field Mapping page via Line Definition.
   
2. Enter the information shown below.
   

Tip: Show 'Multiplier' column within lines to change amount sign to negative. Use '-1' as file value. This can be used if the Bank Statement has separate columns for Debit and Credit figures.

Tip: By selecting Field ID 23 in the Field Mapping above, you can also import your Bank Transactions into the new Payment Reconciliation Journal functionality.

Transformation Rules for Field Mappings

Use the Transformation Rule functionality to correct data format errors without making code modifications. You can chain multiple transforms to ensure the data is correct.

For example, an import is setup to use ddMMyyyy as the date format, but the file contains the value 9122018 as the date (missing leading zero). The system will try to convert this into ddMMyyyy and will fail. A simple transformation rule using regex replace can correct this at the time of import.

You can now go to your Bank Reconciliation and auto import your Bank Statement.