Using Openrowset to select from Excel sheet with space in name

• scg1889

  Old Hand

  Points: 333

  More actions

  August 20, 2010 at 7:07 am

  #225045

  Hi,

  I'm trying to import from an excel spreadsheet but am getting an error when i try and import from a worksheet that has a space in it e.g. "sheet a". I've tried single quotes, double quotes around it to no avail:

  insert INTO tableA select * FROM OPENROWSET

  ('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=C:\Test.xls', [sheet a$])

  Is it possible to do this? alternatively is it possible for OPENROWSET to use a parameter to select a particular sheet each time ie the 3rd worksheet in a spreadsheet?

• WayneS

  SSC Guru

  Points: 95461

  More actions

  August 20, 2010 at 7:13 am

  #1209638

  This link has some useful tips for how to accomplish this.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• scg1889

  Old Hand

  Points: 333

  More actions

  August 23, 2010 at 4:01 am

  #1210324

  yes i'd already gone down that route before asking to no avail. hence the question.

• WayneS

  SSC Guru

  Points: 95461

  More actions

  August 23, 2010 at 3:41 pm

  #1210720

  This link (one of the ones returned from the first one above) shows how to do this.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• scg1889

  Old Hand

  Points: 333

  More actions

  August 26, 2010 at 3:42 am

  #1212173

  Yes that did the trick, thank you.