Problem with ( and ) in Excel Sheet name

Question

I have an excel sheet that i am using as a datasource (SQL server 2008), however worsheet i'm working on in the Excel Sheet has the name of "name(7)", it's out of my hands to the change the name of the sheet manually. I keep getting this error when trying to access the name(7) :

OLE DB provider 'datasource_name' does not contain table 'name(7)$'. The table either does not exist or the current user does not have permissions on that table.

However when I change the name of the sheet to "name7" it works fine. The SQL management studio isn't allowing me to add a table with a name containing brackets.

Any idea how can I get over this ?

Thanks a lot in advance

Answer 1

How about using brackets?

USE tempdb;
GO
CREATE TABLE [name(7)] (i int);
INSERT [name(7)] values (5),(11),(23);
SELECT * FROM [name(7)]
/*
i
5
11
23
*/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 2

Thank you very much for your reply.

Actually I've used brackets, what I noticed now that the table names with brackets work fine when I'm creating them in the local database I'm working on. But this error happens when I'm creating that table for a database on a linked server.

Any ideas what could be causing this ?

Answer 3

Hi Malhoosh1,

Please try this method as below:
SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=c:\YourExcelFilename.xls', 'SELECT * FROM [Sheet1$]') AS XL

Meanwhile you can refer to reply in this thread with the similar issue as yours.

Additional you can refer to this How to use Excel with SQL Server linked servers and distributed queries 

---

Regards, Amber zhang

Answer 4

Thank you amber zhang for your reply.

The problem is solved finally:

Instead of

Select LinkedServerName...[name(7)$] 

I used

Select LinkedServerName...['name(7)$']

I discovered later that the problem isn't in adding table of name containing brackets, the table is added to the linked server but the problem was in selecting it. so adding ' and ' to the tablename fixed the problem.

Thank you all very much for your helpful replies