Found some weird thing in Excel 2003 (i’m not sure about 2007 or 2010) when you have mixed data type in a sheet as like below and if you try to read the cell values programmatically by using any excel specific data provider then the date value at “A11″ will become a Julian date value (numeric) and decimal value at “B11″ become a date!
Tried to get some help from Google but no luck. Customer was running behind me to resolve this issue but the mystery continues for a day. At last, after spending 3 to 4 hours of research, found a peculiar thing and that is
“If there is a mixed data type values in an excel sheet column then the data type for cell value starts from 11th row onward will be determined based on the data type of last available value up to the 10th row”…hmm this is my inference and kudos to me
As given in the above sample data (I’m sorry, I don’t know how to explain and so I will use the above sample to make it clear) the last value with in the range of 10th row is numeric (A4=210.3) and therefore the date value at A11 will be converted as numeric value. I don’t know how this conversion is happened and I’m assuming that it could be the excel design (some one can help to find out).
If you take the second column, the last available value with in the range of 10th row is date (B7=9/10/2009) and therefore the value at B11 will be converted as date as supposed to be a numeric value.
I changed my logic little bit by considering this scenario and after that it was working without any issue.
Thought of sharing this to anyone come across the same issue during custom ETL development…Happy architecting and coding.
No comments:
Post a Comment