Wierd Excel Behaviour

The following is the explanation of a very odd excel behaviour: 

http://support.microsoft.com/kb/194124/EN-US/

The problem comes when using sql server stored procedures to read data in from an Excel spreadsheet.

The symptom is that Excel will guess the datatype of the columns. If it guesses wrong you get null instead of the data.

I have a general rule of thumb about Excel: if you let it guess it will almost always guess wrong.

Why could microsoft not have added extra connection parameters that allow the caller to specify the data types?

This would not break existing code and would save a lot of heartache.

I have found a workaround:

Export the sheet to tsv and then reimport.

This looses all of the formatting that is getting in the way. 

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s