Filter excel rows based on a date range

Help Studio
, ,
22

@MANISHA_V_ARORA - It should be method name convert.todatetime Or (DateTime) whichever works with C#…column index is already added in the code as r(2)

23

image
with convert.datetime gives this error

And with Datetime gives this error
image

@Divyanshu_Divyanshu

24

@Divyanshu_Divyanshu @prasath17 Any update?
This is bit urgent. Thank you in advance.

25

I entered as below in Assign Activity & it didnt work:
Here GuestTierScoreDT is a datatable variable

GuestTierScoreDT = GuestTierScoreDT.AsEnumerable().Where(r => DateTime.ParseExact(r(7).toString().trim(), “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture) >= (DateTime)“1/25/2020” & DateTime.ParseExact(r(7).toString().trim(), “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture) <= (DateTime)“1/25/2021”).CopyToDataTable()

I get syntax error as mentioned in earlier post

@Divyanshu_Divyanshu @prasath17 @NIVED_NAMBIAR

26

@Divyanshu_Divyanshu @prasath17 @NIVED_NAMBIAR

Your help is needed here…

27

@MANISHA_V_ARORA - @Yoichi can help …

@Yoichi - Could you please help @MANISHA_V_ARORA to convert this LINQ query to C#…

dtSample.AsEnumerable().Where(Function ( r ) DateTime.ParseExact( r(2).toString.trim , “M/d/yyyy”, CultureInfo.InvariantCulture) >= cdate(“1/25/2020”) and DateTime.ParseExact( r(2).toString.trim , “M/d/yyyy”, CultureInfo.InvariantCulture) <= cdate(“1/25/2021”)).CopyToDataTable

1 Like
28

Hi @prasath17 @MANISHA_V_ARORA ,

Can you try the following?

dt.AsEnumerable().Where(r => (DateTime.ParseExact(r[2].ToString().Trim(), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture) >= DateTime.Parse("1/25/2020") && DateTime.ParseExact(r[2].ToString().Trim() , "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= DateTime.Parse("1/25/2021"))).CopyToDataTable()

Regards,

1 Like
29

@Yoichi - Thank you so much…Quickly tested and working perfectly (My first c# :wink:)…

@MANISHA_V_ARORA _ please try and let us know…This is with Preserve format…

2 Likes
30

image
image710×44 7.7 KB

I get this error

@Yoichi

31

image
image902×359 60.5 KB

am trying to filter on column G and it is of date type

Entered below in Assign statement:

GuestTierScoreDT= GuestTierScoreDT.AsEnumerable().Where(r => (DateTime.ParseExact(r[7].ToString().Trim(), “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture) >= DateTime.Parse(“1/25/2020”) && DateTime.ParseExact(r[7].ToString().Trim() , “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture) <= DateTime.Parse(“1/25/2021”))).CopyToDataTable()

7th here being column G

@Yoichi @prasath17

32

Hi,

RowIndex number of ColumnG is 6 (because it’s 0-based index)
Can you try the following expression?

GuestTierScoreDT=GuestTierScoreDT.AsEnumerable().Where(r => (DateTime.ParseExact(r[6].ToString().Trim(), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture) >= DateTime.Parse("1/25/2020") && DateTime.ParseExact(r[6].ToString().Trim() , "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= DateTime.Parse("1/25/2021"))).CopyToDataTable()

or

The following might be better.

GuestTierScoreDT=GuestTierScoreDT.AsEnumerable().Where(r => (DateTime.Parse(r[6].ToString().Trim()) >= DateTime.Parse("1/25/2020") && DateTime.Parse(r[6].ToString().Trim()) <= DateTime.Parse("1/25/2021"))).CopyToDataTable()

Note: We can also use row name like r["d_as_of"] instead of r[6].

Regards,

1 Like
33

Thank you so much @Yoichi :slight_smile:

Second one worked for me.

Thank you @prasath17 :slight_smile:

2 Likes
35

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