Export Data in Text File Using BCP

Question

Export Data in Text File Using BCP

manish.mathur

SSChasing Mays

Points: 634
More actions
February 6, 2009 at 2:43 am

#205179

Comments posted to this topic are about the item Export Data in Text File Using BCP

Viewing 10 posts - 1 through 9 (of 9 total)

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 1

Good article. One suggestion would be to use the pipe character ( | ) as the default separator. Some people and place names have embedded commas. Some countries use commas as decimal separators.

Keep up the good work. Nice job.

ATBCharles Kincaid

starunit SSCommitted Points: 1817 More actions · Answer 2

Thanks for the quick and simple overview of how to use BCP with xp_cmdshell from within an SP.

Question: Are there any options to delimit text values? I'd like to use BCP to produce 'csv' files, however some of my data has commas and line-feeds/carriage-returns in it: I'd need to double-quote data those values...

Thanks,

Mark

Mark
Just a cog in the wheel.

manish.mathur SSChasing Mays Points: 634 More actions · Answer 3

Hi Mark,

There is no option available in bcp to delimit the some field value into double-quote but you can you double-quote for all the field values.

Thanks

Manish Mathur

starunit SSCommitted Points: 1817 More actions · Answer 4

manish.mathur (2/15/2009)
Hi Mark,
There is no option available in bcp to delimit the some field value into double-quote but you can you double-quote for all the field values.
Thanks
Manish Mathur

Yeah, that's what I thought.. Oh well.

Thanks for the reply!!

Mark

Mark
Just a cog in the wheel.

rahman82pk SSC Enthusiast Points: 152 More actions · Answer 5

I am running the procedure and getting error:

SQLState = S0002, NativeError = 208

Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'EMS.dbo.tblBilling'.

SQLState = 37000, NativeError = 8180

Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.

Please guide

Regards:

Abdul Rahman

iang-1141812 SSC Enthusiast Points: 132 More actions · Answer 6

There is no option available in bcp to delimit the some field value into double-quote but you can you double-quote for all the field values.

Hi There, in your post you mentioned that there is a way to double quote all fields in BCP, could you tell me how to achieve this please, as I am struggling to find anywhere that tells me.

cheers

gregg_dn SSCommitted Points: 1596 More actions · Answer 7

Apparently this sp depends on being able to remotely access SQL Server. As I'm running the express version it won't allow the connection. According to BOL neither will the Developer Edition

http://support.microsoft.com/kb/914277

Caruncles Hall of Fame Points: 3299 More actions · Answer 8

Good stuff, and timely for me since I've been working on moving 10GB of data to a remote location. After waiting 1.5 hours using an INSERT/SELECT I realized it wasn't going to work. I want this job to be automated on a daily basis and was wondering if you had a ready-made, magic script that would import the txt file into another database on the other end?

Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

May 16, 2016 at 7:22 am

#1879244

Thanks for the script.