Can't read some excel cell into ADO.NET

[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");

 

[Paul Clement]

¤ 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)

 

[ad]

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?

 

[Paul Clement]

¤ 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)

 

[ad]

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();

 

[Paul Clement]

¤ 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)

 

[ad]

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

 

[Paul Clement]

¤ 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)

 

[ad]

Thanks,
It ok now, The DataSource is a type mistake.
What are the functions of (char)34 and IMEX=1?

 

[Paul Clement]

¤ 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)