Input BAI2 Bank Statement Format

[Marc_Gozzett]

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

[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 

[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.

[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?