BCP output - adding double quotes to text


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.


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

      (pk            INT       IDENTITY(1,1)
      ,Address_1      VARCHAR(100)
      ,City            VARCHAR(100))

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


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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Quick answer is:

Exec Master..xp_Cmdshell 'bcp "SELECT CHAR(34) + RTRIM(Address_1) + CHAR(34) + ',' + CHAR(34) + RTRIM(City) + CHAR(34) FROM mcg1" queryout "C:\mcg1.csv" -c -t,"'

I'm not sure about how to include them using a format file (if there is a way).
meowshAuthor Commented:

Thanks for your quick reply.
Unfortunately it doesnt work.  When I execute your syntax I get

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]

It's probably the single quotes in the select statement causing the problem. Try this:

Exec Master..xp_Cmdshell 'bcp "SELECT CHAR(34) + RTRIM(Address_1) + CHAR(34), CHAR(34) + RTRIM(City) + CHAR(34) FROM mcg1" queryout "C:\mcg1.csv" -c -t,"'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.