Excel data delete by compare with todays data

Help Studio
, ,
1

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

2

@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

3

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

4

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

5

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:

Date_Filter
Date_Filter819Ă—153 10.1 KB

Cheers!
Ajay Mishra

7

@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

8

Wrong Post @mkankatala

1 Like
9

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

10

Hey @Fab_Bucket

Use below mentioned LinQ:

1 Like
11

Just put Column index or name in below linQ:

Image:

Date_Filter
Date_Filter819Ă—153 10.1 KB

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
12

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

image
image1893Ă—863 180 KB

13

Try this, This will work for you!

1 Like
14

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
15

@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

16

can you share me xaml in detail i cant understand

17

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

1 Like
18

@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

19

here my excel data is getting in general format string that is why filter is not working may be

my column data is getting like " 05/10/2024 "
spaces before and after data the filter and deliting last 3 days is not happening

20

@Fab_Bucket Okay So,

If your date format is MM/dd/yyyy then below query will give the expected output.

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

                                         OR

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

Just put .ToString.Trim this will resolve your issue.

If not, then debug the project and go to immediate panel and see the format of that value and send me the screenshot.

Regards,
Ajay Mishra