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 Beginner - Please Help

Hi,

I am completely new to the BCP utility and fairly new to SQL Server

I am learning from a book and I am trying the following example (the server
I'm learning on is called contractor and a password has not been give to the
sa)

bcp pubs..authors out authors.txt -C -r \n -t, -U sa -P -S contractor

When I run this in Query Analyser i get the error message..

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I have tried puuting quotes around the database and the table name as
follows-

bcp "pubs..authors" out authors.txt -C -r \n -t, -U sa -P -S contractor

and get the error

Server: Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a stored procedure.

Please can anybody help me to get this first bit working?

Thanks in anticipation.In your bcp statement, is the -C supposed to indicate that it is a
character file? If so, it needs to be lowercase (-c), otherwise it
indicates the code page, which expects a value. Also, I don't know if
this makes a difference, but I've never used spaces between the options
and the values (for example, -Usa instead of -U sa). I thought I
remembered both working, but in the help I didn't see anything about
the space being allowed and the samples given do not have spaces.

HTH,
-Tom.|||Stevie D (Steve@.127.0.0.1) writes:
> I am completely new to the BCP utility and fairly new to SQL Server
> I am learning from a book and I am trying the following example (the
> server I'm learning on is called contractor and a password has not been
> give to the sa)
> bcp pubs..authors out authors.txt -C -r \n -t, -U sa -P -S contractor
> When I run this in Query Analyser i get the error message..

You don't BCP from Query Analyzer. You run it from a command-line
window.

And, as Thomas pointed out, you should use -c and not -C. The
spacing should be OK, though.

> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.

(The reason you get this syntax error is that bcp is not an SQL
command. Everything that is an identifier, but not a command is
taken as a stored procedure command. Next token is pubs. When calling
stored procedures quotes around strings that follow identifier syntax
are optional. (This is to permit us to write "sp_help tbl", instead
of "EXEC sp_help 'tbl'".) When the dot comes there is no longer
any saver.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Batch invocation of Drillthrough action

Does anyone happen to have an example of a batch invocation of a drillthrough action?

Essentially, what I'd like the ability to do is use the results of the drillthrough to feed into another process (i.e., to use as input to an SSIS package or load into a relational table.) However, I've found no examples of what the action settings would need to be in the Application field, for example.

Maybe drillthrough isn't the proper action to define it (although it would be the easiest.) Maybe a regular action returning a dataset or rowset? Any help or idea is a appreciated.

Not sure what you trying to achieve. The drillthrough is invoked in a content of the particular cell.

You can also take a look at the syntax of the DRILLTHOUGH statement http://msdn2.microsoft.com/en-us/library/ms145964.aspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I think what he means is doing individual drillthroughs on a bunch of cells, and then combining all the drillthrough results.

I had a similar need for this as well, awhile back

Thanks,

JGP

|||JGP, you're situation is another aspect of what I'm attempting to accomplish.

As an example (using Adventure Works), let's say I have the customer.education on the Y-axis and something else, say Product Categories, on the X-axis. My measure is Internet Order Quantity. My filter criteria is Customers in the US. I've sliced it up until I had found that 461 orders for Mountain Bikes were placed by Customers with a Graduate degree.

Now, that could be one customer purchasing 461 mountain bikes, or 461 customers purchasing one each. Regardless, there are customers associated with each order represented in that cell and I want to create a list of those people from my grid. With an action, I was hoping to do maybe a batch drillthrough that, rather than having the results back to the screen, output to a table. So far, I've not found how to do that, or if it even can be done.

Really, I can't find any examples of what a mechanism a batch action uses. All examples are of interactive reports, URLs, and drillthroughs.