Sure here is the output
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
Its an Array of type DateTime
user timeout
The whole RPA script does not even start
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.
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.
Sure thanks for your help. I am also currently looking into it too and trying to find the solution
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.
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:
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,