I have to delete last month data with Date in "Date_Closed" column

Help Studio
, ,
1

Hi Team,

I have to delete last month data with Date in “Date_Closed” column

Input:

image
image200×173 1.91 KB

Note: I have to delete last month rows and update in same sheet (without take any other sheet to write output data)

anyone please

Thanks
Shaik

2

Hi,

  1. Read Data - Use the Excel Application Scope activity
  2. Inside the scope, use the Read Range activity to read the data into a DataTable (e.g., dtData).
  3. Filter Rows:
    Add an Assign activity to calculate the first and last dates of the previous month
FirstDateLastMonth = New DateTime(Now.Year, Now.Month, 1).AddMonths(-1)
LastDateLastMonth = FirstDateLastMonth.AddMonths(1).AddDays(-1)
  1. Use Another Assign activity
dtData = dtData.AsEnumerable().
         Where(Function(row) DateTime.ParseExact(row("Date_Closed").ToString(), "dd-MMM-yyyy", System.Globalization.CultureInfo.InvariantCulture) < FirstDateLastMonth OrElse DateTime.ParseExact(row("Date_Closed").ToString(), "dd-MMM-yyyy", System.Globalization.CultureInfo.InvariantCulture) > LastDateLastMonth).
         CopyToDataTable()
  1. Write the data back in the same Sheet.

Regards,
Gokul

3

@shaik.muktharvalli1,

If Excel is installed on the machine follow this approach.

  1. Use Excel Process Scope activity
  2. Use `Use Excel File activity
  3. Use Filter to filter the data of last month.
  4. Use Delete Rows activity - Configure property What to delete = All visible rows
  5. Use Filter activity and configure Clear any existing filter to clear the filter
  6. Save the file if not enabled Auto Save.
4

Hi @Gokul001

last month Not November data, it’s means old month data from today date

Please help

5

@ashokkarale I have followed same steps not deleted any rows from sheet

please help

6

@shaik.muktharvalli1,

Any error or which steps are running fine?

7

It’s my mistake, now your steps are working fine

Thanks
Shaik

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.