How to filter record?

excel
studio

#1

i have one excel file with salary details column. i want to extract the data from excel file, those who are getting salary more then 30,000 in one excel sheet and those who are getting salary less then 30,000 in another excel sheet???


#2

@MKRoy, Declare your datatable as dt.

Filtering (Assign Activity)

  1. Datatable dtLessthan30000 = dt.Select(“Convert(ColumnName,System.Double) <
    ‘30000’”).CopyToDatatable

  2. Datatable dtMorethan30000 = dt.Select(“Convert(ColumnName,System.Double) > ‘30000’”).CopyToDatatable

Output (Write Range)

  1. Write dtLessthan30000 in sheet1
  2. Write dtMorethan30000 in sheet2

Note: Conversion used is a preventive measure ! Even if your column is in string or someother datatype this will convert it to a double format. If your column is already in integer/double, giving your columnName alone is good enough.

Regards,
Dominic :slight_smile:


#3

Hello @MKRoy,
I hope it will work for you Untitled


#4

Thank You so much Dominic. Still i am facing little problem. I am attaching screen shots. when i was using assign activity but i got an error of cannot assign from type ‘System.Data.DataRow[]’ to type ‘System.Data.DataTable’.


#5

@MKRoy, Think you have forgotten to use this after the query.

If not you might have declared dtLessthan30000 and dtMorethan30000 as Datarow[]

Regards,
Dominic :slight_smile:


#6

Thank you Manish for help!!!


#7

Hello Dominic, one more error is coming. That is “cannot find coulmname”


#8

@MKRoy, You need to replace the columnName with your columnName (like salary details)

Regards,
Dominic


#9

ya…i have done that thing earlier only. In my excel file i have name, salary and job column and assign activity i have given ‘dt.Select(“convert(Salary,System.Double)<‘30000’”).CopyToDatatable’. Before assign activity i have used read range from their only ‘cannot find coulmn name’


#10

@MKRoy, Confirm once that you have checked Add Headers property in read range. If not enusre there is/are no space(s) in the column name.

Regards,
Dominic :slight_smile:


Is it possible to create one Workflow for the below attached example?
#11

Thanks…Dominic. It is working now.Once again thank you very much.