Reading Excel by OLEDB reads strings as DBNull

Question

I am reading Excel file using OLEDB in Csharp i have shown the sample excel data what i have

F1    F2    F3    F4
India 23    44    4
China 4     8     Month 6
USA   45    Neg   4

When i read this data and check in my DataTable i get Null values for "Month 6" and "Neg" where as i can be able get the F1 column correctly... my connection string is as shown

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[XLSource];Extended Properties=Excel 12.0;

OleDbDataReader dr;
OleDbConnection conExcel = new OleDbConnection();
conExcel.ConnectionString = ConnectionString
conExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand();
cmdExcel.Connection = conExcel;
cmdExcel.CommandText = "SELECT * FROM Sheet1$";
dr = cmdExcel.ExecuteReader();
DataTable dtExcel = new DataTable();
dtExcel.Load(dr);

Answer 1

Try using the IMEX=1 parameter in your connection string (google for more info).

I think what's happening is that Excel is inferring the data type of each column from the first few rows. When it then encounters a value that does not match the inferred data type, it treats it as null.

Answer 2

I had this problem, but rather than setting IMEX=1 I set the registry setting TypeGuessRows to 0 rather than the default 8, I read that IMEX would be needed somewhere but it seems to pick up this registry change either way.
However, I am using the Jet provider rather than Ace so that might make a difference.

For me I found the setting at: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

Answer 3

I answered a similar question here. Here I've copied and pasted the same answer for your convenience:

I had this same problem, but was able to work around it without resorting to the Excel COM interface or 3rd party software. It involves a little processing overhead, but appears to be working for me.

1. First read in the data to get the column names
2. Then create a new DataSet with each of these columns, setting each of their DataTypes to string.
3. Read the data in again into this new dataset. Voila - the scientific notation is now gone and everything is read in as a string.

Here's some code that illustrates this, and as an added bonus, it's even StyleCopped!

public void ImportSpreadsheet(string path)
{
    string extendedProperties = "Excel 12.0;HDR=YES;IMEX=1";
    string connectionString = string.Format(
        CultureInfo.CurrentCulture,
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"{1}\"",
        path,
        extendedProperties);

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        using (OleDbCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT * FROM [Worksheet1$]";
            connection.Open();

            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            using (DataSet columnDataSet = new DataSet())
            using (DataSet dataSet = new DataSet())
            {
                columnDataSet.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(columnDataSet);

                if (columnDataSet.Tables.Count == 1)
                {
                    var worksheet = columnDataSet.Tables[0];

                    // Now that we have a valid worksheet read in, with column names, we can create a
                    // new DataSet with a table that has preset columns that are all of type string.
                    // This fixes a problem where the OLEDB provider is trying to guess the data types
                    // of the cells and strange data appears, such as scientific notation on some cells.
                    dataSet.Tables.Add("WorksheetData");
                    DataTable tempTable = dataSet.Tables[0];

                    foreach (DataColumn column in worksheet.Columns)
                    {
                        tempTable.Columns.Add(column.ColumnName, typeof(string));
                    }

                    adapter.Fill(dataSet, "WorksheetData");

                    if (dataSet.Tables.Count == 1)
                    {
                        worksheet = dataSet.Tables[0];

                        foreach (var row in worksheet.Rows)
                        {
                            // TODO: Consume some data.
                        }
                    }
                }
            }
        }
    }
}

Answer 4

I answered another question much like this one.

In short, the settings that control the ACE driver behavior are located in the registry at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Set ImportMixedTypes to Text and set TypeGuessRows to 0 (or some suitably large number like 1000) and you should get the behavior you are expecting.