BCP escape character?

[ScrewBalll]

Hi there,
I'm in need of some help. Does anyone know of a way to implement escape characters in a BCP load?? I have a comma delimited file which I can usually put into my DB fine, but when a ',' pops up in an field it causes the whole thing to fall over (truncation error on load). I've managed to prefix the unwanted commas with a '\' escape char but I'm unsure how I can implement this in the BCP load utility.

Anyone got any ideas?

 

[micha123]

I think there is no solution for that, since bcp is not an "intelligent" tool.
What I've done in situations like that is searching another field delimiter that I am sure would not appear in my columns, examples: @@ $%$
Or you can use fixed-length files.

When I had to design a data conversion system (converting old data from diverse old systems to a new SQL SERVER one), what I did is as following:

1. bcp the ASCII files into a table with only one field, defined as a large character (TAB1).
2. insert into TAB2 ... select substring(..),... from TAB1
into a table with the disired format (TAB2).

This would allow me to do data manipulation as required!

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[ScrewBalll]

Thanks, I ended up changing the delimiter to a '\t', it's a shame there wasn'tan easier way around it.