Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
young555
Starting Member
9 Posts |
 Posted - 2009-05-06 : 16:10:14
|
| Hi,I am using the bcp method to copy the results of one of my tables into a .csv file. However, I encounter a problem where I need to add in a comma (,) to the end of one of my fields because the field contains comma already and so messes up my data.I have tried to use + ‘,’ but it doesn’t work. I think because bcp has different formatting.Can someone please suggest how I can add one (comma) to end of apple and pears field please. The following is the relevant section of my code. declare @bcp_cmd VARCHAR(8000) SET @bcp_cmd = 'bcp "SELECT id,apple,pears FROM Cr.dbo.fruit" queryout C:\fruit.csv -c -t,' execmaster..xp_cmdshell @bcp_cmd |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-05-06 : 16:14:18
|
| "SELECT id,apple,pears,',' FROM Cr.dbo.fruit" |
 |
|
|
young555
Starting Member
9 Posts |
Posted - 2009-05-06 : 16:24:31
|
| thanks for the advice but i need the comma to come straight after and be part of the apples and pears variables because the comma will be used for the whole row of that variable.for example, the field apple is a whole chuck of text that contains data with commas and line breaks already in it. so when i execute the query, the data for this field inserts into many different rows. this is not what i want. i want all the data for this field to be in one cell of the .csv file. i thought that a way to do this would be to add a comma after the field name. do you think this will work for a bcp query?any suggestions please,thank you |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-06 : 16:34:24
|
"SELECT id, apple, pears + ',' FROM Cr.dbo.fruit"Have you tried to put your data surrounded by " instead?Or have the file tab-separated?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
how can i add a comma in my bcp method