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.
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;
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.