Read data from excel date range

Help Activities
1

Hi Guys
I have excel file where date colomn I need to filter data with specific date range like 1/1/2025 to 31/1/2025 and write in excel how to do that ?

2

1.first read excel and convert into DT.
2.Use assign activity

Dtresult=dt.Select(“[Date]>= #”+date1.ToString +“# and [Date]<= #”+date2.ToString +“#” ).CopyToDataTable

  1. Use write range to write results in excel

Mark solution if its help :blush:
Happy Automation

3

@Mahera_Khan

Follow below steps,

  1. Use read range activity, to read your excel file
    2.Use assign activity
    in to section create a datatable variable and in value section provide below linq
    dtData.AsEnumerable(). Where(Function(row) DateTime.Parse(row(“Date”).ToString.Trim) DateTime.Parse(“01/01/2025”) AndAlso DateTime.Parse(row(“Date”).ToString.Trim) DateTime.Parse(“31/01/2025”)).CopyToDataTable()

dtData is you excel data

then after you can write range activity to write the data output data into another excel file

4

@Mahera_Khan,

  1. Open your Excel file with an Excel Application Scope → Use Excel File activity.
  2. If your data isn’t already formatted as a table, use the Format Table activity. This makes it easier to use filtering.
  3. Use the Filter Table activity to apply the date filter directly.
    In the Filter Table’s properties, select your table name.
    Specify the Filter Column where your dates are stored.
    For the filtering condition, choose an operator (or use two filter conditions) to indicate that you want dates greater than or equal to 1/1/2025 and less than or equal to 31/1/2025.
    For example, you might set:
  • Criteria1: >=1/1/2025
  • Criteria2: <=31/1/2025
  1. After the table is filtered, you have a couple of options:
    • You may choose to use the Copy Range activity to copy the visible (filtered) cells and then write them to a new Excel file using Write Range.
    • Alternatively, if you’re performing further operations on the workbook, you can leave the filtered results in place.
  2. Finally, if needed, you can clear the filter using Clear Filter activity once your task is complete.

LLM helped me to rewrite this but it’s a valid approach.

5

@Mahera_Khan

If you want to rewrite in to same sheet then you can use excel application scope and then filter excel inside it and then use delete range and select hidden rows

If you want to write to different excel then you can use copy pste ramge activity and copy to dofferent excel or sheet

Or read the data into datatabel use filter datatable activity to filter and then use write range to write

Cheers

7

Hello @Mahera_Khan Maheera,

You can do it in two ways -

Just using excel (if there are many rows, it is recommended to apply this option)

  1. Filter the data using column
  2. Use read range to get only visible rows and write it another sheet.
    or for same sheets- Delete the hidden rows

And then using data tables - read as datable and filter the data and write it back to excel sheet.

Hope it helps!

8

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