Need to filter out date column in excel file

Help Studio
, ,
1

Hi team,

I am unable to upload excel file here so i giving you data here itself

SP_Date

10/21/2023
10/22/2023
10/10/2023
11/9/2023
12/8/2023
6/7/2023

In the input SP_Date column contains “MM/dd/yyyy” bot has to filter date less than 7 days data compare to todays date and remove those dates in this range.

The output should be last 4 rows which are greater than 7 days and bot should delete first 2 rows
This is my concern please help me

Thanks In Advance.
@fab_bucket.

2

Hi @Fab_Bucket

Try this:

Assign activity:
currentDate= DateTime.Today      
        
Assign activity:
OutputDataTable = (From row In dtData.AsEnumerable()
                Let spDate = DateTime.ParseExact(row.Field(Of String)("SP_Date"), "M/d/yyyy", Nothing)
                Let dateDifference = (currentDate - spDate).Days
                Where dateDifference <= -7 Or dateDifference >= 7
                Select row).CopyToDataTable()

Note: DataType of currentDate is System.DateTime and DataType of OutputDataTable is System.Data.DataTable.

image
image1003×722 32.6 KB

Hope it helps!!

3

@Fab_Bucket
Read Range -dtInput

Assign
currentDate = DateTime.Now
dateThreshold = currentDate.AddDays(-7)

Use LINQ Query:

From row In dtInput.AsEnumerable()
Let dateValue = DateTime.ParseExact(row.Field(Of String)("SP_Date"), "M/d/yyyy", CultureInfo.InvariantCulture)
Where dateValue >= dateThreshold
Select row

Cheers…!

4

Hi @Fab_Bucket

Check out below workflow for better understanding. I have use Build Data Table , instead of that you can use Read Range Workbook and store it in an datatable and follow the below process.

Sequence2.xaml (10.3 KB)

Hope it helps!!

5

Hi
Thanks for code.

I am facing issue at

image
image943×542 30.7 KB

Thanks in Advance
@fabbucket

6

Hi @Fab_Bucket

Check out the workflow file that I have provided make changes according to that. Or else send the workflow file I will make the required changes.

Regards,

7

Hi

For build data table code is working but i want to read from excel sheet
To read data from excel sheet we are using Read range workbookactivity I am getting error as.

image
image931×537 27.1 KB

can you please keep Read range workbook activity behalf of Build data table.
I am unable to upload my Xaml can you please make changes and update me

Thanks in Advance
@fab_bucket

8

Hi @Fab_Bucket

Check out this workflow file.
Sequence2.xaml (8.4 KB)

Input.xlsx (8.6 KB)
Sheet1 is your Input and Sheet2 is your Output. According toyour code please do check the Preserve Format Option in the properties of Read Range Workbook. It will work fine.

Hope it helps!!

9

Hi ,

Can i get this same result filter by using filter data table activity?

In the input SP_Date column contains “MM/dd/yyyy” bot has to filter date less than 7 days data compare to todays date and remove those dates in this range.

The output should be last 4 rows which are greater than 7 days and bot should delete first 2 rows

image
image855×295 5.05 KB

Thanks in Advance
@fab_bucket

10

If it’s less than 7 days, it should be returning rows 4, 5 only right?

11

Hi,

filter data table should remove last 7 days data compare to todays date.

Below dates we have to remove because this are less than 7 days compare to todays date.

we are having MM/dd/yyyy format

10/21/2023
10/22/2023

I need to get this output in excel.
This are greater than 7 days compare to todays date

10/10/2023
11/09/2023
12/08/2023
06/07/2023

Thanks
@fabbucket

12

Try this:

image