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.

Answer 2

It means that the row or column which comes after the dollar sign is anchored or absolute in the mentioned sheet.

for Example: [Sheet1$A1:B4] means referring particular range from Sheet if u want to get range of data needs to give like above

Example1: [Sheet1$] means referring all row and columns from Sheet1