can you please let me know how to delete a specific row based on certain condition.
I have gone thought some of the suggestions but not working out.
I have excel file like below.
I have to delete a row when the status = ‘Closed’ and the Closed_date >=30 (with current date) like below.
currently I am reading into a data table then for each row check for closed rows then closed date is more than 30 days or not, after that passing rowindex to Remove data row activity but getting error from Remove data row activity. Error is " [Collection was modified; enumeration operation might not execute]
I agree with you when I have one more extra column like number of days but in this case I have to calculate the number of days based on closed date and current date at run time then delete if more than 30 days.
(From p In dt.Select Where
p("Status").ToString="Closed" And Date.ParseExact(p("Closed_Date").ToString,"dd/MM/yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture)<now.AddDays(-30)
Select p).CopyToDataTable
where dt is your datatable.
Assign that to your dt, and see if it works.
If that the case try to read the excel and pass it to data table and after that filter the data table and write it again to excel @Neerugattu I hope it makes sense
it seems something is wrong in the input date, because when I tried with this “select” code I am getting same error “String was not recognized as a valid DateTime.”.
In another workflow I only writing this closed_date in dd/MMM/yyyy format but wondering why it is not working.
Any guess please.
When reading a date value from excel dd/MM/yyyy ex 17/05/2019 it read as 05/17/2019 depending from the PC time format.This will happen even though you have format the excel to Date format.
It consist some variables:
startDate - It’s current time
testDate - It’s date written manually (random)
seconds - It’s equation where I’m substracting testDate from startDate and changing to a number of seconds
secondsInDay - simple equation to count how many seconds have one day
final - I’m dividing seconds by secondsInDay to check how many days is it (this will tell you how many days of difference is between current date and date provided manualy)
As you see the final calculation is “10 days”
How could you use it in your case?
You need to have column where all dates will be in same format (at the moment there is garbage there as some cells have dates written manually like 14/Feb/2020). It’s better to have it as numbers (14/02/2020). Then you can grab each cell and using regex take separately Day, Month and Year and push it to testDate variable from my project. It will calculate you how many days is it from now and you have already one condition to use together with “closed” status to remove row Hope this will help
Hope this will help
Anil_G
Yoichi
ashokkarale
mkankatala
sharazkm32
afna
rkelchuri
naveen.s
dutta.marina
Jon_Smith