DataTable filtering with expressions

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.

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

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

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

Hi Arivu,

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

Regards,
Nandhini

@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:

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

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

Hi Arivu,

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

Can you please help me with that.

Kind Regards,

the join table you sent wont work because my tables dont have anything in common. I just need to get them together as 1 datatable

Hi @jntrk,

Use join data table activity to merge two tables.

Regards,
Arivu

Hi i’m sorry, but able to convert this in VB please?
i kept encounter errors.

Hi @winnie_toh ,Use Filter data table activity to filter the data table.
Else try below syntax
FinalDT1.Select("[Received Date]<='"+LastRunDateDate.ToString("M/dd/yyyy")+"' ").CopyToDataTable()

Filter Data Table (uipath.com)
4 ways to filter the data table find the below path

Four Ways to Filter Data Tables in UiPath Studio | UiPath

Regards,
Arivu

1 Like

Thank you so much. I tried different solutions but this one worked: Duplicate records from the same data table if you want the specific column alone. This provides both duplicates. If I only want to show the one duplicate and not both, can your code be modified vs adding another assign activity? Thanks again

(From p in dt.Select() where( From q in dt.Select() where q("ColumnName").Equals(p("ColumnName")) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Regards,
Arivu

Thank you for your reply