October 1, 2013 at 11:45 am
I'm trying to export a text string that includes " to a text file using BCP.
This code works as expected by exporting the word blah into a text file on my C drive:
-- Turn on cmdshell
EXEC sp_configure 'xp_cmdshell', 1
reconfigure
go
DECLARE @cmd varchar(1000)
SET @cmd = 'bcp "SELECT ''blah''" queryout "C:\bcpout.txt" -w -T'
EXEC master..xp_cmdshell @cmd
However if I change my text string from 'blah' to include quotation marks so it reads 'blah"blah', it fails to do anything.
DECLARE @cmd varchar(1000)
SET @cmd = 'bcp "SELECT ''blah"blah''" queryout "C:\bcpout.txt" -w -T'
EXEC master..xp_cmdshell @cmd
Is there a way I can get the quotation marks exported to my text file using BCP?
October 1, 2013 at 11:59 am
Unless I'm mistaken, you just need to escape the double-quotes in your BCP string; it should change from this:
DECLARE @cmd varchar(1000)
SET @cmd = 'bcp "SELECT ''blah"blah''" queryout "C:\bcpout.txt" -w -T'
EXEC master..xp_cmdshell @cmd
To this:
DECLARE @cmd varchar(1000)
SET @cmd = 'bcp "SELECT ''blah""blah''" queryout "C:\bcpout.txt" -w -T'
EXEC master..xp_cmdshell @cmd
Gave it a shot and got a text file with blah"blah as a result; is that what you're expecting to get?
- 😀
October 1, 2013 at 12:03 pm
Yes that works and was what I was trying to do, thanks hisakimatama. I thought I had tried that with the code I'm working with and was surprised it didn't work but it does when I do it here with this simplified version. I must attempt again with the actual text I'm trying to export now that I know this wasn't the problem. Thanks again!
October 1, 2013 at 12:06 pm
Not a problem. I had the same problem when I was working with BCP initially; drove me batty for a bit until I realized the double quotes need to be escaped, too.
If your real data is still having problems, post some samples of it if you can; I can have a look at it, or someone else may get to it if I'm out of the office by then.
- 😀
October 1, 2013 at 12:20 pm
Okay great, thanks - have to go do something else this evening but I will do tomorrow if I can't figure it out in the morning.
October 9, 2013 at 6:00 am
After working on this further, I've been able to get things going but hit a roadblock when I encountered text qualifiers in the header of my data. I just created a new related topic to ask a question on it. If you've run into this issue before it would be great to hear any suggestions you may have
The new thread is here: http://www.sqlservercentral.com/Forums/Topic1503039-392-1.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply