Filtering excel on Column value

Help Activities
, , ,
1

Hi Team,

Can you pls suggest the solution for below query?

I have a column with the departments where i need to filter according to the dept write in seperate excel files

Column
Mech
Mech
CS
CS
ECE
ECE

I need to filter and i need to write mech ,Cs,ece in seperate excel files

Thanks,
Praveen

2

Hi @praveenchintu374,

Kindly check this

DataTableName.Select("Convert(Column, System.String)< >'"+"Cs"+"'").CopyToDataTable()

Thanks,
Rajkumar

3

I need to write filtered department data in respective excel workbook…

4

Hi @praveenchintu374

Kindly share Eg Input and Output

5

HI @praveenchintu374

  1. Use Read Range activity → Store it as Dtread

  2. Use Filter Data Table activity

Check out the image for the reference

image
image846×373 23.5 KB

  1. Use Write Range activity and pass the output from the Filter Data Table activity

Use Different excel file path.

Regards
Gokul

6

HI @praveenchintu374

Check out the skeleton of the XAML file

FilterDept.xaml (7.2 KB)

image

Regards
Gokul

7

HI @praveenchintu374

Try like this
use this expression to get the unique departments in another datatable.

Dt.DefaultView.ToTable(true,"Column")

this will get you the dt as

Column
Mech
CS
ECE

loop through this unique datatable.

use filter datatable in main datatable
in filter give like “Column” contains row(0).Tostring

write range the filtered datatable in the sheet name row(0).Tostring and in the excel file name as same row(0).ToString+“.xlsx”

Hope this helps

Regards
Sudharsan

8

But all the output will be in same excel file
…i need to write in different excel files

9

@praveenchintu374
This workflow will filter the MECH , CS and ECE department and write it in the different excel file.

Regards
Gokul

10

I am not sure what your task exactly is. But here is my project folder that separates mech cs and ece as excel files with their names. I hope this helps you get some idea at least.
departmentSeparate.zip (28.0 KB)

11

Follow this updated steps you will get your separate excel files for each department @praveenchintu374

Regards
Sudharsan

12

departmentSeparate.zip (29.3 KB)
@praveenchintu374 Hi, I think this would be the solution you wanted. This project will read department.xlsx and write each department name into separate excel files.

Best Wishes,
Sara