Unhandled Exceptions

This is one of the more interesting “features” I’ve found lately, and it took me a while to figure out, so I figure I’d better post it here.

Excel provides an ODBC driver through the Jet driver, which basically means you can access Excel spreadsheets just like you access database tables, provided they are properly formatted (one header row containing field names, all other rows contain values). I recently wrote a small applet to import glossary definitions from an xls file into the database of our content system. It simply read from the Excel sheet and inserted them into the database, with some checking and manipulation, but not much.

The problem was some of the definitions were being truncated to 255 characters. Now, the definition’s field type in the destination database was text, so it wasn’t a storage issue. Through some google “research” I found that the Jet driver used to provide ODBC access to Excel sheets scans, by default, the first 8 lines of the sheet and determines datatypes from there. Since the first 8 definitions weren’t very long, it must have assigned them a varchar datatype with a length of 255, so everything after that was truncated on fields containing more than 255 characters.

The thing that sucks is there is no way to programmatically tell the driver to scan more lines (unless you actually modify the users registry via code). MS is aware of this “quirk” (MS Support Article 1 and Article 2), but there solution is to change the registry value.

That sucks… I mean, I’m the only one who’ll use the app I wrote, but how can you expect users to modify the registry to get values to import properly. That’s ridiculous.

My solution, by the way, was to simply copy one of the records with a long definition to the top of the sheet. Works like a charm.