How to Import BAI2 File Format Data into Microsoft Dynamics 365 Business Central

by | Updated January 7, 2021 | 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.

Related Posts


Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events


01sep10:00 am10:30 amEnsuring Quality and Compliance for Batch Manufacturers in Life Sciences

01sep12:00 pm12:30 pmIs it Worth It to Move to the Cloud? A Look at Considerations for Current Agribusinesses Using Dynamics GP

01sep2:00 pm2:45 pmWhat’s New in Dynamics 365 Finance and Supply Chain

09sep11:00 am12:00 pmConfab Live with Stoneridge – Data Strategy and Reporting – Mining Decision Making Insights

15sep10:00 am11:00 amSolving the Biggest Challenges in Agribusiness Through Innovation and Technology

22sep12:00 am12:30 pmSimplifying Payroll and HR Management with ADP Workforce Now

22sep10:00 am10:30 amStreamlining Batch Manufacturing with Technology

22sep2:00 pm2:30 pmProcess Automation for Microsoft Dynamics D365 for Business Central, Finance and Operations and GP

23sep11:00 am12:00 pmConfab LIVE with Stoneridge - Riding the Wave 2 Release – Key Features Coming to Dynamics 365 this October

29sep10:00 am11:00 amTop Five Reasons Why NOW is the Right Time to Move from Salesforce to Dynamics 365 Customer Engagement

29sep12:00 pm12:45 pmUnderstanding Job Costing and Tax Management in Business Central

29sep2:00 pm3:00 pmDigitalizing Horticulture & Agriculture - How to Sell Plants Online and Simplify Business Management

30sep12:00 pm4:00 pmSecurity and Permissions Training for D365 Business Central or Dynamics NAV


06oct10:00 am10:30 amPreview of D365 Business Central Fall Release Features and Functionality

06oct12:00 pm12:30 pmInsider's Guide to New Features Available in the Fall Release of D365 Finance and Supply Chain

07oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Technical

21oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Functional

26oct(oct 26)9:00 am28(oct 28)5:00 pmStoneridge Connect Leadership and Community Conference

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!