I’m facing an issue with filtering my excel data. I have a excel with 5000+ data every day data will be increasing but the difference is date. Every day data will be increasing with current day date.
Now my scenario is i want to get only yesterday date data. I’m trying to do it with LINQ query i’m unable to get it correctly. Issue is there is 1 empty row in top because of that i’m getting error. I’m attaching my sample excel below can anyone help me on that.
Note : My Date column name is : Order Date
I’m using below code but it is giving me error.
MyDatatable.AsEnumerable().Skip(1).Where(Function(row) (row(“Order Date”).tostring = yesterdaydate.Tostring)).CopytoDatatable()
Yesterdaydate = “05/23/2023” ---- This is String (DataType)
Test File.xlsx (30.0 KB)
Just use the Filter Datatable activity to get the rows you want.
Hi @postwick getting error with that. I’m unable to filter. I think i’m missing something which i’m unable to solve that. As i have 1st row empty and i should not delete that.
Post screenshots showing how you have the Filter Datatable activity set up. Why do you need a blank first row?
@postwick The excel i should not change anything. I need to keep it as actual. I just need to pick the data and filter. If i use row index for Filterdatatable activity it is giving me Null.
If i use column name it is giving Column name is not set or is invalid
Then you’re not configuring it correctly. Post a screenshot. We can’t help you if you don’t show us what you’re doing.
If you writeline you dates you may see your problem maybe, when you read your excel, the date format is 05/23/2023 00:00:00
You can Read Range the Excel without headers
Filter Data Table using Column index 2 = Today.AddDays(-1)
Write Range Filtered Data Table
Hi @michael.zura if use this code it is working but here i’m filtering with row index but in future if they change the row it will cause an issue right. Is there any option that where we can do it with LINQ. I have tried but getting error with linq it is adding some additional headers with LINQ. I’m attaching my flow below. Please have a look.
Excel_Filter.zip (160.6 KB)
Hi @ppr . I tried to modify and apply this. I’m unable to make it working as per my requirement. Can you please help me on this. I have attached my Code in above message.
Can anyone help me on this. Any leads how to solve this Using LINQ. I’m able to filter this when i don’t get first row empty. But here my challenge is i should not delete that first empty row.
Excel_Filter.zip (160.6 KB)
This code is working fine for data without having empty row. But i need it even if i get 1st row empty also it should work.
ReadDt.AsEnumerable().Where(Function(row) (row(“OrderDate”).ToString = InputDate.ToString)).CopyToDataTable()
Any help from @Yoichi , @Anil_G , @vishal.kp , @ppr , @Harshith_Adyanthaya , @arjunshenoy , @Palaniyappan , @Srini84 , @Jithesh_R , @Nitya1 , @Gokul001
Please try this
ReadDt.AsEnumerable().Where(Function(row) (row("OrderDate").ToString = InputDate.ToString) OrElse (ReadDt.Rows.IndexOf(row).equals(0) AndAlso row.ItemArray.All(function(x) IsNothing(x) OrElse String.IsNullOrEmpty(x)))).CopyToDataTable()
Hi @Anil_G , I’m getting error for this. Can you check this once.
My input date variable is string = “05/24/2023 00:00:00”
and I hope when you say empty first row whole of the row is empty right or only the column you are checking?
We recommend to take the debugging course from the UiPath Academy and get familiar with the different debugging panels e.g.
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum
With these techniques a lot of writelines, output data table… can be avoided and very quick and direct the things are explored.
We also recommend to filter on a datetime base and not on strings when it is about dates
have a look here:
@Anil_G After changing that code error gone but it is giving me this - Assign: Column ‘OrderDate’ does not belong to table DataTable.
Please chekc and correct the order date column name…may be it has spaces or so…check properly
Hi @ppr This is working fine but it is based on rowindex working. In future if they change row position it may cause an issue right. is there any option where i can use my column name.
you can use the column name, can calculate dynamic the offset and also reset the default col names to custom col names. Here we do not see any technical limitations
@ppr if i use column name instead of column index it is giving me error.
Assign: Column ‘OrderDate’ does not belong to table DataTable.