Deleting a row from excel on specific condition

Hi All,

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.

image

I have to delete a row when the status = ‘Closed’ and the Closed_date >=30 (with current date) like below.

image

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]

Could you please help on this.

Thanks in Advance,
Malli

2 Likes

Please check
https://docs.uipath.com/activities/docs/filter-data-table

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.

You can try something like

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

@Neerugattu
Try this
BlankProcess.zip (17.8 KB)

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

cheers :smiley:

Happy learning :smiley:

1 Like

Hi,
Thank you for your reply.
I have tried your solution but getting error at assign activity(NewDate) the error is
Source: Assign

Message: String was not recognized as a valid DateTime.

Exception Type: System.FormatException

I tried to change the input date format(dd/MMM/yy,MMM/dd/yyyy, MM/dd/yyyy ) in the expression but same error.

(DateTime.ParseExact(row(6).ToString,“dd/MMM/yyyy”,System.Globalization.CultureInfo.InvariantCulture))

Did you pass it like this ? @Neerugattu

Yes, I have passed as it is.
image

Even I have excited your code with out any error, the only difference is that you have hard coded the dates in DT but I am reading from excel file.

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 you try to output a cell containing a date from your excel using write line how does it look like?

Please find the below screen shot of the code.

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.

Ya…I agree because I have faced this issue that is the reason I have made it dd/MMM/yyyy while writing and reading also.

Any solution for this.

HI @Neerugattu

Were you able to fix your issue or are you still facing any issues? If so, can you let know what it is so that we can help

Hi @Lahiru.Fernando,
Not able to fix, I have explained the errors which I am facing with different suggestions.

Hi @Neerugattu,
I’ve attached simple workflow which might help you → deleterow.zip (17.6 KB)

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 :wink: Hope this will help