Extract only last month data from excel

[rpatil (Raj)]

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)

[pattyricarte (Patty Boy)]

What do you mean @rpatil

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

cheers
Happy learning

[jcvsalinas (Julius Cris Salinas)]

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.

[Palaniyappan (Forum Leader)]

Hi
hope this would help you

Filter Datatable contains datetime

Hi, I have an excel sheet where Robot Start Date column is in General format. Its supposed to contains values in datetime format MM/dd/yyyy HH:mm:ss I would like to filter the datatable to create a second datatable for rows which have the same date MM/dd/yyyy I’ve tried Dt.Select(“[Robot Start Date] LIKE ‘%MM/dd/yyyy%’”).copytodatatable and it says cannot perform operand LIKE on system.string and system.datetime I cannot use filter datatable activity because it is not a table in the Excel sh…

Cheers @rpatil

[rpatil (Raj)]

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

[rpatil (Raj)]

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

[samir (Samir Mohite)]

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.

[rpatil (Raj)]

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.

[rpatil (Raj)]

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”

[samir (Samir Mohite)]

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

[rpatil (Raj)]

It’s working perfect thank you