Need to filter Excel Data based on Date

[Learner007 (Uipath Learner)]

Hi Everyone,

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)

[postwick (Paul)]

Just use the Filter Datatable activity to get the rows you want.

[Learner007 (Uipath Learner)]

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.

[postwick (Paul)]

Post screenshots showing how you have the Filter Datatable activity set up. Why do you need a blank first row?

[ppr (Peter Preuss)]

[FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues

This FirstAid Tutorial will describe how DateTime parsing issues can be analyzed and handled. Click spoiler text when in hurry: Example Assignment Following Excel data will be processed and the corresponding Quarter is to calculate for the particular task date Input data: [input_raw] Quarter Calculation Quarter = CInt(Math.Ceiling(YourDateTimeVar.Month / 3 )) Initial Implementation [initial_flow_raw] tmpTaskDate = DateTime.ParseExact(row("Date").toString.Trim, "dd.MM.yyyy", Culture…

[Learner007 (Uipath Learner)]

@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

[postwick (Paul)]

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.

[michael.zura (GuardiansFan)]

@Learner007
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
Regards

[Learner007 (Uipath Learner)]

@michael.zura

You can Read Range the Excel without headers
Filter Data Table using Column index 2 = Today.AddDays(-1)

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)

[Learner007 (Uipath Learner)]

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.

[Learner007 (Uipath Learner)]

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

[Anil_G (Anil Gorthi)]

@Learner007

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

cheers

cheers

[Learner007 (Uipath Learner)]

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”

[Anil_G (Anil Gorthi)]

@Learner007

Please use String.IsNullOrEmpty(x.ToString)

and I hope when you say empty first row whole of the row is empty right or only the column you are checking?

cheers

[ppr (Peter Preuss)]

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:

[Learner007 (Uipath Learner)]

@Anil_G After changing that code error gone but it is giving me this - Assign: Column ‘OrderDate’ does not belong to table DataTable.

[Anil_G (Anil Gorthi)]

@Learner007

Please chekc and correct the order date column name…may be it has spaces or so…check properly

Cheers

[Learner007 (Uipath Learner)]

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.

[ppr (Peter Preuss)]

@Learner007

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

[Learner007 (Uipath Learner)]

@ppr if i use column name instead of column index it is giving me error.

Assign: Column ‘OrderDate’ does not belong to table DataTable.