Below is a row I bcp'ed from a Sybase 12.5.2 server, the columns are comma delimited:

12345,JOHN,DOE,,,,,,Hello Word, Hello Moon, Hello Sun,abcd,bacu,,,,,,,,

(note that some of the columns are actually empty)



The problem is that data in some of the columns are actually contain commas, for example this column:

Hello Word, Hello Moon, Hello Sun


I need the delimiter to be comma, my question is, is there a way for me to force the data for each column to be wrapped in double-quotes (without using Unix shell commands) ? So now I will now have something like this:


"12345","JOHN","DOE","","","","","","Hello Word, Hello Moon, Hello Sun","abcd","bacu","","","","","","","",""

Try:

-t\",\"

where \ escapes special characters in the korn shell. Might be different depending on your shell, but this one works for me.

I will need to try this instead :


Essentially what is happening is that " " (i.e double-quote followed by space followed by another double-quote...it will appear thoug that a single white spcae is the delimiter) is being used as the delimiter (instead of a comma being the delimiter), so then you get something like this:


And I will get something like this:

Note that there is no double-quotes at the start and there won't be one at the end either.

I came across these links so hopefully I will be able to figure something out:



I came across this link that seems to have some ideas to on doing something like this

BCP export to CSV with double qoutes around data

BCP format
file

Select INTO TextFile ..... How to?

Hello,

Why not to try another field delimiter ? like -t"|"

No...I need the column data to be wrapped in double-quotes. It is also (although unlikely) possible for pipes to be present in the column data.

First of all Thanks for pointing me off in the right direction, I was trying to export an MSSQL database to CSV for importing to MySQL and had never heard of BCP!

I have just needed to do this and Im pleased to post the command line i used.

This is not a 100% fix, You still need to add 2 quotes.
1 before the first field of the first row, and
1 after the last field of the last row.

This is fine for windows xp, but may need escaping differently for other systems.

Regards,
James Tandy
TandyUK Servers