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

67 Likes
Duplicate rows in data table
Linq in Data Tables
How to remove blank row from dataTable
How to group repeated email ids present in one column of datatable
How to get duplicate rows for specific column in a datatable
Deleteing the whole row if ( condition)
Remove duplicates before inserting into database
Get distinct value in column excel
How to Remove Duplicate Columns in Excel and have the distinct values in the other sheet
Update datatable
Datatable return a row item value if column equals something
No Sort/Filter Data Table in UIPath
Remove Empty Rows From DataTable
Search for column value and merge data cells
How can I find all the values in the duplicate column and merge the duplicate rows?
URgent Can We merge two collection into 1
Sort elements in a excel document and save it in a new sheet
Compare rows from one excel to other excel
Removing empty rows from excel sheet or datatable
How to find blank rows
How to sum all duplicates in a column
How to create a re-usable component in uipath?
Group by - Data table
How to judge the dataTable's all the cell was blank?
Check first row of the table:if yes allow the whole dt through the loop
Filter Datatable - Not like filter
Filter Table not executing
How to remove duplicates in excel on keywords
Compile error when use CopyToDataTable() activity
How to dynamically read a excel range in a for each row loop?
Drop datatble
Anchor Base - Bot does not stop
Excel Read Row activity - check if blank/no data returned (ReFramework)
Group data in datatable and count amount of data
Checking Duplicate rows depending on the condition
How to pass WHERE condition in Datatable.defaultview.totable()
How to remove duplicates in an excel
Filter error in activities also
How can I filter a DataTable?
[QUERY] FilterDataTable Activity
How to remove empty cells from datatable?
Delete empty row from Datatable
Remove data row activity is not working in for each loop
Deleting rows from a sheet after filtering
How to delete Empty Rows in Excel
Excel Automation process
How to check if there are duplicates in the rows of Data Table
Remove duplicate database error
How to Remove Duplicate Columns in Excel and have the distinct values in the other sheet
Merge two datatables with common column

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:

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® 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

@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

1 Like

@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