HOW TO EXTRACT

[dipon1112000]

hi i have a text file and i have a requirement to extract specific details from it. I am not sure how to extract each of them and pass them into variable so that i can create another csv (not excel) with each of these

I need to extract 1020 from AA1020, FC from AAFC, 110.00 from AA110.00, FCC from AAFCC, .10 FROM AA.10

as output i need to create a csv which will have
1020,FC,110.00,FCC,.10

can anyone advise how to get it done
example.txt (122 Bytes)

[ashokkarale]

@dipon1112000 ,

RegEx is you ultimate weapon here. Just be clear about the exact patterns around your data and data itself.

Thanks,
Ashok

[rlgandu (Rajyalakshmi Gandu)]

@dipon1112000

(?<=AA)\.?\w{2,}\.?\w*

What are the restriction to extract that data “AA” is common in your Output as look behind but other data also have this as same,You want only 4 or the data which has “AA”

[dipon1112000]

Only 5 not all so that I can pass each of them under different header

Ex
ColumnA,ColumnB,ColumnC,ColumnD,ColumnE
1020,FC,110.00,FCC,.10

[rlgandu (Rajyalakshmi Gandu)]

@dipon1112000

ColumnA---->(?<=AA)\d+(?=\s+[A-Z]{4}\s)
ColumnB--->(?<=AA)[A-Z]{2}(?=\s)
ColumnC----->(?<=\s\w{4}\s\w{3}\sAA)\d+\.\d+
ColumnD---->(?<=\.\d{2}\sAA)[A-Z]+
ColumnE---->\.\d{2}
System.Text.RegularExpressions.Regex.Match(Input,column1).Value

[mkankatala (Mahesh Kankatala)]

Hi @dipon1112000

Check the below xaml file

Sequence2.xaml (15.1 KB)

Make sure to change the input file names.

Hope it helps you.
Regards

[dipon1112000]

thank you but it is not working
example.txt (170 Bytes)

I have shared the text doc which is updated

[rlgandu (Rajyalakshmi Gandu)]

@dipon1112000

Sequence5.zip (2.3 KB)

Hope this will works for you,if not please update me

[dipon1112000]

Thank you I tried but output is coming blank

[mkankatala (Mahesh Kankatala)]

Hi @dipon1112000
I have given System.Text.RegularExpressions.Regex.Match(item.ToString,"(?<=AA)\d{4}").Value.Trim()

Change from D to d

Make sure to follow the same steps from the above workflow file without syntax errors. Hope you understand!! @dipon1112000

Hope it helps!!

[dipon1112000]

hi thank you very much but the output is coming empty so I am attaching the row text file that I am working on

example1.txt (1.5 KB)

[rlgandu (Rajyalakshmi Gandu)]

@dipon1112000

Sequence5.zip (2.7 KB)
Output

Hope it works

[rlgandu (Rajyalakshmi Gandu)]

@dipon1112000

Is it working for you or do you want any changes

[dipon1112000]

It’s not working

[rlgandu (Rajyalakshmi Gandu)]

@dipon1112000

Use Pipe symbol "|" not "\" and also add ‘“)”’ at the end of regex expression

(?<=AA)\w+\s*\w+(?=\s(Fee|FEE|TAX))

[pravallikapaluri]

@dipon1112000

Close the last brace.
Instead of \ before FEE give |

[dipon1112000]

I tried but it’s not highlighting

Thank you it’s my miss, only i question how about if the input file is a CSV instead of text then how to handle?