Regex for bank statments

[cterrence]

Hi All

I am a starter in Regex and would help in splitting the bank statements

16,399,217160,,DEPOSIT - CASH 64001,,64001_TRAN EFF DATE 230131
16,399,840663,,DEPOSIT - CHEQUE(S) 64001 #Chq:1,,64001 #Chq:1_TRAN EFF DATE 230131
16,399,234115,,DEPOSIT - CASH AGENT NO 63201,,AGENT NO 63201_TRAN EFF DATE 230131

16,399,40320,,DEPOSIT - CASH SMGB 43001,,SMGB 43001_TRAN EFF DATE 230131
16,399,162980,,DEPOSIT - CASH SMGB43001,,SMGB43001_TRAN EFF DATE 230131

I had tried with (\d{2}),(\d{3}),(\d+),,(\w+-\w+)(\d+),,(\d+)_(\w+)(\d+) but got empty rows

The output should be

16         399        217160     DEPOSIT -CASH      64001       64001    TRAN EFF DATE    230131 

16        399        840663      DEPOSIT - CHEQUE(S)      64001     #Chq:1    64001 Chq:1     TRAN EFF DATE        230131

16        399        234115      DEPOSIT - CASH AGENT     63201    AGENT NO          63201       TRAN EFF DATE    230131

16        399        40320         DEPOSIT - CASH SMGB        43001    SMGB 43001  TRAN EFF DATE   230131

Thanks

CT

[mceleavey]

Hi @cterrence ,

What you have requested includes rows which are out of line and have a different number of columns, but those that are not represented on each row are removed with the data shifted left. To get around this you may need to transpose the data to retain the blank columns on the cross-tab, but you can do that.

With regards to your immediate problem I've attached a workflow which starts  by splitting out into columns based on varying delimiters, I've then applied regex to the remaining columns.

Currently the workflow produces the following:

This is as far as I can go before you will need to split the columns out as you have different data in the same column.

I hope this gets you over the initial hurdle though.

M.

[flying008]

Hi, @cterrence 

What is your want output data form? please show it by a table as below, not paste a text.

Input	1-Num	2-Num	3-Num	4-Type	5-ID	6-Ver	7-EFF	8-Date
16,399,217160,,DEPOSIT - CASH 64001,,64001_TRAN EFF DATE 230131	16	399	217160	DEPOSIT - CASH	64001	64001	TRAN EFF DATE	230131
16,399,840663,,DEPOSIT - CHEQUE(S) 64001 #Chq:1,,64001 #Chq:1_TRAN EFF DATE 230131	16	399	840663	DEPOSIT - CHEQUE(S)	64001	64001 #Chq:1	TRAN EFF DATE	230131
16,399,234115,,DEPOSIT - CASH AGENT NO 63201,,AGENT NO 63201_TRAN EFF DATE 230131	16	399	234115	DEPOSIT - CASH AGENT NO	63201	AGENT NO 63201	TRAN EFF DATE	230131
16,399,40320,,DEPOSIT - CASH SMGB 43001,,SMGB 43001_TRAN EFF DATE 230131	16	399	40320	DEPOSIT - CASH SMGB	43001	SMGB 43001	TRAN EFF DATE	230131
16,399,162980,,DEPOSIT - CASH SMGB43001,,SMGB43001_TRAN EFF DATE 230131	16	399	162980	DEPOSIT - CASH SMGB	43001	SMGB43001	TRAN EFF DATE	230131

[cterrence]

Thank you very much !!!