SQL Server BCP导出SQL字段中的逗号 [英] SQL Server BCP Export where comma in SQL field
问题描述
我已成功将文件导出到CSV.我使用以下代码导出文件:
I have successfully exported a file to a CSV. I used the following code to export the file:
exec xp_cmdshell 'bcp "[DC_TrainEnvironment].[dbo].[HAFacilities_Master]" out "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master.csv" -f "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master_FORMAT.fmt" -o "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master_LOG.txt" -T -S "HAPDBCDC02,2112"'
这很好，但是有问题.在某些字段中包含逗号.我正在使用以下格式文件:
This works well, but there is a problem. There are certain fields that have commas within them. I am using this format file:
在格式文件中我该怎么做，以避免必须更改整个格式文件?有没有一种快速的方法来指示字段终止符在某些字段中不会以逗号结尾? IE. '，'"或者 "''，''"?
What can I do in the format file to avoid having to change the entire format file? Is there a quick way to indicate a field terminator will not end with the comma in those certain fields? I.e. "','" or "'',''"?
感谢任何帮助.谢谢
推荐答案
如果要导出的字段可以包含用于分隔文件中字段的字符，则可以选择:
If a field you are exporting will can contain the character you are using to delimit the fields in your file, your choices are:
-
使用其他定界符-这似乎是最简单的方法.只需使用"|"或〜"作为分隔符.将，"全局替换为"|"与几乎所有文本编辑器一起使用的格式文件中.目前尚不清楚为什么很难修改整个"文件.也许您有一个需要用逗号分隔文件的收件人?
Use a different delimiter - this seems the easiest way go to. Just use "|" or "~" as your delimiter. Do a global replace of "," to "|" in the format file with just about any text editor. It's not clear why it would be difficult to modify "the whole" file. Maybe you have a recipient of the file who requires comma-delimited?
如果必须使用逗号作为分隔符，则必须将列分隔符从逗号(，)更改为quote-comma-quote(，").为此，您需要使用转义字符来使BCP程序忽略要在输出文件中用作定界符的引号，因此它不会将其视为它理解为在格式文件中包含定界符的引号. .所以...
If you must use commas as the delimiter, you must change your column delimiter from comma (,) to quote-comma-quote (","). To do this, you need to use the escape character to get the BCP program to ignore the quotes you want to use as delimiters in the output file so it does not think of them as the quotes it understands to contain the delimiter in the format file. So...
代替...，使用..."\"，\"
Instead of ","... use... "\",\""
这将导致以下结果
col1，col2，"col，3"，col4
col1,col2,"col,3",col4
对于col1，分隔符为:，以格式文件表示该分隔符，请使用:，"
For col1 the delimiter is: , to represent this in format file use: ","
对于col2，定界符为:，"以格式文件表示，请使用:，\""
For col2 the delimiter is: ," to represent this in format file use: ",\""
对于col3，分隔符为:，以格式文件表示此分隔符，请使用:"\"，"
For col3 the delimiter is: ", to represent this in format file use: "\","
对于col4，分隔符为:，以格式文件表示此分隔符，请使用:，"
For col4 the delimiter is: , to represent this in format file use: ","
我希望能帮上忙.
这篇关于SQL Server BCP导出SQL字段中的逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
