BCP questions

I'm using BCP for the first time and have a problem.
I'm trying to import some client data into SQL Server. The data is in
CSV format. The problem is that the data contains embedded commas, so
BCP cannot correctly parse the commas.
So, my question is:
I need to get the data re-exported, but in what format?
Can BCP handle field delimiters, such as surrounding the data in
quotes?
Should I have them use something other than commas to separate fields?
Or should I ask them to use a fixed-length format?
I'm new to BCP, so I don't know what BCP is capable of doing, or how
easy/hard each way would be.blindsey,
> I'm using BCP for the first time and have a problem.
> I'm trying to import some client data into SQL Server. The data
> is in CSV format. The problem is that the data contains embedded
> commas, so BCP cannot correctly parse the commas.
> So, my question is:
> I need to get the data re-exported, but in what format?
> Can BCP handle field delimiters, such as surrounding the data in
> quotes?
Yes, it can. But you will need a bcp format file to handle the
quotes. Format files are documented in Books Online.
> Should I have them use something other than commas to separate
> fields?
Yes, if you have any say in the matter, by all means have the data
file created in a format that is convenient for you to load.
Tabs are the default field delimiter for bcp, but you can also
specify any other character that does not appear in the data you are
loading. | or # are popular delimiters. You can also use a
deliniting character string of your own choosing, such as [#|], if
you cannot find a single character that does not appear in the data.
> Or should I ask them to use a fixed-length format?
Fixed-length fields require a bcp format file, making them not quite
as convenient to load as delimited files.
> I'm new to BCP, so I don't know what BCP is capable of doing, or
> how easy/hard each way would be.
Bcp is a powerful tool to efficiently load and transform large
amounts of data. I you need to load data from various on a regular
basis, it is worth the time to learn about bcp formt files.
Lindasql