Sign in to follow this  
JonW

[.net] OleDbDataReader and Mixed-Date Fields

Recommended Posts

Hi, I'm using an OleDbDataReader to read an Excel spreadsheet that contains data extracted from a piece of hardware. The spreadsheet has a Date column that contains the date of log entries. For some reason, the date format of this field tends to vary between a European-ish format (ex 17-07-2006) and a US format (ex 7/17/2006). Worse, the date format sometimes even varies within a single spreadsheet; for example the first half of the rows will be in European format and the second half will be in US format. Wild. When the entire spreadsheet has its date format as 7/17/2006 (M/d/yyyy), I can read the values from the field using: DateTime dtDate = reader.GetDateTime(1); When the entire spreadsheet is in the 17-7-2006 form (d-MM-yyyy), I can read it as a string with: string strDate = reader.GetString(1); However, when the spreadsheet has some rows in one format and some rows in the other, I can't seem to get the data out in any form. When I call reader.GetFieldType(1) it tells me that its a string, but the field value for the US date formatted rows comes back as DBNull. I tried writing my query as: SELECT format([Date], 'Short Date') FROM Sheet1$ This converts the European-format dates to US format, but the rows that are already in US format come back as empty strings. It just doesn't make any sense. Is there anyway I can read the dates? I don't care if the reader gives me a string of one's and zero's, I just want to get the stupid values without it spitting out empty strings or DBNulls. Thanks...

Share this post


Link to post
Share on other sites
I found out that the reason for the error is that ADO.NET is retarded.

Quote:

The main quirk about the ADO.NET interface is how datatypes are handled ... ADO.NET scans the first 8 rows of data, and based on that guesses the datatype for each column. Then it attempts to coerce all data from that column to that datatype, returning NULL whenever the coercion fails!


http://blog.lab49.com/?p=196 explained the cause of my problem perfectly. The rows in one date format at the beginning gets read, and the rows in the other date format get ignored. Worst implementation of anything ever.

Anyway, I hope this saves someone else from going through this trouble.

Share this post


Link to post
Share on other sites
Correction -- Microsoft suggests that this problem is due to the Excel ISAM driver: http://support.microsoft.com/kb/194124

Whichever, it's still inane.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this