Regex for bank statments

Question

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

AL.JPG

cterrence · Answer

Thank you very much !!!

flying008 · Answer

Hi, @cterrence

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

Input1-Num2-Num3-Num4-Type5-ID6-Ver7-EFF8-Date16,399,217160,,DEPOSIT - CASH 64001,,64001_TRAN EFF DATE 23013116399217160DEPOSIT - CASH6400164001TRAN EFF DATE23013116,399,840663,,DEPOSIT - CHEQUE(S) 64001 #Chq:1,,64001 #Chq:1_TRAN EFF DATE 23013116399840663DEPOSIT - CHEQUE(S)6400164001 #Chq:1TRAN EFF DATE23013116,399,234115,,DEPOSIT - CASH AGENT NO 63201,,AGENT NO 63201_TRAN EFF DATE 23013116399234115DEPOSIT - CASH AGENT NO63201AGENT NO 63201TRAN EFF DATE23013116,399,40320,,DEPOSIT - CASH SMGB 43001,,SMGB 43001_TRAN EFF DATE 2301311639940320DEPOSIT - CASH SMGB43001SMGB 43001TRAN EFF DATE23013116,399,162980,,DEPOSIT - CASH SMGB43001,,SMGB43001_TRAN EFF DATE 23013116399162980DEPOSIT - CASH SMGB43001SMGB43001TRAN EFF DATE230131

mceleavey · Answer

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.

regex_mceleavey.yxmd