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,