OleDB & mixed Excel datatypes : missing data

Several forums I found claim that by adding IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text to the Extended Properties in the connection string would fix the problem, but this was not the case. I finally solved this problem by adding "HDR=NO" to the Extended Properties in the connection string (as Brian Wells shows above) so that I could import mixed types.

I then added some generic code to name the columns after the first row of data, then remove the first row.

    public static DataTable ImportMyDataTableFromExcel(string filePath)
    {
        DataTable dt = new DataTable();

        string fullPath = Path.GetFullPath(filePath);

        string connString =
           "Provider=Microsoft.Jet.OLEDB.4.0;" +
           "Data Source=\"" + fullPath + "\";" +
           "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";

        string sql = @"SELECT * FROM [sheet1$]";

        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, connString))
        {
            dataAdapter.Fill(dt);
        }

        dt = BuildHeadersFromFirstRowThenRemoveFirstRow(dt);

        return dt;
    }

    private static DataTable BuildHeadersFromFirstRowThenRemoveFirstRow(DataTable dt)
    {
        DataRow firstRow = dt.Rows[0];

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if(!string.IsNullOrWhiteSpace(firstRow[i].ToString())) // handle empty cell
              dt.Columns[i].ColumnName = firstRow[i].ToString().Trim();
        }

        dt.Rows.RemoveAt(0);

        return dt;
    }

Using .Net 4.0 and reading Excel files, I had a similar issue with OleDbDataAdapter - i.e. reading in a mixed data type on a "PartID" column in MS Excel, where a PartID value can be numeric (e.g. 561) or text (e.g. HL4354), even though the excel column was formatted as "Text".

From what I can tell, ADO.NET chooses the data type based on the majority of the values in the column (with a tie going to numeric data type). i.e. if most of the PartID's in the sample set are numeric, ADO.NET will declare the column to be numeric. Therefore ADO.Net will attempt to cast each cell to a number, which will fail for the "text" PartID values and not import those "text" PartID's.

My solution was to set the OleDbConnection connectionstring to use Extended Properties=IMEX=1;HDR=NO to indicate this is an Import and that the table(s) will not include headers. The excel file has a header row, so in this case tell ado.net not to use it. Then later in the code, remove that header row from the dataset and voilà you have mixed data type for that column.

string sql = "SELECT F1, F2, F3, F4, F5 FROM [sheet1$] WHERE F1 IS NOT NULL";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PrmPathExcelFile + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();
ds.Tables.Add("xlsImport", "Excel");
da.Fill(ds, "xlsImport");

// Remove the first row (header row)
DataRow rowDel = ds.Tables["xlsImport"].Rows[0];
ds.Tables["xlsImport"].Rows.Remove(rowDel);

ds.Tables["xlsImport"].Columns[0].ColumnName = "LocationID";
ds.Tables["xlsImport"].Columns[1].ColumnName = "PartID";
ds.Tables["xlsImport"].Columns[2].ColumnName = "Qty";
ds.Tables["xlsImport"].Columns[3].ColumnName = "UserNotes";
ds.Tables["xlsImport"].Columns[4].ColumnName = "UserID";

connection.Close(); 

// now you can use LINQ to search the fields

    var data = ds.Tables["xlsImport"].AsEnumerable();
    var query = data.Where(x => x.Field<string>("LocationID") == "COOKCOUNTY").Select(x =>
                new Contact
                {
                    LocationID= x.Field<string>("LocationID"),
                    PartID = x.Field<string>("PartID"),
                    Quantity = x.Field<string>("Qty"),
                    Notes = x.Field<string>("UserNotes"),
                    UserID = x.Field<string>("UserID")
                });