Data not fills correctly in DataTable using OleDbDataAdapter

Question

When I try to import excel sheet, it does not fetches some integer values. I am using DevExpress GridControl for exporting data from Grid. After export I change value of some cell's(which have blank value) to integer let's say 123 then on import it does not fetches that integer value in DataTable.

I have posted same issue on DevExpress support center "Export values in Improper Cell". They said the issue is from MSDN not by their control's. Please download the sample from given DevExpress link & also watch the video attached for more detailed information.

I have used following code for import.

 private System.Data.DataTable GetDataTableFromFile(string fileName)
    {
        string query = string.Empty;
        //// string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties=Excel 8.0;";
        string connectionStringV12 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;";
        string connectionStringV4 = "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0};Extended Properties=Excel 8.0;";
        System.Data.DataTable dataTable = new System.Data.DataTable();
        OleDbConnection obedbConnection = new OleDbConnection(string.Format(connectionStringV4, fileName));
        try
        {
            if (obedbConnection.State != ConnectionState.Open)
            {
                obedbConnection.Open();
            }
        }
        catch (Exception)
        {
            ////Diff. Connetion String
            obedbConnection = new OleDbConnection(string.Format(connectionStringV12, fileName));
        }

        ////Get First SheetName From Xls File 
        string sheetName = GetSheetNameFromFile(obedbConnection);

        if (sheetName != null)
        {
            ////Query for Reading all Data from File
            query = "select * from [" + sheetName + "]";
        }

        if (!string.IsNullOrEmpty(query))
        {
            OleDbDataAdapter data = new OleDbDataAdapter(query, obedbConnection);
            data.Fill(dataTable);
        }
        return dataTable;
    }

    /// <summary>
    /// Gets First SheetName of excel File 
    /// </summary>
    /// <param name="con">Connection object.</param>
    /// <returns>return sheet name.</returns>
    private string GetSheetNameFromFile(OleDbConnection con)
    {
        try
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            var oledbTableSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            if (oledbTableSchema.Rows.Count > 0)
            {
                string sheetName = oledbTableSchema.Rows[0].ItemArray[2].ToString();
                if (string.IsNullOrEmpty(sheetName))
                {
                    throw new Exception("Sheet Not Found");

                }
                return sheetName;
            }
            return string.Empty;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }
}

So, Can any one please help me to solve this issue?

Answer 1

Adding the IMEX = 1 property to my connection string, solved my issue.

    string connectionStringV4 = "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0};Extended Properties=Excel 8.0;IMEX=1;";

There is also similar behavior reported in the below thread

Not able to read integer in excel file consistantly

For more detailed answer please go to following link :

Data not fills correctly in DataTable using OleDbDataAdapter