Gajendra Gajendra - 1 month ago 11
C# Question

Reading excel file using OLEDB Data Provider

I am using OLEDB Data Provider to read excel file, but the problem is that in excel sheet some cloumn has an invalid value for example instead of number string is there,
When I read this invalid value I get an empty string instead of actual value.

enter image description here

for above screenshot when i read value john getting empty string.

So is there any way to read this invalid value?

Any help will be appreciated.

The Code is to read excel file

private DataTable ReadExcelFile(string sheetName, string path)
{

using (OleDbConnection conn = new OleDbConnection())
{
DataTable dt = new DataTable();
string Import_FileName = path;
string fileExtension = Path.GetExtension(Import_FileName);
if (fileExtension == ".xls")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
if (fileExtension == ".xlsx")
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
using (OleDbCommand comm = new OleDbCommand())
{
comm.CommandText = "Select * from [" + sheetName + "$]";

comm.Connection = conn;

using (OleDbDataAdapter da = new OleDbDataAdapter())
{
da.SelectCommand = comm;
da.Fill(dt);
return dt;
}

}
}
}

Answer

You need to set value for TypeGuessRows Registry key to 0, this way driver will set data type based on all column values instead of first 8 (default).

The location of the key differs from version to version of driver, you can easily Google it based on your specific version. For example for Access Connectivity Engine 2007 it would be

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

By the way, you do not need Jet to read XLS files, ACE is perfectly capable of this as well.