Getting bcp.exe to escape terminators
I need to export some data using SQL Server 2000's BCP utility. Sometimes my data contains characters, such as \t and \n, that I need to use as column and row terminators. How do I get BCP to escape characters it's using as terminators as it outputs the data, so that I can actually import the data in another program?
For example, one of my columns is text data, and includes tabs and newlines. BCP just exports them as-is, and the program I'm trying to import them with gets confused because the data ends in the middle of a line and/or a line contains extra columns for no apparent reason.
This seems like a very, very, very basic function to include in a data exporter, but none of the command-line options seem to mention it. (Why it wouldn't just be the default is beyond me.) Am I missing something?
Answer
Totally agree with you: escaping should be an option. "You can't have data with tabs or newlines" is the silliest thing I have ever heard.
Here is a possible solution:
- use the -r option to set a different line terminator. Something
unlikely to be present in your data (#!#$#%#). I think you can use multiple
characters, so that makes it easier. - Open your data file in sed, a capable text editor, or write a script - and replace any \n and \t character with their escaped equivalents (\\n and \\t). Finally replace your line terminator with \n and you should be good.
- I think the same thing should apply to using -t for field terminators
Take a look at this article for more information.