Recently, I’ve been working on a project that involves reading a batch of data using an ASP script. I wanted the bulk of the code to be able to operate on a standard MSSQL database OR read the same data from a static Excel file. On the surface, the Microsoft Excel ODBC driver seems like a natural choice. However, as you will see the best advice is to stay far, far away from this driver.
I started out attempting to use the CSV ODBC driver, but that seems to have a row limitation that is much smaller than the number of records I needed. So, I thought the Excel driver would be more powerful.. and it is, but it has some major problems.. one in particular.
After struggling with this for a couple hours, I finally came across a posting on Google Groups, which eventually lead me to this MS Knowledge Base article.
In the article, it states:
“A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type.”
If you ask me, this is a HUGE crippling bug, not a “limitation.” Basically, what it comes down to is this. If you have a column where most of the cells are strings, but you have an occasional number mixed in your numbers are replaced with nulls. Take for example, a part number list:
See that fourth entry? No dash, therefore it is not a string. So, the Excel ODBC driver conveniently replaces it with a NULL when it’s requested. Sure the article explains a few ways to get around this, but they are all pretty ridiculous. The simplest seems to be:
“To work around this problem for read-only data, enable Import Mode by using the setting “IMEX=1″ in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode.”
But I would never want to bank on that registry setting to be what I expect. Anyway, the Excel ODBC driver may be ok for a one time import where you’ve carefully massaged the data to ensure proper type recognition, but I would NEVER use it in any kind of production product. Ok, end rant. Thanks for listening.