Excel ODBC Driver Woes

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.


http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819

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:

100-234
101-543
233-009
1233221
654-322

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.

Advertisements
This entry was posted in Programming. Bookmark the permalink.

7 Responses to Excel ODBC Driver Woes

  1. straylight says:

    WOW, considering i just stumbed across your blog, Im pretty appreciative. You saved my ass here, (as well as a lot of time).

    Ridiculous, thats one of the worst ‘limitations’ I’ve seen. Outlook has some pretty lame ones too, i feel your pain though. Especially you are trying to incorporate this as more than a one time procedure….

    -stray

  2. Matschek says:

    Glad to see that I’m not the only one who is appalled at this implementation of this “feature”…
    Any 3rd party solution available?

    Matschek

  3. Sam says:

    Just came across your post in a google search. I had numeric data in a column where some of the values began with zero. Some of these were showing up as null and after reading your post and looking at the KB article, I realized the ODBC driver thought that these values were dates!

    My solution/workaround was to create a new column and use the concatenate function to add the letter “X” to every value. When I query the new column through ODBC all records were returned because they were all text values. When using the query recordset I just had to strip off the letter “X” from each value.

    Thanks for the post.

  4. Bajrang Gupa says:

    Hi Sam,
    Could you please help me with the work around that you have done?

    have you been able to use the updating using the SQL Query or used the excel concatenation function?

    I am looking for the SQL query in case it is possible. UPDATE [Sheet1$] set COLUMN1=CONCAT(COLUMN1,’ ‘); gives me a nasty Invalid use of vertical bars error.

    The excel concatenation function i cannot use until, it can be incorporated using a Java function.
    Any help would be appreciated.

    Regards,
    Bajrang Gupta
    bg.winlin@gmail.com

  5. sweetha says:

    I tried by setting IMEX=1 in extended propreties but it didn’t work for me.Can you please help me to fix this bug.

  6. Rob says:

    Thanks, you saved me a bunch of time!

  7. Andrew says:

    It’s funny looking back through the dates of posts with people having the same issue. You’d think that after this many years, a solution could have been implemented. As it stands, there is data in thousands of Excel sheets that I was attempting to query and return some pertinent values, but it doesn’t look like that will be the case… Way to go, Microsoft.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s