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

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

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

Regards,
Arivu

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,

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

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

Regards,
Arivu

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,

@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

Hi @Jumbo,

Try this code

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

Regards,
Arivu

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…

Regards,
J,

@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

@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

Hi @MAHESH1,

I tried below code but does not work with error.


Error
image

My Date time format in data table.
image

What is problem of this??

Best regards,
J,

@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

Hi @MAHESH1,

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

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,

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

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,

Converting to date time value

@Jumbo

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

Regards,
Mahesh

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,

@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

Hi @MAHESH1,

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

Rgds,
J,