Sheet name doesn't end with dollar ($) symbol

Question

5.00/5 (2 votes)

See more:

UPDATE:
I am putting a hold to my question.
I got some clue and missed few things to mention here.
Sorry to waste your valuable time. Will update the question very soon and will post the answer too if I succeed with it.

Thanks all for your valuable time.

Well, this can be very basic question for some people here but I haven't worked much on this area and tried to search in internet with various combinations of keyword but failed to get any result which relevant to my problem.

Problem: In one of my project I have written some code to read data rows from an excel sheet with something like following-

C#

System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "]", con);

It is working fine for almost all the excel files supplied by the customer since couple of years or so but one of the file is failing to obtain the data rows. It just retrieves the header row and not other data rows. When I copied the content to a new excel file, everything works fine.

I went for debugging and found that, for this particular file the sheet name comes as just the name (i.e. "Sheet1") and doesn't end with a dollar symbol("$") at the end. But for other files, it's coming with the required $ symbol.

When I changed the code to append a $ symbol if there isn't already, the code worked fine. All the files are in the same format i.e, .xlsx

Can someone please educate me on this, if I am missing anything basic here?
Any reference to good article/documentation would also be helpful.

Please let me know in case any further details are required.

Thanks in advance!

What I have tried:

--Debugging to find out the issue as mentioned above

Posted 18-May-17 22:07pm

Suvendu Shekhar Giri

Updated 18-May-17 23:27pm

v3

Add a Solution

Comments

CHill60 19-May-17 4:16am

Where is that variable SheetName assigned?
I have experienced users renaming a file to filename.xls and expecting it to magically become an Excel spreadsheet (!). It's possibly just a "broken" file.

Suvendu Shekhar Giri 19-May-17 5:09am

I am getting sheet names using GetOleDbSchemaTable()
The file opens correctly with MS Excel though.

Richard MacCutchan 19-May-17 4:19am

How do you get the sheet name from the file?

Suvendu Shekhar Giri 19-May-17 5:25am

Hi all!
I am putting a hold to my question.
I got some clue and missed few things to mention here.
Sorry to waste your valuable time. Will update the question very soon and will post the answer too if I succeed with it.

Thanks all for your valuable time.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2017-05-18T23:13:00

I can not give a final answer but stumbled upon this some years ago too. The following is what I can extract from my C++ source code comments.

OLE DB (and ODBC) are database interfaces that treat Excel files like databases. They use two kind of table types that are of interest here: System tables and normal tables. With Excel drivers system tables are mapped to Excel sheets (with dollar sign) and normal tables are mapped to named ranges (without dollar sign).

When creating sheets with Excel, there will be only a sheet (system table). But when using the ODBC driver to create a sheet, both types are created (this may also apply to OLE DB but I'm not sure).

~~You don't have specified how you get the sheet names. But I guess you are using something similar to my method~~
[EDIT: GetOleDbSchemaTable() mentioned meanwhile in a comment] You are using something similar to my method for getting the table names:[/EDIT]
I'm enumerating the tables using ADODB::adSchemaTables and getting the names from the TABLE_NAME field. But this will return all tables (system tables and normal tables) so that there may be duplicate entries. There is also the TABLE_TYPE field. But this field always returns TABLE (normal table) and never SYSTEM_TABLE; even for system tables (sheets). It seems to be a bug. Reference: OleDbSchemaGuid.Tables Field (System.Data.OleDb)[^].

So you might get a name without dollar sign when the sheet has been created using ODBC (or maybe also with OLE DB). You might ask your customer if this applies to the failing file to prove it.

However, the name may also refer to a named range instead of a sheet.

The solution that I have used:
I'm enumerating the tables and store the results in a list. If the passed name does not have a trailing dollar sign, it is appended. If that name does not exist in the list, it is added. So I avoid duplicate entries and treat normal table names as sheets too. But this will ignore the existance of named ranges that have names that are not used for sheets.