我需要使用 SQL Server 2000 的 BCP 实用程序导出一些数据。有时我的数据包含字符，例如 \t 和 \n，我需要将它们用作列和行终止符。如何让 BCP 在输出数据时转义用作终止符的字符，以便我可以将数据实际导入到另一个程序中？
例如，我的一列是文本数据，包括制表符和换行符。 BCP 只是按原样导出它们，而我尝试导入它们的程序会感到困惑，因为数据在一行中间结束和/或一行无明显原因包含额外的列。
这似乎是数据导出器中包含的非常非常基本的功能，但没有一个命令行选项似乎提到了它。 （为什么它不只是默认值超出我的范围。）我错过了什么吗？
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?
发布评论
评论（5）
完全同意你的观点：逃避应该是一种选择。 “你不能用制表符或换行符获取数据”是我听过的最愚蠢的事情。
这是一个可能的解决方案：
不太可能出现在您的数据中 (#!#$#%#)。我认为你可以使用多个
字符，这样就更容易了。
请查看本文以获取更多信息。
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:
unlikely to be present in your data (#!#$#%#). I think you can use multiple
characters, so that makes it easier.
Take a look at this article for more information.
如果将多个字符放在双引号之间，则可以使用由多个字符组成的分隔符：
bcp MY_TABLE out C:\MY_FILE.txt -S SERVER_IP -d DB_NAME -U MY_USER -P MY_PASSWORD -w -t "&# )^@" -r ">~+!"找到解决方案此处。
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.
您可以使用本机格式，它将以处理控制字符的二进制数据库格式导出。只需添加
-n开关而不是-c或-w：bcp MyTable out C:\temp\MyTable.tsv - S SERVER -d 数据库 -U USER -n使用本机格式导入或导出数据（SQL Server）
You can use the native format, which will export in a binary database format that handles control characters. Just add the
-nswitch instead of-cor-w:bcp MyTable out C:\temp\MyTable.tsv -S SERVER -d Database -U USER -nUse native format to import or export data (SQL Server)
我也有同样的问题，找了好久才找到解决办法。
我从一位 BCP 大师那里找到了这个，听起来很有道理。
也许您也想尝试一下。
可能的解决方案： 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
格式化文件详细信息&设计：
http://msdn.microsoft.com/en-us /library/aa173859%28SQL.80%29.aspx
一般来说，我可以建议这些链接来让您了解 BCP 问题和解决方案：
http://groups.google.co.uk/groups?q=lindawie +格式+文件
最好的问候
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
您不能拥有包含制表符和换行符以及制表符和换行符分隔符的数据。这毫无意义。转义没有帮助，因为选项卡就是选项卡。我们这里讨论的不是 C# 字符串处理。
我要做的是使用 不同的终止符例如
|和||/n，或使用 格式文件You can't have data containing tabs and newlines with tabs and newline separators. It makes no sense. Escaping wouldn't help, because a tab is a tab. We're not talking c# string handling here.
What I'd do is use different terminators such as
|and||/n, or use a format file