Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP text out that includes quotation marks Expand / Collapse
Author
Message
Posted Tuesday, October 01, 2013 11:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
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?
Post #1500518
Posted Tuesday, October 01, 2013 11:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 491, Visits: 5,482
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?


----------------------------------
My journal of things I'm learning about SQL
Post #1500526
Posted Tuesday, October 01, 2013 12:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
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!
Post #1500529
Posted Tuesday, October 01, 2013 12:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 491, Visits: 5,482
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.


----------------------------------
My journal of things I'm learning about SQL
Post #1500537
Posted Tuesday, October 01, 2013 12:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
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.
Post #1500543
Posted Wednesday, October 09, 2013 6:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
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
Post #1503044
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse