Problem Description

When reading values from an Excel 2003 spreadsheet within a .NET application utilizing the Microsoft.Jet.OLEDB.4.0 provider, if a particular column of data contains rows with mixed types of data (text and numbers), excel randomly picks one of the data types and ignores anything else that is of a different data type. You cannot fix it by simply re-formatting the cells in excel to all be text. Once you’ve entered a number into a cell in excel, just reformatting it to text does not work!

Column H, PostalCode, in the spreadsheet below is an example of mixed data types. You can see the first value in the list is formatted as text by the little green triangle in the upper left corner. That’s because if this were formatted as a number, the 0 would be left off. When trying to read these values into an application, the data type being chosen was numeric and therefore the first value was not being read in. If we simple format this entire column as text and try importing it again, this does not resolve the problem.

After experimentation, it was determined that excel is choosing the data type represented by the majority of rows.  In the above spreadsheet that would be a numeric data type.  If the spreadsheet had more rows formatted as text, then it would choose the text data type and ignore anything not formatted as text. 

Solution

You have to modify the column formatting in a different way. 

• Select the column of data
• Select Data->Text To Columns
• Select Next (default to delimited)
• Select Next (default to Tab)
• Select Text radio button for column data format
• Click Finish