How to Import BAI2 File Format Data into Microsoft Dynamics 365 Business Central
Recently we had a client that wanted to load their Bank Statement for reconciliation purposes, so immediately we thought about Data Exchange Definition as a solution. The problem came when we realized that the bank statement file was a BAI2 file format.
In short, the BAI2 format is a cash management balance reporting specification developed by the BAI (Bank Administration Institute), presented as a plain text, comma delimited. Its structure is complex, composed by header, group headers, detail, group footers and grand total. Each record is identified by a code and the detail has a type code that defines the transaction.
Let’s take a brief look at the BAI2 file format.
BAI2 Record Description
You can consult the BAI’s Technical Reference manual here.
The BAI2 file can contain information for multiple bank accounts at the same time. The transactions are grouped by Transaction Date and sub-grouped by Bank Account Number.
For the purpose of this blog, you should download your BAI2 file per bank account.
A BAI2 bank statement looks like this:
We are particularly interested in some of the columns colored, as following:
Record Code: Identifies the type of record.
Transaction Date: YYMMDD, As of Date of all accounts within the group.
Bank Account Number: we can do a verification to make sure the user is loading the correct file in the correct bank account.
Type Code: Identifies the type of balance (Summary/Transaction) being reported.
Funds Type: Indicates the availability of the Amount. It may occupy one field or several separately delimited fields. The first field is a single-character code that indicates the distribution of availability, and therefore defines any fields that follow.
These codes are:
Z = Unknown (default)
0 = Immediate availability
1 = One day availability
2 = Two-or-more days availability
V = Value dated
S or D = Distributed availability
Z, 0, 1 or 2 indicate the availability of the entire Amount.
If funds type = S, the next three fields are immediate availability amount, one-day availability amount, and more than one-day availability amount.
If funds type = V, the next two fields are date and time.
If funds type = D, the next field indicates the number of availability distributions and each following pair of fields indicate the number of days and the amount available, respectively. (Example: …,D,3,0,150,1,100,2,90,…)
This field will help us to determine the position of the Description.
Amount: Presented as Absolute value for the Detail records, with implied decimal places that are determined by the currency code, in our case we are assuming that we are always talking about USD, which have 2 decimal places, for example the amount 55599 is 555 integer and 99 decimal.
Customer Reference: The Customer Reference Number will be the Check number for the Type Codes 474, 475 and 395.
Text (Description): We're extracting the description from record type 16, and not considering the continuation record (88). An extra development needs to be done in order to take the complete description from record 88 if wanted.
For Bank Reconciliation you should have at least the Transaction date, the Amount and its nature, and the Description. The Customer Reference is an additional field that we want to extract when the transaction is a check paid.
Data exchange definition
When talking about importing CVS Bank Statement files through Data Exchange definition, you only configure one Line Definition for the single format of the bank statement file that you want to import, this means that the system is expecting just the detail transaction lines; nothing like header, body, footer, so, you will not be able to configure and distinguish each record type out of the box using Data Exchange definition.
Then, we have to create a new Reading XMLPort in order to manipulate the file in the way we need, the standard XMLPort is 1220 Data Exch. Import – CSV, we can use it as a base to build our own XMLPort.
The trick is to be able to identify each type/record and extract only the columns that we need. To do a basic version of the XMLPort, we need to at least:
Verify the Bank Account No. from Header record (Record Code 03 , column No. 2).
Extract the Transaction Date from the Header (Record Code 02 , column No. 5).
Extract the Amount from the Detail records (RRecord Code 16, column No. 3).
Extract the Customer Reference from the Detail records (Record Code 16, column No. depending of the Type Code), blank if it is not a check paid.
Extract the Description depending of the (Record Code 16, column No. depending of the Funds Type).
Skip the rest of the records.
Configure the Data Exchange Definition to work with our new XMLPort.
Let’s take a look at the code.
We can identify the Line Type and play with it to skip the records that we are not interested in.
In the InsertColumn function, we can get the Transaction Date and verify that the Bank Account No. in the file matches the one from where we are loading the bank statement.
Since the date isn't part of the detail lines, we will have to define a way to set it and identify it in the data exchange definition, so we can assign it the column No. = 100. In this way, we are sure that no other column will overwrite it.
Depending on the Type Code, we can manage the nature of the Amount and the Check number. Depending on the Funds Type, we can have the position for the Description.
Now we have to configure our new XMLPort in the Reading/Writing XMLPort field.
In the Column definitions, we set the column numbers we used in the code.
In the Field mapping we link the important fields. Note that the Customer Reference is linked to the Check No.
And, finally, this is the result:
You can do a more advanced version, playing with the Type codes to have more fields mapped, as well as creating tables to set up the transaction codes and its nature. This is a basic version that helps you solve the problem using the standard functionality.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.