DataTable filtering with expressions

Hi All,

Use below code to remove empty row from the table.
DataTable name->DataTableName
Use assign activity
DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

Re-usable Component pass Parameter as datatable you will get the answer.

RemoveEmptyRows.xaml (8.5 KB)

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

Distinct Based on the specific column name from the data table and need to return the hole data.
Remove Duplicate record from the Data Table based on Column Name
dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)("ColumnName")).Select(Function(g) g.First).CopyToDataTable()
Or
((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()

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

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

Sorting the data table based on particular column name
dt = dt.Select("",[ColumnName] asc").CopyToDataTable()

Duplicate records from the same data table
(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()

Duplicate records from the same data table and
If you want specific Column alone mention the column name

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

Get Max value from the data table column value
int MaxValue=Convert.ToInt32(dt.AsEnumerable().Max(Function(row) row("Column2")))

Join Two Data table
JoinTables.xaml (10.6 KB)

Regards,
Arivu

128 Likes

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

getting very helpful from this information.

Hi arivu ,

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

Thanks

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

1 Like

Good info !!!

Hi @arivu96 ,

can you please help me with this?

Thanks!

@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

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

Regards,
Arivu

1 Like

@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

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

4 Likes

@arivu96

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

Best Regards,
Varshini

2 Likes

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

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)

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

Hi @m.abdullatif

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

Thanks
ashwin S

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

Hi @Pradeep_Shiv

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

and then print the values

Thanks
Ashwin S

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

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