Input BAI2 Bank Statement Format

Has anyone any suggestions or has already worked through how to input BAI2 bank statement files?

The files is a txt file but inputting as a delimited file, but getting error on number of fields on line 3.

Then I will need to restructure the data to apply each item to the correct bank account. suggestion there would also be much appreciated.

Thanks

Input

Tips and Tricks

Accepted answers

All comments

flying008

Hi, @Marc_Gozzett

Maybe you can upload sample-data.txt for show the detail.

Marc_Gozzett

Please see dummy file, due tot the nature of the files i have amended some parts of the file but the structure is largely the same

BAI.ORC.DUMMY.txt

Raj

can you share the required format as well

Yoshiro_Fujimori

Hi @Marc_Gozzett ,

I got the specification of the format and the sample data from https://bankrec.westpac.com.au/docs/statements/bai2/#bai2-statement-format.

As it is not a normalized data format, it needed to be diaplayed in several tables.

As the format expects repetitions at multiple levels, I do not know if this workflow supports all the cases.

So you should take this as just a start point and verify with the live data.

It is a little big workflow, but the concept is simple - You split the records for each format of the "Record Code".

Workflow

Output image

You can change the format as you like with the configuration of Table tool.

Good luck.

BAI2_Parse.yxzp

Raj

Great logic !!!

BrianT_FnD

This doesn't quite work. Each 3 record is a header for each 16 records. so the 3 records need to be in the same row as the associated 16 records. The way it is right now, you have no way of knowing to which account each 16 records belongs. The deposit date only lives in the 2 record, so you'd have no way of knowing the deposit date without including that with the 16 records. I written a couple C# programs to parse out BAI format and it is a pain in the butt, especially when banks don't 100% abide by the standard. I'm trying to figure out how to do this in Alteryx at the moment, which is how I ended up here.

01 - File Header

02 - Group Header (can repeat)

03 - Account Header (for Accounts in above Group)

16 - Transaction Detail (for transactions in above Account)

88 - continuation record for overflow on above 16 record (0-N)

49 - Account Trailer

98 - Group Trailer

99 - File Trailer

In order to capture all attributes of each transaction in the sample file, the output table would have to be:
01 02 03 88 16 88 88

01 02 03 88 16 88 88

01 02 03 99 16 88 88

The 49, 98, and 99 records are just control totals so wouldn't necessarily have to be in each row.

Edit: For anyone interested, the official spec lives here:
https://www.bai.org/docs/default-source/libraries/site-general-downloads/cash_management_2005.pdf

rushabhshah

You're perfectly right Brian.

I have another problem that I was hoping to get some help with.

Is there a way to sequence the bank statements for each account? There is no sequence number present in these like an MT940 or a CAMT have.

Once avenue was to check that opening balance is matching the previous closing balance. But, if you have accounts setup with day end sweep outs, the balance is always 0.

And if there was accounts which do not have transactions each day, we can't check whether a statement has been received daily.

Is there something fundamental that I am missing with this BAI file format?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703