Question

I need to export some data using SQL Server 2000 s BCP utility. Sometimes my data contains characters, such as and , 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 1

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 and character with their escaped equivalents (\n and \t). Finally replace your line terminator with 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.

Answer 2

You can use a separator made up of multiple characters if you put them between double quotes:

bcp MY_TABLE out C:MY_FILE.txt -S SERVER_IP -d DB_NAME -U MY_USER -P MY_PASSWORD -w -t "&#)^@" -r ">~+!"

Found the solution here.

Answer 3

You can use the native format, which will export in a binary database format that handles control characters. Just add the -n switch instead of -c or -w:

bcp MyTable out C: empMyTable.tsv -S SERVER -d Database -U USER -n

Use native format to import or export data (SQL Server)

Answer 4

I have the same problem and searched a long time to find a solution. I found this one from a BCP master and it sounds reasonable. Perhaps you want to try it as well.

Possible solution: http://groups.google.co.uk/group/microsoft.public.sqlserver.tools/tree/browse_frm/thread/f1ee12cba3079189/ef9094123901fe26?rnum=1&q=lindawie+format+file&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.tools%2Fbrowse_frm%2Fthread%2Ff1ee12cba3079189%2Fef9094123901fe26%3Ftvc%3D1%26q%3Dlindawie%2Bformat%2Bfile%26#doc_fa5708ca51d967a6

Format file details & design: http://msdn.microsoft.com/en-us/library/aa173859%28SQL.80%29.aspx

Generally I can suggest these links to get you know about BCP problems and solutions: http://groups.google.co.uk/groups?q=lindawie+format+file

Best regards

友情链接