Opening from Excel into a dataset, issue with mixed datatypes

[Eric Sabine]

I have an excel sheet with a column of mixed data types, i.e., numeric &
text. ADO.NET is scanning the first few rows and deciding on numeric so
when we come across text, the data becomes blank. That being the case I set
the extended properties setting to include IMEX=1 but this seems to be of no
help. Below is the stripped out code of what I am attempting to do. Thank
you for your help.

string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" +
Server.MapPath("~/Output/myData.xls") + ";" +
"Extended Properties=\"Excel
8.0;IMEX=1;HDR=NO;\"";
OleDbConnection oleCon = new OleDbConnection();
oleCon.ConnectionString = sConnectionString;
oleCon.Open();
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [2005$A5:A133]",
oleCon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1);
DataGrid1.DataSource = objDataset1.Tables[0];
DataGrid1.DataBind();
oleCon.Close();
oleCon.Dispose();

 

[Val Mazur \(MVP\)]

Hi Eric,

IMEX=1 works for sure and I am using it to avoid same issue. I am suspecting
the connection string ignores your Extended properties for some reason. For
the testing try to remove all the extended properties except IMEX=1 and
remove double quotes for it as well, like

string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" +
Server.MapPath("~/Output/myData.xls") + ";" +
"Extended Properties=IMEX=1;";