Problem with ADO.Net and Excel

13 Feb 2007 9:54 PM

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

14 Feb 2007 5:27 PM

Paul Clement

On Tue, 13 Feb 2007 16:54:57 -0500, "Bill Gower" <billgo***@charter.net> wrote:

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