By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 992 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

How to read data from Excel spreadsheet?

P: n/a
Hi All,

I need to read data from a Excel spreadsheet, but I got the problem
when I tried the code below:

StringBuilder sbConn = new StringBuilder();
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;D ata Source=" );
sbConn.Append(excelFile);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));

//open spreadsheet and query data
OleDbConnection cnExcel = new OleDbConnection(sbConn.ToString());
cnExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1]",cnExcel);

try
{
OleDbDataReader drExcel = cmdExcel.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
The exception message is "The Microsoft Jet database engine could not
find the object 'Sheet1'. Make sure the object exists and that you
spell its name and the path name correctly."

I checked the Sheet name, it is "Sheet1", what am I doing wrong?

Thanks for the help!

May 25 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On May 25, 11:05 am, barbara_d...@yahoo.com wrote:
Hi All,

I need to read data from a Excel spreadsheet, but I got the problem
when I tried the code below:

StringBuilder sbConn = new StringBuilder();
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;D ata Source=" );
sbConn.Append(excelFile);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));

//open spreadsheet and query data
OleDbConnection cnExcel = new OleDbConnection(sbConn.ToString());
cnExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1]",cnExcel);

try
{
OleDbDataReader drExcel = cmdExcel.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

The exception message is "The Microsoft Jet database engine could not
find the object 'Sheet1'. Make sure the object exists and that you
spell its name and the path name correctly."

I checked the Sheet name, it is "Sheet1", what am I doing wrong?

Thanks for the help!
I access Excel data from VB.NET, but I use the ODBC Microsoft Excel
Driver, DriverID 790. If that driver works similar to the driver you
are using then you need to suffix the worksheet name with the dollar
sign character, as in:

OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1$]",cnExcel);
May 25 '07 #2

P: n/a
On May 25, 8:31 am, z...@construction-imaging.com wrote:
On May 25, 11:05 am, barbara_d...@yahoo.com wrote:


Hi All,
I need to read data from a Excel spreadsheet, but I got the problem
when I tried the code below:
StringBuilder sbConn = new StringBuilder();
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;D ata Source=" );
sbConn.Append(excelFile);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));
//open spreadsheet and query data
OleDbConnection cnExcel = new OleDbConnection(sbConn.ToString());
cnExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1]",cnExcel);
try
{
OleDbDataReader drExcel = cmdExcel.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
The exception message is "The Microsoft Jet database engine could not
find the object 'Sheet1'. Make sure the object exists and that you
spell its name and the path name correctly."
I checked the Sheet name, it is "Sheet1", what am I doing wrong?
Thanks for the help!

I access Excel data from VB.NET, but I use the ODBC Microsoft Excel
Driver, DriverID 790. If that driver works similar to the driver you
are using then you need to suffix the worksheet name with the dollar
sign character, as in:

OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1$]",cnExcel);- Hide quoted text -

- Show quoted text -
Thank you for the response.

I tried the spreadsheet name with $ sign, the exception message is:
'"Sheet1$' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."

Can I get more suggestions?

Thanks!

May 25 '07 #3

P: n/a
On May 25, 9:09 am, barbara_d...@yahoo.com wrote:
On May 25, 8:31 am, z...@construction-imaging.com wrote:


On May 25, 11:05 am, barbara_d...@yahoo.com wrote:
Hi All,
I need to read data from a Excel spreadsheet, but I got the problem
when I tried the code below:
StringBuilder sbConn = new StringBuilder();
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;D ata Source=" );
sbConn.Append(excelFile);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));
//open spreadsheet and query data
OleDbConnection cnExcel = new OleDbConnection(sbConn.ToString());
cnExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1]",cnExcel);
try
{
OleDbDataReader drExcel = cmdExcel.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
The exception message is "The Microsoft Jet database engine could not
find the object 'Sheet1'. Make sure the object exists and that you
spell its name and the path name correctly."
I checked the Sheet name, it is "Sheet1", what am I doing wrong?
Thanks for the help!
I access Excel data from VB.NET, but I use the ODBC Microsoft Excel
Driver, DriverID 790. If that driver works similar to the driver you
are using then you need to suffix the worksheet name with the dollar
sign character, as in:
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1$]",cnExcel);- Hide quoted text -
- Show quoted text -

