Read Excel data in ASP.NET Error

[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.

 

[sloan]

I've had success with these.

//specific column names
private readonly string EXCEL_TITLE_SELECT_QUERY = "SELECT top 10
[title_id],[title],[pubdate],[type],[price] FROM [Sheet1$]";

// * from
private readonly string EXCEL_TITLE_SELECT_QUERY = "SELECT top 100 * FROM
[Sheet1$]"; //Alternate Version


You can get rid of the "top 10" or "top 100".

I would try a new, blank sheet. Leave it as Sheet1 for the name.

Put in 1 header row.

EmpID,EmpName,HireDate

and fill in about 5 rows or something of data.

and get that to work first. Then move to your more customized sheet/data.

Be VERY anal about your connection string.
Every ; (space) and whatnot matters.

 

[shashank kadge]

I've had success with these.

//specific column names
private readonly string EXCEL_TITLE_SELECT_QUERY = "SELECT top 10
[title_id],[title],[pubdate],[type],[price] FROM [Sheet1$]";

// * from
private readonly string EXCEL_TITLE_SELECT_QUERY = "SELECT top 100 * FROM
[Sheet1$]"; //Alternate Version

You can get rid of the "top 10" or "top 100".

I would try a new, blank sheet. Leave it as Sheet1 for the name.

Put in 1 header row.

EmpID,EmpName,HireDate

and fill in about 5 rows or something of data.

and get that to work first. Then move to your more customized sheet/data.

Be VERY anal about your connection string.
Every ; (space) and whatnot matters.

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.
http://mscrmguy.blogspot.com/- Hide quoted text -

- Show quoted text -

hi,
I have some code which would work. let me know if u still need the
solution for this. sorry i m 2 lazy to dig into my archieves n post
the code @ this moment.

-
shashank kadge

 

[Johnny]

Hi Guys:

Thanks for the replies. I found out that if the Excel cell had any
formatting applied to it the data was not being read. I am still trying to
find a way around this but for now I am setting all the fields to "test".

--
John.

http://mscrmguy.blogspot.com/

I've had success with these.

//specific column names
private readonly string EXCEL_TITLE_SELECT_QUERY = "SELECT top 10
[title_id],[title],[pubdate],[type],[price] FROM [Sheet1$]";

// * from
private readonly string EXCEL_TITLE_SELECT_QUERY = "SELECT top 100 * FROM
[Sheet1$]"; //Alternate Version

You can get rid of the "top 10" or "top 100".

I would try a new, blank sheet. Leave it as Sheet1 for the name.

Put in 1 header row.

EmpID,EmpName,HireDate

and fill in about 5 rows or something of data.

and get that to work first. Then move to your more customized
sheet/data.

Be VERY anal about your connection string.
Every ; (space) and whatnot matters.

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.
http://mscrmguy.blogspot.com/- Hide quoted text -

- Show quoted text -

hi,
I have some code which would work. let me know if u still need the
solution for this. sorry i m 2 lazy to dig into my archieves n post
the code @ this moment.

-
shashank kadge

 

[sloan]

Here's another option:

http://www.sqlservercentral.com/col...eragingxpexcelxmlandopenxmlfordataimports.asp


Convert it all to XML, and pull it out that way.

Hi Guys:

Thanks for the replies. I found out that if the Excel cell had any
formatting applied to it the data was not being read. I am still trying to
find a way around this but for now I am setting all the fields to "test".

--
John.

http://mscrmguy.blogspot.com/

I've had success with these.

//specific column names
private readonly string EXCEL_TITLE_SELECT_QUERY = "SELECT top 10
[title_id],[title],[pubdate],[type],[price] FROM [Sheet1$]";

// * from
private readonly string EXCEL_TITLE_SELECT_QUERY = "SELECT top 100 * FROM
[Sheet1$]"; //Alternate Version

You can get rid of the "top 10" or "top 100".

I would try a new, blank sheet. Leave it as Sheet1 for the name.

Put in 1 header row.

EmpID,EmpName,HireDate

and fill in about 5 rows or something of data.

and get that to work first. Then move to your more customized
sheet/data.

Be VERY anal about your connection string.
Every ; (space) and whatnot matters.





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.

--
John.

http://mscrmguy.blogspot.com/- Hide quoted text -

- Show quoted text -

hi,
I have some code which would work. let me know if u still need the
solution for this. sorry i m 2 lazy to dig into my archieves n post
the code @ this moment.

-
shashank kadge