Post reply

OPENDATASOURCE syntax for Excel worksheet with space in name

  • April 8, 2009 at 11:02 am

    #72610

    Hi guys,

    The following works OK (run from SQL server management studio express)...

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\test.xls', [Definitions$])

    ...in this example the worksheet is called 'Definitions' I can't find the syntax to use when the worksheet has spaces in its name e.g...

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\test.xls', [True Agents$])

    ... doesn't work, giving the error:

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "True Agents$". The table either does not exist or the current user does not have permissions on that table.

    I've tried quotes etc, in various permutations but getting nowhere. Help much appreciated 😉

    Thanks, Pete

  • April 8, 2009 at 11:14 am

    #974531

    I think you can use an underscore there. I had to solve the same problem once, and I think that was what did it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 8, 2009 at 12:23 pm

    #974579

    Thanks GSquared,

    I can't get putting an underscore in to work when the worksheet name still has a space. However, I'm taking the easy way out in the short term and just renaming the worksheets with underscores.

    cheers,

    Pete

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply