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

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

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