Problems reading in an Excel file in C#

Question

I'm reading an Excel file with OLDB Connection using this code

        var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

        var fileName = string.Format("{0}\\s23.xls", Directory.GetCurrentDirectory());
        var adapter = new OleDbDataAdapter("SELECT * FROM [TEJ3$]  ", connectionString);
        DataTable dt=new DataTable();
        adapter.Fill(dt, "Table1");

and after runing this code my data table is filled. But I have a column that has many string cells and few empty cells ; in excel file this cells have numeric values.

Someone has an idea?

Answer 1

Check the first examples here: http://www.connectionstrings.com/excel

What often goes wrong is that Excel will estimate the type of a column based upon the first X rows. When after that the values don't match, these rows get empty values. I'm afraid that going into the registry is sometime the only way to get the Excel driver to scan all rows first (as described in the connectionstrings.com article).

Play around with the HDR and IMEX settings in your environment. In some cases that will help as well.