Problem with ADO.Net and Excel

[Bill Gower]

I am reading an Excel spreadsheet into a C# program and am having the
following problem. Here is my code and then I will explain the problem.

DataTable dt;

string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ Server.MapPath(".") + "\\Uploads\\Dues.xls;Extended Properties=Excel
8.0;";
string strSQL = "SELECT * FROM [" + name + "$]" ;
// create Objects of ADOConnection and ADOCommand
OleDbConnection myConn = new OleDbConnection(strDSN);
OleDbDataAdapter adapter;
dt = new DataTable("DuesImportSTP");
dt.Columns.Add("name", System.Type.GetType("System.String"));
dt.Columns.Add("empno", System.Type.GetType("System.String"));
dt.Columns.Add("Amount", System.Type.GetType("System.Double"));
dt.Columns.Add("SSN", System.Type.GetType("System.String"));


later in the method I loop through the datatable rows and the
empNo column is empty for any row that has a "-" in the column such as
2222-22. The column in the excel spreadsheet is formatted as Text and yet
ADO.Net is trying to read it as a number and when it finds a row that has
a - in the column, it makes the column null.


How can I work around this?

Thanks
Bill

 

[Paul Clement]

¤ I am reading an Excel spreadsheet into a C# program and am having the
¤ following problem. Here is my code and then I will explain the problem.
¤
¤ DataTable dt;
¤
¤ string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
¤ + Server.MapPath(".") + "\\Uploads\\Dues.xls;Extended Properties=Excel
¤ 8.0;";
¤ string strSQL = "SELECT * FROM [" + name + "$]" ;
¤ // create Objects of ADOConnection and ADOCommand
¤ OleDbConnection myConn = new OleDbConnection(strDSN);
¤ OleDbDataAdapter adapter;
¤ dt = new DataTable("DuesImportSTP");
¤ dt.Columns.Add("name", System.Type.GetType("System.String"));
¤ dt.Columns.Add("empno", System.Type.GetType("System.String"));
¤ dt.Columns.Add("Amount", System.Type.GetType("System.Double"));
¤ dt.Columns.Add("SSN", System.Type.GetType("System.String"));
¤
¤
¤ later in the method I loop through the datatable rows and the
¤ empNo column is empty for any row that has a "-" in the column such as
¤ 2222-22. The column in the excel spreadsheet is formatted as Text and yet
¤ ADO.Net is trying to read it as a number and when it finds a row that has
¤ a - in the column, it makes the column null.
¤
¤
¤ How can I work around this?

You probably need to add the IMEX argument to your connection string to handle mix mode columns:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=e:\\My Documents\\Book2.xls;" +
"Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34;


Paul
~~~~
Microsoft MVP (Visual Basic)