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.
Developer Rants 