troubleshooting Question

BCP output - adding double quotes to text

Avatar of meowsh
meowsh asked on
Microsoft SQL Server
3 Comments1 Solution3341 ViewsLast Modified:
Experts,

How can I program BCP to output text items in double quotes (")?
Here is an example (please try it) that trys to output some columns from a table to csv file.  However, due to the existence of commas within the fields, the comma separation gets messed up.

------------------------------------
USE [MASTER]

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'mcg1')
      DROP TABLE mcg1
go

CREATE TABLE mcg1
      (pk            INT       IDENTITY(1,1)
      ,Address_1      VARCHAR(100)
      ,City            VARCHAR(100))
go

INSERT INTO mcg1 (Address_1, City) VALUES ('100 Road1, Suburb1' , 'BigCity1')
INSERT INTO mcg1 (Address_1, City) VALUES ('200 Road2, Suburb2' , 'BigCity2')

SELECT * FROM mcg1

Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1" queryout "C:\mcg1.csv" -c -t,"'

------------------------------------
The output I get is below.  You can see how the use of commas in the text makes the comma separate list all confused
100 Road1, Suburb1,BigCity1
200 Road2, Suburb2,BigCity2

Thus what I want is
"100 Road1, Suburb1","BigCity1"
"200 Road2, Suburb2","BigCity2"

You can do this OK in DTS by specifying the text identifier to be double-quotes.
I do NOT want to use DTS and want to be able to do via a T-SQL procedure.  Note that the real table I will export from has numeric datatypes and I would prefer NOT to wrap them in double-quotes too.

Thus, for the points, how can I alter the Exec Master..xp_Cmdshell command, to wrap each text field in double quotes.  I may have to use a format file in which case please provide the format file too.

Thanks in advance
Meowsh
ASKER CERTIFIED SOLUTION
PSSUser

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros