DataTable filtering with expressions

News Tutorials
, , ,
#23

Write the same expression separately for selecting each individual column to convert to array.

Create a variable of type System.Collections.Generic.IEnumerable<system.string>

Write an assign to append each array to the Collection e.g. Collection.Append(Array1)

This will provide you a list of string arrays e.g. {[A,B,C],[D,E,F],[G,H,I].

Alternatively if you want to have a single list of individual elements e.g. {A, B, C, D, E, F, G, H, I} then each time you create an individual array from a column use a ForEach Loop through that array and append it to the collection one by one (make sure to change the collection type to string rather than string).

There is no doubt a more elegant way to do this but it will get you there.

#24

Hi Arivu,

Could you please help to construct a query for below scenario.

I have to take all the unmatched records from a Datatable and assign a specific value to one column in the unmatched data table.

Regards,
Tamilselvi K

#25

Hi @arivu96,
I have tried this code that you give:
DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

However, I encounter this error:
cast is not a member of system.data.rowcollection

#26

if I want to filter a data table an just get rows which have a date oldest than 01/07/2019

#27

Hi Arivu,

Im getting “Exception has been thrown by the target of invocation” error while assigning primary key.

Regards,
Nandhini

#28

@arivu96 Can you please provide me query to Remove Duplicate record from the Data Table based on 2 Column Names?

With below query I can remove duplicate rows based on 1 column name.
dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)(“ColumnName”)).Select(Function(g) g.First).CopyToDataTable()

but actually I want to remove duplicate records from the dataTable based on 2 column values so don’t know how to add another column in above query or if you can provide me the query to achieve this would be great.

In this example I want to remove duplicate records based on Column1 and Column3:

Input DataTable:
Column1 | Column2 | Column3
1234 | abc | Test1
1234 | xyz | Test1
1234 | pqr | Test2
5678 | abc | Test2
5678 | xyz | Test2
5678 | pqr | Test1

Output DataTable should be:
Column1 | Column2 | Column3
1234 | abc | Test1
1234 | pqr | Test2
5678 | abc | Test2
5678 | pqr | Test1

Thanks you,
Navneet
Happy Automation! :slight_smile:

#29

Hello Thanks a lot , so helpful ,just like
(From p in dt.Select() where( From q in dt.Select() where q(“name”).Equals(p(“name”)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

can i get a code that select where (“name”) is not equal to (“name”)

#30

Remove rows based on particular column is empty.**
DataTableName.Select("Convert(column3, System.String)< >''").CopyToDataTable()
or
DataTableName.Select("column3< >''").CopyToDataTable()

Here can we use column number in the query instead of column mane

1 Like
#31

Hi Arivu,

I need to filter the latest date from Data table column “Date”.

Can you please help me with that.

Kind Regards,