Thank you for the response.

I tried the spreadsheet name with $ sign, the exception message is:
'"Sheet1$' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."

Can I get more suggestions?

Thanks!- Hide quoted text -

- Show quoted text -
Barbara,

I would have thought the previous suggestion would have worked for
you. I understand the '$' notation doesn't work in some case though.

Have you seen this example:
http://support.microsoft.com/default...b;EN-US;316934

HTH
-Jay

May 25 '07 #4

P: n/a
On May 25, 10:08 am, Jay Riggs <jay.s.ri...@gmail.comwrote:
On May 25, 9:09 am, barbara_d...@yahoo.com wrote:


On May 25, 8:31 am, z...@construction-imaging.com wrote:
On May 25, 11:05 am, barbara_d...@yahoo.com wrote:
Hi All,
I need to read data from a Excel spreadsheet, but I got the problem
when I tried the code below:
StringBuilder sbConn = new StringBuilder();
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;D ata Source=" );
sbConn.Append(excelFile);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));
//open spreadsheet and query data
OleDbConnection cnExcel = new OleDbConnection(sbConn.ToString());
cnExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1]",cnExcel);
try
{
OleDbDataReader drExcel = cmdExcel.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
The exception message is "The Microsoft Jet database engine could not
find the object 'Sheet1'. Make sure the object exists and that you
spell its name and the path name correctly."
I checked the Sheet name, it is "Sheet1", what am I doing wrong?
Thanks for the help!
I access Excel data from VB.NET, but I use the ODBC Microsoft Excel
Driver, DriverID 790. If that driver works similar to the driver you
are using then you need to suffix the worksheet name with the dollar
sign character, as in:
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1$]",cnExcel);- Hide quoted text -
- Show quoted text -
Thank you for the response.
I tried the spreadsheet name with $ sign, the exception message is:
'"Sheet1$' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."
Can I get more suggestions?
Thanks!- Hide quoted text -
- Show quoted text -

Barbara,

I would have thought the previous suggestion would have worked for
you. I understand the '$' notation doesn't work in some case though.

Have you seen this example:http://support.microsoft.com/default...b;EN-US;316934

HTH
-Jay- Hide quoted text -

- Show quoted text -
Thank you so much! I'll look at this information.

May 25 '07 #5

P: n/a
Hi,

Most probably the sheet name is incorrect,
Take a look at this code, it does read an excel without any problem, let me
know if it worked for you

string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
sourceFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string srcQuery = "Select * from [" + GetExcelSheetNames(
sourceFile)[0] + "]";

OleDbConnection srcConn = new OleDbConnection( srcConnString);
srcConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);

readerExcel = objCmdSelect.ExecuteReader(
CommandBehavior.CloseConnection);
Here is how you get the name of the sheets
static String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;

try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables , null);

if(dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}

return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}
<ba**********@yahoo.comwrote in message
news:11**********************@a26g2000pre.googlegr oups.com...
Hi All,

I need to read data from a Excel spreadsheet, but I got the problem
when I tried the code below:

StringBuilder sbConn = new StringBuilder();
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;D ata Source=" );
sbConn.Append(excelFile);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));

//open spreadsheet and query data
OleDbConnection cnExcel = new OleDbConnection(sbConn.ToString());
cnExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1]",cnExcel);

try
{
OleDbDataReader drExcel = cmdExcel.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
The exception message is "The Microsoft Jet database engine could not
find the object 'Sheet1'. Make sure the object exists and that you
spell its name and the path name correctly."

I checked the Sheet name, it is "Sheet1", what am I doing wrong?

Thanks for the help!

May 25 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.