Excel data delete by compare with todays data

hi

in a excel cloumn 4 i have few dates like mm/dd/yyyy we need to compare it with todays date and delete the data which are only 3 days older

example 04/02/2024 this date should be compared with today date 04/03/2024 this date is under 3 days older so we need to delete
if i have 03/22/2024 is more than 3 days so it should not be deleted.

only compare todays date and 3 days back and delete those items only

@Fab_Bucket

Use for each row in excel activity

Then use if condition inside it with cdate(currentrow("yourcol4name").ToString) > Now.adddays(-3)

On then aide use delete rows activity and give currentrow

Cheers

till if conditon and condition is working we are getting in then loop but Deleting the current row is not working as its thowing error can you share me xaml
Thanks in advance

@Fab_Bucket

Clone your Excel datatable using dt.clone() to lets say dtTest

cdate(currentrow("yourcol4name").ToString) > Now.adddays(-3)

Use this condition by Anil in an if condition inside for each row in excel. In the else section use add data row activity to add datarow to dtTest and then write this datatable back to excel.

image

The reason why delete row will give error

NOTE: If you place this activity inside a For Each Excel Row activity, make sure that the target range of rows is not the same as the target range of the For Each Excel Row activity. This aims to avoid issues where unintended rows in the iteration would get deleted.

Hey @Fab_Bucket ,

Use this Linq in a assign activity,

With including 3rd day:

dt_Input = dt_Input.AsEnumerable().Where(Function(x) CDate(x(4).ToString)<=Now.AddDays(-3)).CopyToDataTable

                                    OR

For Without including 3rd day:

dt_Input = dt_Input.AsEnumerable().Where(Function(x) CDate(x(4).ToString)<Now.AddDays(-3)).CopyToDataTable

Image for your reference:

Cheers!
Ajay Mishra

@Fab_Bucket

When you say not working what issue are you getting…can you shwo the flow?

Alternately you can also use filter excel activity and delete rows at once instead of loop

If you can provide a sample excel we can check

Cheers

Wrong Post @mkankatala

1 Like

can you please make a xaml and share me or i cant catch you or screen shot me flow
thanks in advance

Hey @Fab_Bucket

Use below mentioned LinQ:

1 Like

Just put Column index or name in below linQ:

Image:

dt_Input.AsEnumerable().Where(Function(x) CDate(x(“ColumnName”))<=Now.AddDays(-3)).CopyToDataTable

dt_Input.AsEnumerable().Where(Function(x) CDate(x(Index))<=Now.AddDays(-3)).CopyToDataTable

Note: Retype Inverted Commas “”

Regards,
Ajay Mishra

1 Like

we are entering in then part we have data to delete but after executing remove data row after that we are getting error
For Each Row in Data Table: Collection was modified; enumeration operation might not execute.

can you please clear this i have used filter data table its not working in my studio

can you help me with this process

Try this, This will work for you!

1 Like

Hey @Fab_Bucket

I’m attaching .xaml for your reference!
DateFilter_Lessthan3days.xaml (7.7 KB)

Put your column name in that assign LinQ.

Regards,
Ajay Mishra

1 Like

@Fab_Bucket

Can you send a sample excel please…ill check with filter

For eqch is not working because in the same loops its not deleting

Either we need to save the row number to array and then delete after the loop or use filter

Cheers

can you share me xaml in detail i cant understand

@Fab_Bucket Just put the column name in quotes or column index in place of 3 “x(3)”

1 Like

@Fab_Bucket
Please find the screenshot of the process. I cannot share xaml due to some restrictions.
image

image

image

image

image

You could also use linq provided by ajay