returning values within quotes from openrowset

[dfwcharles]

I'm querying a Excel spread sheet and would like the values that are returned to be contained with qoutes.

Here's my query:

SELECT * FROM OpenRowSet
('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\test', 'SELECT column1 FROM
[sheet1$]')

I want the results like this 'column1data'

I've tried adding multiple quotes and + to the column name in the query. Depending on the number of quotes, I either get 'invalid object' or it just returns the name of the column. Sometimes it will even return the column name in quotes.

any ideas?
Thanks
Charles

 

[dfwcharles]

Nevermind I got it, heres the correct query:


SELECT * FROM OpenRowSet
('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\test', 'SELECT ''''''''+column1+'''''''' FROM
[sheet1$]')

Eight quotes on each end.

 

[tlbroadbent]

You can also do the following.

SELECT Column1=char(39) + Column1 + char(39)
FROM OpenRowSet ('MSDASQL',
'Driver=Microsoft Excel Driver
(*.xls);DBQ=c:\test', 'SELECT column1
FROM [sheet1$]')
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.