bcp output format question

Question

bcp output format question

Kerstin Safari

Right there with Babe

Points: 754
More actions
November 30, 2006 at 5:42 pm

#97903

I'm trying to bcp out a query from SQL Server 2000, and I need it to end up DOUBLE quote/comma delimited. I have figured out how to delimit the file with single quotes and commas, but it doesn't seem to recognize the double quotes.
What I have to bcp out the single quotes and commas is:
exec master..xp_cmdshell'BCP "SELECT * FROM ##REQUEST" queryout C:\REQUEST.prn /c -t ''","'
(the double quotes/commas thing is what the customer wants, as well as the naming convention, even though .prn files are generally fixed width. And the customer is always right.)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Isn't it amazing that such a simple format is so very complex programatically speaking? Why they didn't make BCP do this a little easier, I'll never know...

This would be how to get the double quotes with a comma in as a delimiter and it puts a double quote at the end of every line...

exec master..xp_cmdshell 'BCP "SELECT * FROM ##Request" queryout C:\REQUEST.prn /c -t \",\" -r \"\n -S putyourservernamehere -T'

Trust me, it works even though BOL says it won't... but it doesn't put one at the beginning of each line. That's a problem!! You can't even use a format file to fix it because the first quote is not a server column and BCP won't allow you to skip columns for output.

You need to prefix the first column of the ##Request table with a double-quote and use the above BCP command. Make sure the datatype of the first column is VARCHAR something...

UPDATE ##Request
SET firstcolname = '"'+firstcolname

Since ##Request is a temporary table, you shouldn't have a problem with that at all... if it is a problem, then you'll need to list every column in the table in the query and concatenate the '"' to the beginning of the first column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kerstin Safari Right there with Babe Points: 754 More actions · Answer 2

Thanks! It worked, although instead of updating the ##REQUEST table I just made the first field in the table ('"' + firstcolname) as fieldone. I kept getting error messages the other way, warning me that it would have to truncate data, and that it was not going to continue.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Cool... thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)