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=Microsoft.Jet.OLEDB.4.0;Data 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!

 

[zacks]

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;Data 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);

 

[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=Microsoft.Jet.OLEDB.4.0;Data 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!

 

[Jay Riggs]

On May 25, 11:05 am, (e-mail address removed) 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;Data 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.aspx?scid=kb;EN-US;316934

HTH
-Jay

 

[barbara_dave]

On May 25, 8:31 am, (e-mail address removed) wrote:

On May 25, 11:05 am, (e-mail address removed) 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;Data 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.aspx?scid=kb;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=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 = 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();
}
}
}