Filter Multiple Items in same Row and write to csv/xlsx

[BaskaranVenkatesan (Baski)]

Hi All,

Hope all are doing good and safe!!

input csv file have multiple rows and columns. I want to filter particular column and write it to csv/xlsx. First i had done the “Read csv” file and stored into Dt. Then using Build Data Table activity created the first column as City and 2nd column as Count. Now i want filter the City name and update into build dt count column. I had tried the filter data table but i’m able to get only one count in writeline like “DT.rows.count.tostring”. Please advise.

[jack.chan]

can you post the excel file here?

[BaskaranVenkatesan (Baski)]

Hi Jack Chan,

Thanks for your prompt response. Please Find my input file for your ref.

Expecting output like as below

EE_Details.xlsx (8.7 KB)

[jack.chan]

Thanks for the excel . I have a made a sequence to create your result
Tester.xaml (15.7 KB)

Steps:

1. read excel (dt) and assign list of unique cities to a list
   

   

2. build new result datatable (newDt)
   

3. loop the list of unique cities
   a) filter datatable (filteredDt) to only get rows where city = current city in loop
   b) create new datarow, where City = current city in loop, Count of City = number of rows in filteredDt
   c) add new datarow to newDt
   

4. calculate grand total
   

   

5. write newDt to new excel
   

Result:

[BaskaranVenkatesan (Baski)]

Hi Jack Chan,

Really Thanks for your support!!.

Its really working fine now. Thank you!