Error reading Excel data

[Johnny]

Hi all:

I am trying to write some code to read an Excel spreadsheet from an ASP.NET
application. For some reason no fields that have a number in them are read,
while text data is read just fine.

I am using a connection string:

Conn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
Conn1 += @"C:\Temp\Success Partners\Test.xls";
Conn1 += ";Extended Properties=\"Excel 8.0;HDR=YES;\"";:

and then filling a data set:
cmd1 = new System.Data.OleDb.OleDbCommand("SELECT * FROM ['Test$']", conn1);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = cmd1;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
GridView1.DataSource = objDataset1.Tables[0].DefaultView;
GridView1.DataBind();

but for some reason no numbers show up in the grid or the dataset (I parsed
through it to check the data).

Can anyone suggest what I am doing wrong and/or a better way to do this.

I found some examples that use some Interop classes to read the worksheets,
but I couldn't get those working from ASP.NET...

Thanks for any and all help.

 

[Cindy M.]

Hi Johnny,

I am trying to write some code to read an Excel spreadsheet from an ASP.NET
application. For some reason no fields that have a number in them are read,
while text data is read just fine.

I am using a connection string:

Conn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
Conn1 += @"C:\Temp\Success Partners\Test.xls";
Conn1 += ";Extended Properties=\"Excel 8.0;HDR=YES;\"";:

The Jet ODBC driver and OLEDB provider make an on-the-spot decision about the
data type of a column (field) when connecting, based on the first eight or so
rows (records) of data. Mixed number/text data types are not supported. If the
data contains mixed text and numbers, then you have a problem. See this KB
article

Http://support.microsoft.com/kb/257819/en-us

And then filling a data set:
cmd1 = new System.Data.OleDb.OleDbCommand("SELECT * FROM ['Test$']", conn1);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = cmd1;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
GridView1.DataSource = objDataset1.Tables[0].DefaultView;
GridView1.DataBind();

but for some reason no numbers show up in the grid or the dataset (I parsed
through it to check the data).

Can anyone suggest what I am doing wrong and/or a better way to do this.

I found some examples that use some Interop classes to read the worksheets,
but I couldn't get those working from ASP.NET...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail