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

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.
image
image

can you post the excel file here?

Hi Jack Chan,

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

Expecting output like as below
image

EE_Details.xlsx (8.7 KB)

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)
    image

  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
    image

  4. calculate grand total

  5. write newDt to new excel
    image

Result:
image

1 Like

Hi Jack Chan,

Really Thanks for your support!!.

Its really working fine now. Thank you!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.