How to select data table with date format when I read range to Excel?

Help
1

Hi all,

Does anybody know how to simple way how to extract data table items from the table that was read from Excel file?

In my case, there is 3 column in a Excel, ID, Name and DateTime, for example. And when I read it with Read-range activity, all data will be read as String format and I cannot use Datatable.select activity with date time fo-rmat. (Like Datetime < now() - 1 day.)

Indeed, it is able to separate data if I use for each row and convert string to Datetime format but I think it might be poor performance…

So, if anybody know good way to select data table with date time format, please kindly let me know.

Regards,
J,

1 Like
2

Hi @Jumbo,
Use this way to convert string to date time.
DateTime.ParseExact(str_PrevDate, "MM/dd/yyyy", CultureInfo.InvariantCulture)

Regards,
Arivu

3

HI @arivu96,

Thanks for your reply but it’s converting string to date time.
I hope to read Excel column as Date time format instead of String if possible.

Rgds,
J,

4

If it’s in date time format you can directly apply in the condition.

Dt.Select("DateFieldColumn=DateTime.Now")

Regards,
Arivu

5

Hi @arivu96,

You right but when I read data from Excel with read range, it default string. And I’m looking for the way to read as DateTime.

Rgds,
J,

6

@Jumbo

Try this
dt.Select(“DateTime='”+DateTime.Now.ToString(“yyyy/M/d H:mm”)+“'”)

There mention the format of date which is present in excel for filtering.

Regards,
Mahesh

7

Hi @Jumbo,

Try this code

dt.Select("Convert(DateTime,System.DateTime)>DateTime.Now.AddDays(-1)")

Regards,
Arivu

8

Hi @arivu96, @MAHESH1,

I tried both of your solution but it does not work… I attached my sample data file so if possible could you provide me sample code for this?

Book1 .xlsx (9.6 KB)

Syntax error will be occurred when assign it…

image
image1180Ă—621 60.4 KB

Regards,
J,

9

@Jumbo
whart error you are getting

and in you data, which rows you have to extract
do you want to extract only today’s date

Regards,
Mahesh

10

@Jumbo

Try this
@Jumbo

Try this
dt.Select(“DateTime=’”+DateTime.Now.ToString(“yyyy/M/d H:mm”)+“’”)

There mention the format of date which is present in excel for filtering.

Regards,
Mahesh

11

Hi @MAHESH1,

I tried below code but does not work with error.

image
image1134Ă—341 38.5 KB

Error
image

My Date time format in data table.
image

What is problem of this??

Best regards,
J,

12

@Jumbo
Try this
(From p in dtbsheet1.Select()
where Date.Now.ToString(“MM/dd/yyyy HH:mm:ss”).Equals(Convert.Tostring(p.Item(“DateTime”))
Select p).ToArray.CopyToDataTable

Regards,
Mahesh

13

Hi @MAHESH1,

Thanks for your reply but it does not work with compile error…

image
image1280Ă—156 65.9 KB

I think it is lack of “)” and something other part of formula, because “.ToArray” is List class method.

Could you check it again?
Rgds,
J,

14

HI @Jumbo,
Try this code
(From p In dtbsheet1.Select() Where Convert.ToDateTime((p.Item("DateTime")))<Date.Now Select p).ToArray.CopyToDataTable

Regards,
Arivu

1 Like
15

Hi @arivu96,

Great!! it works. Thanks for your many help :grinning:
This code: Convert.ToDateTime((p.Item(“DateTime”))) is what I intended to!!

Best regards,
J,

16

Converting to date time value

17

@Jumbo

Sry I missed, replace Date.Now with DateTime.Now

Regards,
Mahesh

18

Hi @MAHESH1,

I have already tried that but does not work, I think one or more typo exist but I couldn’t found it…

Rgds,
J,

19

@Jumbo

I tried its working for me

(From p In dta.Select()
Where DateTime.Now.ToString(“MM/dd/yyyy HH:mm:ss”).Equals(Convert.Tostring(p.Item(“DateTime”)))
Select p).ToArray.CopyToDataTable

Regards,
Mahesh

2 Likes
20

Hi @MAHESH1,

Thanks for your code and now it’s work. (might be lack of “)” in previous post.)

Rgds,
J,