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

excel

#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,


#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…

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.


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…

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


#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


#20

Hi @MAHESH1,

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

Rgds,
J,