The Microsoft Jet database engine could not find the object 'Sheet

[Guest]

I have found a problem while accessing Excel spreadsheets via OleDB (I'm not
sure if this is the correct newsgroup for this, but here goes...)

The problem only occurs if I have used filtering in the spreadsheet. Could
anyone tell me if this is a bug? Is there a workaround?

I get the following error when calling connection.GetSchema("Columns");
- 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.

Steps to reproduce:

1. Create a new spreadsheet in Execl 2007 (haven't tried earlier)
2. In A1 type a column heading, e.g. ID
3. In A2 type a value, e.g. 1
4. In A2, enable filtering (Ctrl+Shift+L)
5. Save the file in Excel 97-2003 compatible format.
6. Using .NET 2.0, attempt to get the metadata from the workbook using the
following code:

string ctnStr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" +
excelFile + "; Extended Properties=\"Excel 8.0;IMEX=1\";";

using (OleDbConnection excelCtn = new OleDbConnection(ctnStr))
{
excelCtn.Open();

inputTableInfo = excelCtn.GetSchema("Tables");
inputColumnInfo = excelCtn.GetSchema("Columns"); // fails
here
}

The code fails with the following exception when trying to access the column
metadata:

Type : System.Data.OleDb.OleDbException, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : 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.
Source : Microsoft JET Database Engine
ErrorCode : -2147467259
Errors : System.Data.OleDb.OleDbErrorCollection
Data : System.Collections.ListDictionaryInternal
TargetSite : System.Data.DataTable GetSchemaRowset(System.Guid,
System.Object[])
Stack Trace : at
System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema,
Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema,
Object[] restrictions)
at System.Data.OleDb.OleDbMetaDataFactory.PrepareCollection(String
collectionName, String[] restrictions, DbConnection connection)
at System.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection
connection, String collectionName, String[] restrictions)
at
System.Data.ProviderBase.DbConnectionInternal.GetSchema(DbConnectionFactory
factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection,
String collectionName, String[] restrictions)
at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName,
String[] restrictionValues)
at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName)

It seems that by enabling filtering, a hidden worksheet is created using the
name of the filtered worksheet followed by a trailing underscore. This
worksheet is listed as one of the "tables" when calling
connection.GetSchema("Tables") but it makes the call to GetSchema("Columns")
fall over.

The main problem I am having is that disabling the filter does not remove
this hidden worksheet. So once filtering is enabled the workbook is useless
to me.

Any help would be appreciated.

Thanks

Stu

 

[Wei Lu [MSFT]]

Hi Stu,

I reproduce this issue. I am performing some research on it. I appreciate
your patience.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wei Lu [MSFT]]

Hello Stu,

I would like to know whether you open that excel, did this issue still
occured?

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

I don't quite understand the question.

 

[Guest]

Actually, I understand now. The problem doesn't occur if the spreadsheet is
open in Excel. However, the spreadsheet won't be open in production.

At the moment I am using a work around, which is to filter out any table
names ending in $_ and to request column info for each of the remaining
tables individulally, using the "restrictions" overload, e.g.

DataTable tempTable = excelCtn.GetSchema("Columns", new string[] { null,
null, row["TABLE_NAME"].ToString(), null });

This works fine. But I still think it is a bug that the call to
GetSchema("Columns") fails if you've used filtering in the spreadsheet (even
if you've subsequently turned filtering off).

 

[Wei Lu [MSFT]]

Hello Stu,

I am still researching this issue. If any discover, I will let you know.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steven Cheng[MSFT]]

Hi Stu,

I'm the community lead of managed newsgroup support team. I'd like to check
the status on this issue, have you got any further progress? Wei has
discussed this issue with me and we agree that this should be a problem
with the OLEdb provider which connectiong the excel and pull out the
sheet's schema info. Since we think this a serious problem and may affect
your application developing, if you're still monitoring on this issue and
want to continue look for some further solution on this, we'd like to
leverage some further research to help you. You can contact us through
email (in our signature and remove "online") so that we can communicate
with you more efficiently on this.

Please feel free to let me know if there is anything need help.

Thank you for your posting.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Rogers]

If needed, I am experiencing this sort of problem and I would be happy to work with support to provide information which might lead to an understanding or a resolution of this problem.

Briefly,

I am using C# and ADO.NET to read an Excel workbook with multiple worksheets. When I use the code below, I get multiple entries for each sheet.

For example, I have a sheet named "Plate_2". When I do the GetSchema call, I see entries for "Plate_2" and one for "Plate_2$".

More confusing, for the sheet named "Plate_3", I have "Plate_3", "Plate_3$", and "Plate_3$_".

Which of these is the worksheet? Why are there threee different entries? What do the differences indicate? Are there any other funky things like "Print_Area" which I should expect to see?

Thanks,
David

public static string LoadWorkbookSchema(string workbookPath, ref DataTable dtWorksheets)
{
try
{
using (OleDbConnection connection = GetWorkbookConnection(workbookPath))
{
dtWorksheets = connection.GetSchema("Tables");
}
foreach (DataRow dr in dtWorksheets.Rows)
{
if (dr["TABLE_NAME"].ToString().EndsWith("Print_Area"))
dr.Delete();
}
}
catch (Exception exception)
{
return exception.Message;
}
return "";
}


public static OleDbConnection GetWorkbookConnection(string WorkBookPath)
{
// Note that IMEX=1 was added to eliminate problems with data in the "9th-plus line"
// having a different data format than the first 8. dwr
string connectionString =
string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""",
WorkBookPath);

OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
return connection;
}


EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[slipeternal]

It is 10 years later, and I am experiencing this exact same problem. Did Microsoft really never correct this error?