OPENROWSET with spaces in sheet name

• jlperez19

  SSC Enthusiast

  Points: 193

  More actions

  April 30, 2010 at 8:34 am

  #146767

  Hi All,

  I'm trying to use the following statement to import data from an excel worksheet into sql, but I haven't been able to get the syntax right, since the source worksheet contains spaces in the tab name. I have tried:

  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=P:\Statements\Activity20100422.xls',Trade Date Detail$)

  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=P:\Statements\Activity20100422.xls',[Trade Date Detail$])

  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=P:\Statements\Activity20100422.xls',[Trade Date Detail]$)

  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=P:\Statements\Activity20100422.xls','Trade Date Detail'$)

  But I always get the error:

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

  I'd greatly appreciate any help on this.

  Many thanks in advance!

• jhouston

  SSC Veteran

  Points: 290

  More actions

  August 23, 2010 at 6:40 pm

  #1210745

  Retry the second one prepended with "SELECT * FROM "

  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=P:\Statements\Activity20100422.xls','SELECT * FROM [Trade Date Detail$]')

• Petrpan

  SSC Journeyman

  Points: 79

  More actions

  October 5, 2012 at 3:07 am

  #1545949

  Hi,

  For me this works

  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=P:\Statements\Activity20100422.xls',['Trade Date Detail$'])

  PetrPan

• Arthur Kirchner

  Ten Centuries

  Points: 1239

  More actions

  May 31, 2013 at 8:16 am

  #1620097

  Hi Petr,

  that worked for me, too, thx.

  ... after I closed the excel file that is... 🙂