DataTable filtering with expressions

[mudunura (Rakesh Mudunuri)]

Hi arivu96

Really i have got good info from this and it worked also for me.

but i was not able to understand this code completely i am not that much good at dotnet.

could you please give some explanation on this or where can i find stuff something like this.

Thanks

[joedenly]

getting very helpful from this information.

[838486 (Abhash)]

@arivu96

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

Hi arivu ,

Can you help me with getting Distinct values Based on the specific column index from the data table.

Thanks

[arivu96 (Arivazhagan A)]

Hi @838486,
First filter the datatable using skip method like below then use the same query

For example:
DataTable.AsEnumerable().Skip(2).CopyToDataTable()
Or
DataTable.Select().Skip(2).CopyToDataTable()

Regards,
Arivu

[kaliraj1982 (Kali Raj)]

@arivu96

((From LineNo In dt.DefaultView.ToTable(True,“Product”).Select().ToList() Select (From row In dt.Select Where row(“Product”).ToString=LineNo(“Product”).ToString Select row).ToList(0)).ToList()).CopyToDatatable()

Good info !!!

[mkriznjak (Matej)]

Hi @arivu96 ,

can you please help me with this?

Puting variable in expression

I need to use an expression for filtering Datatable. But in that expression, I would like to use variables. Here is my expression: (From p In dt.Select Where p(5).ToString.Equals(filtered_array(0).ToString) Or p(5).ToString.Equals(filtered_array(1).ToString) Select p).CopyToDataTable I need to use variables because I need to build this expression based on an array, and I don’t know what is the length of an array. So I have in mind to build part of expressions based on an array and save it t…

Thanks!

[Varshini (R.K.VARSHINI)]

@arivu96 I have used below expression to select rows in a datatable that contain todays’s date in “Start Date” column

OutDT.AsEnumerable.Where(Function(r) If(IsDate(r(“Start date”).ToString.Trim), Convert.ToDateTime( r(“Start date”).ToString.Trim ) = TodayDate, False) ).CopyToDataTable

Out of the above filtered datatable I want to select rows which contains “Start Time” more than 2 hours from the current time and copy it to a datatable,Means i need to find the difference between every start time field and current time, if it is more than 2 hours, i need to select those rows…Can you please help me with an expression for that.

Sample data provided below.

JobName Job CreatedBy Status Start date Start Time
Test Job TestBOT Finished 10/8/2019 12:05:53 AM
Test Job TestBOT Finished 10/8/2019 12:05:55 AM
Test Job TestBOT Finished 10/7/2019 3:00:27 AM

[arivu96 (Arivazhagan A)]

Hi @Varshini,
use below code
dt.Select("[Start date] like '"+Now.ToString("M/d/yyyy")+"%'").CopyToDataTable()

Regards,
Arivu

[Varshini (R.K.VARSHINI)]

@arivu96 Thanks for the expression

Actually i want an expression to filter out “Start Time” column which have time more than 2 hours from the current time and copy it to a datatable,Means i need to find the difference between start time value in each row to the current time, if it is more than 2 hours, i need to select those rows.

The difficulty is that values in a datatable is in string so we need to first convert it to datetime to find the difference in hours…The rows may not contain datetime, so we need to put a check before converting it to datetime.

Thanks in advance,
Varshini

[arivu96 (Arivazhagan A)]

Hi varshini,

Then try below query and let me know

(From row In dt.Select Where row(“Start date”).ToString=Now.ToString("M/d/yyyy") and Now.Subtract(Convert.ToDateTime(row(“start time”).ToString)).TotalMinutes>120 Select row).CopyToDataTable()

Regards,
Arivu

[Varshini (R.K.VARSHINI)]

@arivu96

It did worked, Thanks so much for your help!!

Best Regards,
Varshini

[DawnW (Dawn Wessell)]

I have a spreadsheet that has many columns with headers showing goods that have been booked in.

Currently i read the whole range of the spreadsheet. Then in a try catch i assign a DataRow variable with the value:

DT.Select(“PO_NO=” + po)

(Where PO_NO is the Column Header & po is a variable created earlier that holds the PO number that i’m looking for in all the data on the spreadsheet)

This works as it is, but i’d like to be able to expand the value in the assign activity to include another column header and variable to increase the accuracy of the search. I thought it would be something like:

DT.Select(“PO_NO=” + po) and (“DNote=” + dn)

However this is clearly not working, do i need to repeat the DT.Select proportion or maybe use “and also” rather than just the “and”??? Or maybe i need to filter once by the PO save those results somewhere then filter again by the Delivery Note???

Or am i completely off the mark???

Also is there a way of either saving the filtered rows from the initial spreadsheet once the 2 criteria have been met, into a new spreadsheet or new DT…? As i am going to need to append this once i have the correct data.

Any help would be greatly appreciated
Thanks in advance
Dawn

[rookies (Ed)]

hi @arivu96

How to determine whether the table only has empty row or not? (not using for each row as it will be better to do it on one go)

[m.abdullatif (Mohamed Abdullatif)]

I have a data table with 2 columns ,I need to remove duplicates from first one and get the max value from second one

[AshwinS2 (Ashwin S)]

@arivu96

(From p in dt.Select() where( From q in dt.Select() where string.Join(",",q.ItemArray).Equals(string.Join(",",p.ItemArray)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Hi @m.abdullatif

Use remove duplicate row and based on int MaxValue=Convert.ToInt32(dt.AsEnumerable().Max(Function(row) row(“Column2”)))

Thanks
ashwin S

[Pradeep_Shiv]

@arivu96

Distinct

hey i want remove duplicate row based on keywords or particular string value
i’ve to retain the first one then remove the rest

column1 | column2 | column3
abc | abc | 123
123 | abc | 456
123 | abc | 456
abc | abc | 123
abc | abc | 123
abc | abc | 123

so my output should be
column1 | column2 | column3
abc | abc | 123
123 | abc | 456
123 | abc | 456

[AshwinS2 (Ashwin S)]

@arivu96

DataTable.AsEnumerable().Skip(2).CopyToDataTable()

Hi @Pradeep_Shiv

Use
DataTable.AsEnumerable().Skip(3).CopyToDataTable()
and then store the values in Dt

and then print the values

Thanks
Ashwin S

[Pradeep_Shiv]

column1 | column2 | column3

123 | abc | 456
123 | abc | 456
abc | abc | 123
abc | abc | 123
abc | abc | 123

First row is removed ,i have to keep the first occurance then delete the rest

[debashishforyou (Debashish)]

I am using the below query to convert one column of my datatable to string array :

String array = (dt2.AsEnumerable().Select(Function(a) a(5).ToString)).ToArray

I need to convert mulitple columns (3 columns from the datatable into string array) using the above query, Please help

[ronanpeter]

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.