Extract only last month data from excel

Hi All,

Can you please help me to copy only last month data from the excel and paste it in another excel. Can anyone provide a .xaml file . Sample excel attached . test.xlsx (11.2 KB)

1 Like

What do you mean @rpatil

You want to get the last value of your data in excel.?
image

cheers :smiley:
Happy learning :smiley:

You can use the Read Range activity, output your data to a DataTable. Then use Filter DataTable activity to output the rows that has last month’s date.

1 Like

Hi
hope this would help you

Cheers @rpatil

1 Like

Noo i need to get all the data from previous month ex - from nov 1st to nov 30th

yes but how to pass the value for previous month . Here the date is hard coded image

Hi @rpatil,

Try following code to filter last month’s data. But before that,

  1. In your excel file there’re 2 columns with same header AUD, so it’ll throw exception while read range, to handle this you need to change header of one of those columns. eg —> AUD2.
    2.In excel there’s no header for column with dates, so give header to that column, let’s call it Dates.

Now you can use this code in assign,

FilteredDT = DT.Select("[Dates] >= '#"+(New DateTime(Now.AddMonths(-1).Year, Now.AddMonths(-1).Month, 1)).ToString+"#' AND [Dates] <= '#"+((New DateTime(Now.Year, Now.Month, 1)).AddDays(-1)).ToString+"#'").CopyToDataTable

here,

  1. FilteredDT is newly created dataTable var.
  2. DT is output var of read range.

Now you can write range FilteredDT var in other sheet to check it, that’s it. :wink:

Thank you , but i cant add column name as date.Is there any way i can take first row , as date will be in first column always.

Excel is generated from application and we are not supposed to do any changes . But dates will be present in first column always so what value to provide in assign activity in place of “Dates”

Okay @rpatil , then you can use —> ["+DT.Columns(0).ToString+"] instead of [“Dates”]

FilteredDT = DT.Select("["+DT.Columns(0).ToString+"] >= '#"+(New DateTime(Now.AddMonths(-1).Year, Now.AddMonths(-1).Month, 1)).ToString+"#' AND ["+DT.Columns(0).ToString+"] <= '#"+((New DateTime(Now.Year, Now.Month, 1)).AddDays(-1)).ToString+"#'").CopyToDataTable
1 Like

It’s working perfect thank you :grinning:

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