How to convert string array to datetime array?

Sure here is the output
image

Create an Empty List of Datetime Format and for each Item in the String List Use an Assign and convert that to datetime Convert.ToDateTime(item.ToString) and add it to the datetime list

Sorry but what to do mean by create an Empty list of Datetime format. How do I do it ?

Fantastic
hope these steps would help you resolve this
–once after getting the datatable from read range activity use a assign activity like this
datetimearrayvariable = datatablename.AsEnumerable().Select(Function(a) Datetime.ParseExact(a.Field(of string)(“Holidays”).ToString.Substring(0,10),“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).ToArray()
–where datetimearrayvariable is a array of Datetime variable

Cheers @kieranwong

1 Like

Its an Array of type DateTime

I have an error is there something wrong?

1 Like

you were almost done
kindly follow these steps to get this error out

Cheers @kieranwong

Now the script does not even work. Is there any other way?
Thanks for your help! @Palaniyappan

What was the error you were getting
@kieranwong

user timeout

at which activity @kieranwong

The whole RPA script does not even start

can i have that excel file or a sample file if possible
Cheers @kieranwong

Hello Kieran,

Be careful regarding assuming the datatype within Excel. While Excel will present the data in a given way that may make you believe that it is a string, what UiPath actually gets back can be wildly different, especially with Timestamps.

In this case, what you’re seeing could be a Double instead. The reason I believe this is because actual string values are left aligned and the picture of your spreadsheet shows that the Day and Name are but the Holidays column is not.

I would suggest looking at DateTime.FromOADate if my suspicions are correct.

3 Likes

Sorry for the late reply Singapore Public Holidays.xlsx (12.4 KB) . Pls use Sheet2 of the excel workbook

Ok,

  • I added the assesmbly part
  • @dmccammond you might be right.

so, I tried this table_Toarray.xaml (9.9 KB) :
arrStr.Select(Function(d) If(IsDate(d), Convert.ToDateTime(d).ToString("dd/MM/yyyy"), d) ).ToArray ** **
To show the non date like values:

01/01/2019;;05/02/2019;06/02/2019;;19/04/2019;;01/05/2019;;19/05/2019;20/05/2019;05/06/2019;;11/08/2019;12/08/2019;09/08/2019;;27/10/2019;28/10/2019;25/12/2019;;;;;;;;;;;

There are many values that are not in datetime

this is why the error DateTime’ type ‘System.String’. to System.Data.DataRowExtensions.UnboxT`1.ReferenceField(Object value )

I am attaching what I have so far but I think it might had to do with above explanation.


will keep looking into this.

1 Like

Sure thanks for your help. I am also currently looking into it too and trying to find the solution

1 Like

My suspicions were somewhat correct. In case anyone has this issue in the future, here’s how I figured it out.

There are two ways to figure out the data type, one is to simply go into excel and change the formatting to general. As you can see below, it’s a number representation of the date, not a string.
image

The second way to figure it out would be to load the data in using either Read Range activity and then using GetType. While the first option is usually easier to do, it’s not always accurate, read on to see why.

And here’s an example of how to pull out the dates using DateTime.FromOADate like I suggested: Test_ReadDateTimeFromExcel.xaml (8.5 KB)

There was an interesting issue with your input file. Not all of the datatypes are the same with the excel formulas giving Doubles as I expected instead of the DateTimes like all of the other fields. Using a switch statement, I was able to parse them correctly.

Here’s the sample output:
image

3 Likes

You can easily convert any of these formats to a datetime array using the “datetime” function in matplolib or in python.

Don’t forget to mark the issue as solved otherwise it will stay open.

Thank you,