Delete Excel rows with specific value

I cant share the excel sheet.
My requirement is Need to remove those rows from the excel where Status is equal to " Exception Triggered" or Status = “Unhandled Exception Triggered” one more condition is Date column having dates of last 28 days like from3rd jan to 31st Jan need to me removed .
There are total 52 column and right now 27k data the data will increase and few columns have null values too but Status and date column is not null

Hi @Yoichi

The Column value is fixed in the code and as it finds and empty cell it comes out but it should keep checking till end. That is not working

I have tweaked the code a bit and its working to satisfy half of my requirement to delete based on one column value but If I want to put conditions on one more column like date less than current and greater than last 28 days is it possible

Hi,

I haven’t understood your condition completely, however, the following will help you.

Sample20220201-2v4.zip (12.3 KB)

Regards,

1 Like

Thank you so much will check and get back to you


Date in the excel is in this format: 17-11-2021 07:05:05

Hi,

Can you try to modify define line of d variable to the following expression?

System.DateTime d = DateTime.ParseExact(rangeDate.Value2.ToString(),"dd-MM-yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture);

Regards,

same error

Hi,

Can you try to add .Trim() after ToString() as the following? Or is there possibility cell is blank? If so, need to add some code for it.

System.DateTime d = DateTime.ParseExact(rangeDate.Value2.ToString().Trim(),"dd-MM-yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture);

Regards,

1 Like

Still not working and no empty cells

Hi,

Can you try to add the following just before the above expression as the following, for checking its content?

Console.WriteLine(rangeDate.Value2.ToString());

Regards,


Some weird value is coming

Hi,

Can you try the following expression?

System.DateTime d = DateTime.FromOADate(Double.Parse(rangeDate.Value2.ToString()));

Regards,

1 Like

input string not in correct format error

Hi,

Can you share screenshot of Output panel when exception occurs?

Regards,

yes sharing

The date column has value being added by the bot as Datetime.Now.tostring

Hi,

For now can you modify to the following?

						Console.WriteLine(rangeDate.Value2.ToString());
						Double dbl;
						System.DateTime d;
						if (Double.TryParse(s,out dbl)){
							 d = DateTime.FromOADate(Double.Parse(rangeDate.Value2.ToString()));
						}else{
							 d = DateTime.ParseExact(rangeDate.Value2.ToString().Trim(),"dd-MM-yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture);
						}		

Regards,

1 Like

sure will try and get back thanks a lott


I think there is some issue with the if condition