Using Openrowset to select from Excel sheet with space in name

Question

Post reply

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?

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

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

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 · Answer 2

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

WayneS SSC Guru Points: 95461 More actions · Answer 3

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 · Answer 4

scg1889

Old Hand

Points: 333

August 26, 2010 at 3:42 am

#1212173

Yes that did the trick, thank you.