Why does Excel sometimes put a $ on sheet name?

Question

I have found sometimes but not always, Excel puts a $ at the end of a worksheet name but that is not seen in Excel, only when trying to import it into SQL Server using C#. I've run into a number of different scenarios with this.

1. It leaves the original worksheet but also creates a second empty 'hidden' worksheet with the $ at the end.
2. There is only 1 worksheet and it has the data but the '$' is appended to the name.
3. The original worksheet is there with the $ and there is another copy with $_filteredDatabase appended. (I was able to find that _filteredDatabase is where Excel stores the 'filtered' data.)

The reason I ask is in the past I've been able to tell my application to import if there is only 1 worksheet or if it does not end in $. However there have been a few times that I've ended up with a "worksheet$" and a "worksheet$_".

So I'm just wondering if anyone knows what the $ is or why/when it gets added?

Answer 1

When you use Excel Source -Excel source extracts data from worksheets or ranges in Microsoft Excel workbooks- SQL Server Books-Online says:

Important:
In Excel, a worksheet or range is the equivalent of a table or view. The list of available tables in the Excel Source and Destination editors displays existing worksheets (identified by the $ sign appended to the worksheet name, such as Sheet1$) and named ranges (identified by the absence of the $ sign, such as MyRange).

or

Data sources
The source of data in an Excel workbook can be a worksheet, to which the $ sign must be appended (for example, Sheet1$), or a named range (for example, MyRange). In a SQL statement, the name of a worksheet must be delimited (for example, [Sheet1$]) to avoid a syntax error caused by the $ sign. The Query Builder automatically adds these delimiters. When you specify a worksheet or range, the driver reads the contiguous block of cells starting with the first non-empty cell in the upper-left corner of the worksheet or range. Therefore you cannot have empty rows in the source data, or an empty row between title or header rows and the data rows.