How to get the excel data from -7 days to -3 years from .xls file

Help Studio
, ,
1

Hi, I am trying to extract the data from -7 days to -3 years range and I tried with the filter data table but unfortunately not getting the result .
test_duedate.xls (537 KB)

2

Hi @Syed_Sultan_Ahmed1

Can you specify for which column you need to filter the range.

Regards,

3

I need to filter due date column

4

i tried with the Filter DT

image
image1035×535 36.2 KB

5

@Syed_Sultan_Ahmed1

ExcelDt=ExcelDt.AsEnumerable.where(Function(x) cdate(x(“Due date””).ToString)>CDate(now.AddYear(-3).ToString(“dd-MMM-yyyy”)) And cdate(x(“Due date”).ToString)<CDate(now.AddDays(-7).ToString(“dd-MMM-yyyy”))).CopyToDataTable

try this hope this helps

6

Hi,

What activity do you use to read it to datatable?
If you use ReadRangeWorkbook, it returns datetime as OADate value.
So can you try UseExcelFile activity and ReadRangeX activity (or Classic ReadRange if ReadRangeX is very slow), then use Filter DataTable with the above condition?

Regards,

7

Hi , I am getting the error with this expression “ADDYear is not an member of Date”

8

Hi @Syed_Sultan_Ahmed1 ,

Could you maybe check with the below Steps :

  1. Read the Excel sheet as a Datatable using Read Range Workbook activity, Keep the PreserveFormat property enabled.
  2. Next, For the Filtration we could use the below Linq Expression :
drArray = DT.AsEnumerable.Where(Function(x)CDate(x("Due date").ToString)<=Now.AddDays(-7) andAlso CDate(x("Due date").ToString)>=Now.AddYears(-3)).ToArray

Here, drArray is a variable of Type Array of DataRow.

  1. Next, we can check if there are any Filtered Rows present and then convert to Datatable, else We can assign Clone of the Datatable.

Implementation :

image
image706×498 23.1 KB

Findings when read as PreserveFormat, the dates are in MM/dd/yyyy format, so the CDate() was used directly :
image

9

What is the datatype of drArray,

10

@Syed_Sultan_Ahmed1 ,

It is mentioned.

image

11

getting this error

image
image1273×558 41.2 KB

12

tried that but its not working

13

@Syed_Sultan_Ahmed1 ,

Was the same input file checked or a Different file ? Also, Let us know what activities you have used or the Implementation done so far.

14

Please check if I am doing something wrong
testDue-7to-3years.zip (120.7 KB)

15

@Syed_Sultan_Ahmed1 ,

I did check your workflow, It seems that you are using Excel Application Scope, with Excel application Scope it seems that you are able to get the date values in the required format, Hence No need to Enable PreserveFormat Property.

Could you disable PreserveFormat property and check whether it is able to get the desired results ?

16

Thank you its working fine and getting the expected result…

17

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