I've got an excelsheet which is generated by a thirdparty which I need to read data from. One column is formated as percentage in the excelsheet and when I read data from the sheet I get eg. 27%. But If I open excel and click the cell, I can see that the actual value of the cell is 0,27673528. How can I get that value without opening the excelfile and modifying the column type?
I'm using the connectionstring:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Chr(34) & uploadFileName & Chr(34) & ";Extended Properties=" & Chr(34) & "Excel 8.0;HDR=NO;IMEX=1;" & Chr(34)
Well would you know. Ran into excel/Oledb problmes again and thought I google it. And apparently I've already posted a question here.
Though this time I've found some sort of solution.
Since I only have the JET engine to work with this answer is only valid for that, I guess.
Problem: Say I have a cell in excel with underlying value 2017-09-01 14:40:37. Though the cell only displays 2017-09-01 14:40. When I retreive the value with OleDbConnection and JET and IMEX=1 then I only receive 2017-09-01 14:40.
Solution: To get the whole value I have to set IMEX=0.
Caveat: The JET engine makes a Column-Type assumption based on the first rows. I had text in the forst rows so alla dates were ignored on the fetch. I made a workaround by first updating the cells that didn't have a date value to null. And then I fetched all the values with IMEX=0