Many developers/programmers while writing code to extract data from an excel Intermixed data columns struggle to read the data. Reading data from an excel file is always been a challenge as the first row (record) of the data in excel defines the data type of the column. For example if the first row has the data as Integer and any consecutive row has the data as Float, excel read operation would fail as it expects the rest of the data in that column to be of Integer type. When a column has data of different data type is called as Intermixed Data Columns.
Now the question is that how to read excel data from a column having Intermixed Data Types; the answer is that we read all the data as TEXT type. You can build connection for an excel file using the below code:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyExcel.xls; Extended Properties="Excel 8.0;HDR=Yes; IMEX=1";
The Extended Property IMEX set to 1 tells the driver to always read Intermixed Data Columns as TEXT. Also note that HDR set to YES means that the first row in the excel file is the column header.