I have an excel sheet where there is a column called due date. i need to filter that column based on a value . Say 7. Meaning , from today it has to go back 7 days and retain rows only for those number of days
Hi @shilpa_p ,
When it comes to filtering you can either achieve it using the Filter DataTable Activity(most preferred), but if you have to perform any sort of conversion(like from string to date) then you have to either use a For Each + If And Add those rows to a new datatable, or use LINQ.
I understand that you want to filter the Due Date Column based on a value, but its the second half that is a little confusing.
Its difficult for us to provide you with an appropriate end to end resolution unless you provide us with some sample data and an example of the operation you want the code to perform.
excel_Dates.xlsx (221.5 KB)
Here is my excel sheet.
Filter wizard didnt work for me. Looking for alternative solution
Eg: if todays date is 28-03-2022, and a value say x=4, then it should filter and keep the rows between 25-03-2022 to 28-03-2022
Hi @shilpa_p ,
We could try following the below Steps :
Read the Excel file using
Workbook Read RangeActivity and get the Datatable, say
The Number of days value, can be stored in an Integer variable, say
Next create a variable, of Array of DataRow, say
AssignActivity, we could use the below Linq Expression :
arrayRow = DT.AsEnumerable.Where(Function(x)Now >= CDate(x("Due Date").ToString) > Now.AddDays(-x)).ToArray
- Next using an
IfActivity, we could Check if there are any rows in
arrayRowvariable, then Convert the Array row into Datatable and assign it to another datatable variable, say
The Condition in If :
Then Part of If, use the Below Assignment :
OutputDT = arrayRow.CopyToDatatable
In the Else Part, we can just assign the clone :
OutputDT = DT.Clone
Let us know if you are facing any difficulties.
Thanks for your response
I will try now and let you know
I am getting this error. What changes have to be made
Also when i read the excel file into datatable, there are few columns with name name like invoice currency(repeats 2 to 3 times). I get an error saying it already belongs to the data table. How should i deal with it
My Apologies, There needs to be a Correction in the Code :
Try the Updated Expression Below :
arrayRow = DT.AsEnumerable.Where(Function(x)Now >= CDate(x("Due Date").ToString) andAlso CDate(x("Due Date").ToString)>Now.AddDays(-x)).ToArray
For the Column Name Issue, Take a Look at the below Snippet :
Sure. i will try.
My Apologies again, could you maybe try renaming the x integer variable to another name , since x is already used in the Linq Expression, it is having conflicting data types and hence the error.
Please change the name to a more unique one and Check it out.
Thank you so much for your time
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.