BCP output - putting double quotes around text

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
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 woul
d
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
Mgale1Exec 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' , 'BigCity
1')
> INSERT INTO mcg1 (Address_1, City) VALUES ('200 Road2, Suburb2' , 'BigCity
2')
> 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
> 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 wo
uld
> 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 cas
e
> please provide the format file too.
> --
> Thanks in advance
> Mgale1|||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.
"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:
>|||mgale1 (mgale1@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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
over all the quotes and simply wont run the BCP command. Instead i
t
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|||mgale1 (mgale1@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP OUT with Double Quote(") text qualifier?

How do I tell BCP that I want it to output with Double Quotes around my char fields?

It's easy to do in DTS by setting the connection properties of the Text File destination. The reason I can't use DTS is because my table has 329 fields and when I try to modify the destination properties of the transform task, Enterprise Manager bombs. I'd rather use BCP and a format file anyway.

Thanks,

Ray Metz
Everett, WAI just tried using a format file to define the field seperators as "," but BCP barfed. when I switched to ',' it worked. Maybe you could use this.|||Yeah, I had a similar idea to create a view with select '"' + field + '"' for each of the char columns. But since my export has over 300 columns and many of them char or varchar, I wanted an easier solution than going through each column.

Luckily, the Oracle DBA on the other end was ok with me not sending the quotes. The field seperator was a bar (|), and so far we haven't found any bars in the data so I think we're in good shape.

Thanks for your reply.

Ray

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
files
Hi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
Yogish
|||this is the limitation of bcp. use dts instead.

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
filesHi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
--
Yogish|||this is the limitation of bcp. use dts instead.

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
filesHi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
Yogish|||this is the limitation of bcp. use dts instead.

bcp and text qualifying

I would like to output to a csv file using bcp with the fields text qualified by double quotes. Like this:

"1","John Doe","100 main st"
"2","John Smith","101 main st"
"3","John Johnson","102 main st"

Right now here's what my bcp command looks like:
bcp "select id, name, address from People" QUERYOUT d:\data.csv -S aserver -T -t"," -R

And it returns results without quotes:
1,John Doe,100 main st
2,John Smith,101 main st
3,John Johnson,102 main st

How to I get the results in the file with quotes? Thanks for the help.I believe it is

/t"\",\""

The inner double quotes have to be escaped. Hope this helps.|||I tried that, which works for every field, except the 2 outside fields. The first field doesn't have a begining quote and the last field doesnt have an ending quote.

Thanks for the reply though. I appricate the help. Same goes to anyone else who has an idea!|||Alright, so now I tried using a format file, and it worked well, I was able to get double quotes around every field except the first one. So I did some research and supposedly you can add the line:
1 SQLCHAR 0 0 "\"" 1 first_quote ""
to your format file, and it will add a " to the beginning of each record.

Upon trying this I get the error: Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

Doing research on the error I came up with nothing. Any ideas?|||Has anyone created a csv file with text qualifiers using the bcp command?|||Originally posted by WhiZa
Has anyone created a csv file with text qualifiers using the bcp command?

Did you try to format each field in the select command ?
Maybe this is not the best way but is working.|||I guess I figured it out.

I my select statement I added a field like this SELECT '', * FROM...

After doing this I was able to add 1 SQLCHAR 0 0 "\"" 1 first_quote ""
to the format file, and I was able to add a " to the begining of each record. Acctualy it adds a space and a " to each record, but it works all the same.

I hope this helps someone out, it took my slow brain a while to figure it out :)

batch file to run a query

Dear All,
i need to put a query in a batch file to be easy to use by the user.
jusy only double click on it and it will work.
can any one help?Checkout OSQL.EXE in books on line, that should give you what you need.|||'ISQL' as well as 'OSQL' will do. ISQL is DB-Library applications & OSQL is ODBC oreinted...|||OSQL is OLEDB oriented.|||OLEDB is a successor to ODBC... Such online command tools should be downward compatible... thats why its said ODBC oriented...

http://db.ittoolbox.com/documents/document.asp?i=2412

If you check Sql Server Books Online for OSQL u could see the below infrm...

"The osql utility uses the ODBC database application programming interface (API). It is a replacement for the isql command prompt utility based on the DB-Library API. "|||But how do you assign the result of query to a DOS variable?

for example, how do you assign the result of this query (select top 1 name from employee where empname = 'John') to a DOS variable in a batch program? I read that the use of EXIT with osql returns the result but when I tried to use it, it gave some errors. Does anyone has an exmaple?|||I think that only works for integers;
"You can use the result of a SELECT statement as the return value from isql. The first column of the first result row is converted to a 4-byte integer (long). MS-DOS passes the low byte to the parent process or operating-system error level. "

If it's processing strings you want, I guess you'll need to use an output file and read the file from the batchfile (and delete it afterwards).|||How do you do the dos variable assignment if the select is changed to (select count(*) from employee where empname = 'John'). In this case the result will be integer.

Say, I want to check if an employee already exists in a table.

set /p emp='osql -S DBServ -U sa -P passwd -d master -Q "EXIT(select count(*) from employee where empname = 'John')"'
if "%emp%"=="1" goto :EmpFound

It gives me an error - "count(*) was unexpected at this time".|||Depending on your tolerance for pain, you can get assign SQL result set values to environment variables, but there are better answers. Look at Perl (http://www.perl.org/) or KIX (http://www.kixtart.org/) for examples.

-PatP|||I am just trying to modify some pre-existing dos batch scripts and can't use perl.
I am surprised that a simple variable assignment in dos batch script (from osql single value result) is such a complicated thing!|||This will cause somebody to whirl in their grave, but consider this:echo servername is %sn%
osql -E -S. -Q "DECLARE @.c VARCHAR(1000) SELECT @.c = 'SET sn=' + @.@.servername PRINT @.c" >c:\temp\sqlvoodo.bat
call c:\temp\sqlvoodoo.bat
echo servername is %sn%-PatP