BCP Quote delimited fields

Question

Post reply

BCP Quote delimited fields

David-377753

Right there with Babe

Points: 770
More actions
June 21, 2007 at 3:39 pm

#117230

Hello,
How can I use BCP to write the data to a text file but quote delimited format?
Thank you

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Exclusive help for BCP is available in BOL. did you check that. If you want us to script for you can be more specific.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

george sibbald SSC Guru Points: 104210 More actions · Answer 2

As long as the table is not huge, dts may be a better option for you as you want to amend the data on the way out.

---------------------------------------------------------------------

Shawn McGehee Ten Centuries Points: 1239 More actions · Answer 3

I would agree with George, you should look at DTS first. If BCP is required, you can use the queryout option with BCP. It would be a little bit of a pain, but it can be done.

BCP "SELECT '\"' + name + '\"' FROM sys.databases" QUERYOUT C:\Test.bcp -S localhost -T -c

I just tested this, and it works fine. You will have to put the delimeted quotes around each of the fields, but if BCP has to be used, this will work. Just remember to escape the double quote marks in your query.

One last thing, non text fields will have to be converted to text datatypes to allow concatenation (i.e. date, numeric). I really think DTS is the way to go, though.

David-377753 Right there with Babe Points: 770 More actions · Answer 4

Hi Guys,

Yuppp... DTS is the way to go... I was working on it for 3 hours and at the end, used DTS

Thank you

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

What about bcp out through a view ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

David-377753 Right there with Babe Points: 770 More actions · Answer 6

didn't try that... but I guess it's gonna be the same result

no?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

If you use OSQL with the -h-1 option and the -o option, something like the following works very well...

USE Northwind

SET NOCOUNT ON

SELECT QUOTENAME('CustomerID','"')+','

+ QUOTENAME('CompanyName','"')+','

+ QUOTENAME('ContactName','"')+','

+ QUOTENAME('ContactTitle','"')+','

+ QUOTENAME('Address','"')+','

+ QUOTENAME('City','"')+','

+ QUOTENAME('Region','"')+','

+ QUOTENAME('PostalCode','"')+','

+ QUOTENAME('Country','"')+','

+ QUOTENAME('Phone','"')+','

+ QUOTENAME('Fax','"')

UNION ALL

SELECT QUOTENAME(ISNULL(CustomerID,''),'"')+','

+ QUOTENAME(ISNULL(CompanyName,''),'"')+','

+ QUOTENAME(ISNULL(ContactName,''),'"')+','

+ QUOTENAME(ISNULL(ContactTitle,''),'"')+','

+ QUOTENAME(ISNULL(Address,''),'"')+','

+ QUOTENAME(ISNULL(City,''),'"')+','

+ QUOTENAME(ISNULL(Region,''),'"')+','

+ QUOTENAME(ISNULL(PostalCode,''),'"')+','

+ QUOTENAME(ISNULL(Country,''),'"')+','

+ QUOTENAME(ISNULL(Phone,''),'"')+','

+ QUOTENAME(ISNULL(Fax,''),'"')

FROM dbo.Customers

--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)

David-377753 Right there with Babe Points: 770 More actions · Answer 8

Jeff,

Thanks... your solution somehow works, but the only problem is that when I generate the file, I get this

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>

and then there is like 6 CrLf

this is the way it looks:

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> "f1","f2","f3","f4"

"1","1","0","11111111"

"12","1","0","11111111"

Sergiy SSC Guru Points: 110208 More actions · Answer 9

What was the first post about?

I don't see any "quote delimited format" here.

Can you explain what exactly do you need?

_____________
Code for TallyGenerator

David-377753 Right there with Babe Points: 770 More actions · Answer 10

Hello,

How can I use BCP to write the data to a text file but quote delimited format?

Thank you

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Sorry David, didn't list all of the switches you needed 'cause I thought you might already know them... You'll need the following switches, as well...

-s"" -w256 -n

Replace the "256" with the appropriate number and take a look at "OSQL" in Books Online to see what these switches do...

--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)

David-377753 Right there with Babe Points: 770 More actions · Answer 12

David-377753

Right there with Babe

Points: 770

June 22, 2007 at 11:26 pm

#714751

Thanks a lot Jeff

I will try that