Get distinct value in column excel


#1

Hi all,

I have an excel files with a column which has
DEPARTMENT

Admin
Admin
Admin
BA
BA
BA
CA
CA
CA

And I would like to get the distinct value of the department column, how do i go around it?

Thanks.


#2

hi @saraawq
For distinct column values you can use the below query
Datatable dt=dt.DefaultView.ToTable(true, “DEPARTMENT”) in assign activity

Thanks
Ashwin S


#3

Hi @saraawq,

Refer this one for datatable filter

Distinct Based on the specific column name from the data table.

DataTable dt=dt.DefaultView.ToTable(true, "DEPARTMENT")
or
DataView view = new DataView(table)
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...)
or
DataTable =DataTable.DefaultView.ToTable(true)

Regards,
Arivu


#4

Hi @AshwinS2,

Thanks! But i got this error


#5

Or it is because I’ve been using read range to get my data table?


#6

@saraawq

Check Add headers property in Read Range Activity

Regards,
Mahesh


#7

@saraawq I think you are passing different DataTable variable


#8

it has the headers!


#9

image

it is the same datatable variable :confused:


#10

image


#11

@saraawq
Once check in the excel that column name might have spaces at that start and ending.
Regards,
Mahesh


#13

@saraawq can u attach workflow


#14

@saraawq

And one more thing I think you are using the query inside Excel Application Scope… Please check the same by using outside of it.

Regards,
Mahesh


#15

I got it to work, but how do i print out to know that it has the distinct values, cause when i message box, it doesn’t show any value.


#16

I got it to work, but how do i print out to know that it has the distinct values, cause when i message box, it doesn’t show any value.
Thanks!


#17

my variable is an object
image


#18

image

my variable ‘dataDept’ is an object


#19

@saraawq
Datadept should be datatable type.

Regards,
Mahesh


#20

HI @saraawq,

use output data table activity to convert the data table to string and print it.

Regards,
Arivu


#21

@saraawq
and if you want print your datatable in output panel or in message box. use Output DataTable Activity and use the string in message box

Regards
Mahesh