Can't read some excel cell into ADO.NET

  • Thread starter Thread starter ad
  • Start date Start date
A

ad

I use ado.net to read excel file into dataset.
But there are some cell that hold some value in Excel, but when I read it
with ado.net, and display in a dataGrid
It disappear.
How can I do that?




















----------------------------------------------------------------------------
-----------------------------------------------------
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=" + savePath +
"; Extended Properties=Excel 8.0;";
string sSelect = "SELECT * FROM [Sheet1$]";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand(sSelect, objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(ds, "XLData");
 
¤ I use ado.net to read excel file into dataset.
¤ But there are some cell that hold some value in Excel, but when I read it
¤ with ado.net, and display in a dataGrid
¤ It disappear.
¤ How can I do that?
¤

Try adding the IMEX parameter to your connection string:

string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + savePath +
"; Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;"
+ (char)34;


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thank,
I added IMEX=1 according your advice.
But when my application open the connection, it error, with message:
Can't find ISAM.
How can I do?
 
¤ Thank,
¤ I added IMEX=1 according your advice.
¤ But when my application open the connection, it error, with message:
¤ Can't find ISAM.
¤ How can I do?

Could you post your connection string? This error usually indicates the syntax is incorrect.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
This is my code:

string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\Health.xls ; Extended Properties=Excel 8.0;";
string sSelect = "SELECT * FROM [Sheet1$]";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand(sSelect, objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(ds, "XLData");
objConn.Close();
 
¤ This is my code:
¤
¤ string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
¤ Source=c:\\Health.xls ; Extended Properties=Excel 8.0;";
¤ string sSelect = "SELECT * FROM [Sheet1$]";
¤ OleDbConnection objConn = new OleDbConnection(sConnectionString);
¤ objConn.Open();
¤
¤ OleDbCommand objCmdSelect = new OleDbCommand(sSelect, objConn);
¤
¤ OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
¤
¤ objAdapter1.SelectCommand = objCmdSelect;
¤ objAdapter1.Fill(ds, "XLData");
¤ objConn.Close();
¤

I need to see your connection string with the IMEX argument - the one that is generating the error.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thank,
My connection string with the IMEX argument is :
string sConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\\Health.xls ; Extended
Properties=Excel 8.0;IMEX=1;";
 
¤ Thank,
¤ My connection string with the IMEX argument is :
¤ string sConnectionString =
¤ "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\\Health.xls ; Extended
¤ Properties=Excel 8.0;IMEX=1;";
¤

Data Source should be two words. Try the following:

string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=c:\\Health.xls;" +
"Extended Properties=" +
(char)34 + "Excel 8.0;IMEX=1;" + (char)34;


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thanks,
It ok now, The DataSource is a type mistake.
What are the functions of (char)34 and IMEX=1?
 
¤ Thanks,
¤ It ok now, The DataSource is a type mistake.
¤ What are the functions of (char)34 and IMEX=1?
¤
¤

(char)34 is simply for handling the syntax of Extended Properties. The IMEX parameter is for columns
that use mixed numeric and alpha values. The Excel driver will typically scan the first several rows
in order to determine what data type to use for each column. If a column is determined to be numeric
based upon a scan of the first several rows, then any rows with alpha characters in this column will
be returned as Null. The IMEX parameter (1 is input mode) forces the data type of the column to
text so that alphanumeric values are handled properly.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top