How to read data from Excel spreadsheet?

[barbara_dave]

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=Micro soft.Jet.OLEDB. 4.0;Data Source=" );
sbConn.Append(e xcelFile);
sbConn.Append(" ;Extended Properties=");
sbConn.Append(C onvert.ToChar(3 4));
sbConn.Append(" Excel 8.0;HDR=Yes;IME X=2");
sbConn.Append(C onvert.ToChar(3 4));

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

try
{
OleDbDataReader drExcel = cmdExcel.Execut eReader();
}
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!

[zacks]

On May 25, 11:05 am, barbara_d...@ya hoo.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=Micro soft.Jet.OLEDB. 4.0;Data Source=" );
sbConn.Append(e xcelFile);
sbConn.Append(" ;Extended Properties=");
sbConn.Append(C onvert.ToChar(3 4));
sbConn.Append(" Excel 8.0;HDR=Yes;IME X=2");
sbConn.Append(C onvert.ToChar(3 4));

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

try
{
OleDbDataReader drExcel = cmdExcel.Execut eReader();
}
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("S elect * From
[Sheet1$]",cnExcel);

[barbara_dave]

On May 25, 8:31 am, z...@constructi on-imaging.com wrote:

On May 25, 11:05 am, barbara_d...@ya hoo.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=Micro soft.Jet.OLEDB. 4.0;Data Source=" );
sbConn.Append(e xcelFile);
sbConn.Append(" ;Extended Properties=");
sbConn.Append(C onvert.ToChar(3 4));
sbConn.Append(" Excel 8.0;HDR=Yes;IME X=2");
sbConn.Append(C onvert.ToChar(3 4));

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

try
{
OleDbDataReader drExcel = cmdExcel.Execut eReader();
}
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("S elect * 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!

[Jay Riggs]

On May 25, 9:09 am, barbara_d...@ya hoo.com wrote:

On May 25, 8:31 am, z...@constructi on-imaging.com wrote:

On May 25, 11:05 am, barbara_d...@ya hoo.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=Micro soft.Jet.OLEDB. 4.0;Data Source=" );
sbConn.Append(e xcelFile);
sbConn.Append(" ;Extended Properties=");
sbConn.Append(C onvert.ToChar(3 4));
sbConn.Append(" Excel 8.0;HDR=Yes;IME X=2");
sbConn.Append(C onvert.ToChar(3 4));

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

try
{
OleDbDataReader drExcel = cmdExcel.Execut eReader();
}
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("S elect * 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

[barbara_dave]

On May 25, 10:08 am, Jay Riggs <jay.s.ri...@gm ail.comwrote:

On May 25, 9:09 am, barbara_d...@ya hoo.com wrote:

On May 25, 8:31 am, z...@constructi on-imaging.com wrote:

On May 25, 11:05 am, barbara_d...@ya hoo.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=Micro soft.Jet.OLEDB. 4.0;Data Source=" );
sbConn.Append(e xcelFile);
sbConn.Append(" ;Extended Properties=");
sbConn.Append(C onvert.ToChar(3 4));
sbConn.Append(" Excel 8.0;HDR=Yes;IME X=2");
sbConn.Append(C onvert.ToChar(3 4));

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

try
{
OleDbDataReader drExcel = cmdExcel.Execut eReader();
}
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("S elect * 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.

[Ignacio Machin \( .NET/ C# MVP \)]

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=Micr osoft.Jet.OLEDB .4.0;Data Source=" +
sourceFile + @";Extended Properties=""Ex cel 8.0;HDR=YES;""" ;
string srcQuery = "Select * from [" + GetExcelSheetNa mes(
sourceFile)[0] + "]";

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

readerExcel = objCmdSelect.Ex ecuteReader(
CommandBehavior .CloseConnectio n);
Here is how you get the name of the sheets
static String[] GetExcelSheetNa mes(string excelFile)
{
OleDbConnection objConn = null;
System.Data.Dat aTable dt = null;

try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = "Provider=Micro soft.Jet.OLEDB. 4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Exce l 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.GetOleD bSchemaTable(Ol eDbSchemaGuid.T ables, 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**********@y ahoo.comwrote in message
news:11******** **************@ a26g2000pre.goo glegroups.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=Micro soft.Jet.OLEDB. 4.0;Data Source=" );
sbConn.Append(e xcelFile);
sbConn.Append(" ;Extended Properties=");
sbConn.Append(C onvert.ToChar(3 4));
sbConn.Append(" Excel 8.0;HDR=Yes;IME X=2");
sbConn.Append(C onvert.ToChar(3 4));

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

try
{
OleDbDataReader drExcel = cmdExcel.Execut eReader();
}
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!