BCP output - putting double quotes around text

10 Feb 2006 5:38 PM

mgale1

Folks,

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, 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
Mgale1

10 Feb 2006 6:16 PM

Mark Williams

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

What you see above is a single quote, followed by a double-quote, followed
by another single quote. Instead of trying to get bcp to do the formatting,
have the query do it.

--

Show quoteHide quote

"mgale1" wrote:

> Folks,
>
> 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, 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
> Mgale1

10 Feb 2006 6:56 PM

Mark Williams

Sorry, didn't read through your entire post.

Exec Master..xp_Cmdshell 'bcp "SELECT CASE WHEN ISNUMERIC(Address_1) = 1
THEN Address_1 ELSE ''"'' + Address_1 + ''"'' END, CASE WHEN ISNUMERIC(City)
= 1 THEN City ELSE ''"'' + City + ''"'' END
FROM mcg1" queryout
"C:\mcg1.csv" -c -t,"'

Couple of things about the above:
-ISNUMERIC has been known to evaluate to 1 for things that aren't really
numeric. See http://www.aspfaq.com/show.asp?id=2390.
-Whatever datatype Address_1 and City are, if they are not numeric, must be
implicitly convertible to a character data type. If it isn't, you could use
CAST or CONVERT to force it.

--

Show quoteHide quote

"Mark Williams" wrote:

> Exec Master..xp_Cmdshell 'bcp "SELECT '"' + Address_1 + '"', '"' + City + '"'
> FROM mcg1" queryout
> "C:\mcg1.csv" -c -t,"'
>
> What you see above is a single quote, followed by a double-quote, followed
> by another single quote. Instead of trying to get bcp to do the formatting,
> have the query do it.
>
> --
>
>
> "mgale1" wrote:
>
> > Folks,
> >
> > 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, 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
> > Mgale1

11 Feb 2006 8:03 PM

Erland Sommarskog

mgale1 (mga***@discussions.microsoft.com) writes:

> How can I program BCP to output text items in double quotes (")?

You could use a format file:

8.0
4
1 SQLCHAR 0 0 "\"" 0 ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\r\n" 3 col3 ""

This format file defines an output for three fields on the form

"data","more data","even, more, data"

There are four fields in the format file, because there are to be an
empty field to get the first " in place. The 0 on that row, means that
there is no database-column mapping here.

I will need to add that I've only tried this for input, not for output.
But it should work...

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13 Feb 2006 12:21 PM

mgale1

Folks,

Thanks for your replies - I am grateful for your help.

Unfortunately, I dont think your suggestions are going to work for me
Mark Williams - I cant get your syntax to work at all. Query Analyser gets
confused over all the quotes and simply wont run the BCP command. Instead it
returns the standard BCP error msg like 'BCP commands should be in the form
of..." etc

Erland - I have be having trouble getting your example to work. My command
is Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1"
queryout "C:\mcg1.csv" -t, -f c:\formatfile.txt"' and I get "Host-file
columns may be skipped only when copying into the Server" as an error.

--
Thanks for your help - another colleague has found a way around this problem
for me by using DTSRUN on a command line. Thus please dont put too much
effort into working on this any further unless it is your wish
Thanks again, much appreciated
Mgale1

13 Feb 2006 11:46 PM

Erland Sommarskog

mgale1 (mga***@discussions.microsoft.com) writes:

> Erland - I have be having trouble getting your example to work. My
> command is Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM
> mcg1" queryout "C:\mcg1.csv" -t, -f c:\formatfile.txt"' and I get
> "Host-file columns may be skipped only when copying into the Server" as
> an error.

Drat, it didn't work out. Hm, shat if you change the SELECT to

SELECT '', Address_1, City FROM mcgl

and update the format file to read 1 2 3 and 0 1 2 in the database-
column column?

(Sorry for not testing myself, but it's about bed-time for me.)

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx