Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Export Data in Text File Using BCP


Export Data in Text File Using BCP

Author
Message
manish.mathur
manish.mathur
Mr or Mrs. 500
Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)

Group: General Forum Members
Points: 544 Visits: 70
Comments posted to this topic are about the item Export Data in Text File Using BCP
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 2383
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
starunit
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 2566
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
manish.mathur
Mr or Mrs. 500
Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)

Group: General Forum Members
Points: 544 Visits: 70
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
starunit
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 2566
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
rahman82pk
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 151
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
iang-1141812
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 22

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
gregg_dn
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 1034
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
Caruncles
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 240
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."
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8712 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search