Get data from CSV

[dipon1112000]

hi I have data in a CSV file (not excel) and now I am trying to get it extracted with Regex but not sure how to do that.

example

1. I have data in csv file as
   AccountNo,Package,SrvCode,TaxType,MRC,TAX,TaxAmount
   ,
   “12345678,MP24LARGE”,", AAC AA1 AA1020 AAFCC Fee AA110.00 AAFCC AA.10
   AAC AA1 AA1020 AAFRANCHISE FEE AA110.00 AAFFE AA3.30

and I am trying to get it done as
AccountNo,Package,SrvCode,TaxType,MRC,TAX,TaxAmount
12345678,MP24LARGE,1020,FCC Fee,110.00,FCC,.10

can anyone help please
exaample3.txt (2.3 KB)

@rlgandu

[ashokkarale]

@dipon1112000 ,

Using Read CSV file and then retrieving data from it would be more precise and reliable solution here. Git it a try.

Thanks,
Ashok

[rlgandu (Rajyalakshmi Gandu)]

@dipon1112000

Use Read text file activity to read the csv file

[healsko_ho (Healsko)]

@dipon1112000
Assumptions:

1. 8 numbers for AccountNo.
2. 9-10 digits for Package
3. ‘AA AA AA AA AA AA AA’ is the end for each AccountNo and Package.

Please use below 2 steps.

1. Use below regex to get the groups for each account no and package.

"([\d]{8}),([\d\w]{9,10})"[\n\r.,"\s\w\d-]*?AA[\s]*AA[\s]*AA[\s]*AA[\s]*AA[\s]*AA[\s]*AA

2. Use below regex to get all transactions. Loop the result by groups for further data manipulation.

AA([\d]{4})[\s]AA([\w -])[\s]AA([\d.])[\s]*AA([\w]{3})[\s]AA([\d.])

[dipon1112000]

Hi bit confusing because in read CSV we pass the input to data table right. ?
But for regex to work we need input in .txt ?

In my case if I am doing read CSV then I am unable to use it in regex

Any idea how to do it

[rlgandu (Rajyalakshmi Gandu)]

@dipon1112000

Use “Read Text file” activity to read the CSV file

[healsko_ho (Healsko)]

Read CSV works well for structured data.
For the unstructured data like your case, read the whole data using ‘Read Text File’ is the first step, then regex or other data manipulation.