将数据从SQL Server Express导出到CSV（需要引用和转义） [英] Exporting data from SQL Server Express to CSV (need quoting and escaping)

问题描述

我花了2天时间尝试从SQL服务器安装中导出包含大文本字段的用户输入数据的75000行表。此数据包含每个plain ascii字符，制表符和换行符。我需要导出CSV，其中每个字段都引用，并且引用的列中的引号正确转义（）。

这里是我试过的：
- 从Management Studio右键单击数据库并导出到Excel：失败，因为字段太长。
- 数据从Management Studio导出到带有文本分隔符和逗号分隔的平面文件 - 完全无用，不会转义字段中的引号使文件完全不明确
- BCP从命令行 - 也不支持引用字段。

我需要使用FasterCSV ruby​​库导入，它不允许引号定界符为非标准ascii字符或多个字符。它也不允许在无引号列中使用\\\
或\r。

任何帮助都非常感谢。

在您要保存的查询窗口中，转到查询 - >查询选项...

保存.csv结果时包含列表分隔符的字符串。

然后

香蕉，cookie'作为col1,1324作为col2，'一个两个三'作为col3，'a，b，c'，d'作为col4 
  
 
 
会输出
  col1，col2，col3，col4 
 ，香蕉，cookie，1324，一两三，a，b，cd
  
 
 $ b b 
这是我们都想要的。
 
I've spent 2 days trying to export a 75,000 row table containing a large text field of user input data from a SQL server installation.  This data contains every plain ascii character, tabs, and newlines.  I need to export CSV where every field is quoted, and quotes within the quoted columns are properly escaped ("").

Here is what I've tried so far:
- Right clicking on the database from Management Studio and exporting to Excel: fails due to the field being too long.
- Data Export from Management Studio to flat file with " text separator and comma separation - completely useless, does not escape quotes within a field making the file completely ambiguous.
- BCP from command line - also does not support quoting fields.

I need to import with the FasterCSV ruby library.  It does not allow the quote delimiter to be a non-standard ascii character or more than one character.  It also does not allow \n or \r in unquoted columns.

Any help is greatly appreciated.
解决方案
It can be done! However you have to specifically configure SSMS to use quoted output, because for some daft reason it is not the default.

In the query window you want to save go to Query -> Query Options...

Check the box "quote strings containing list separators when saving .csv results".



then
select 'apple,banana,cookie' as col1,1324 as col2,'one two three' as col3,'a,b,"c",d' as col4
will output
col1,col2,col3,col4
"apple,banana,cookie",1324,one two three,"a,b,""c"",d"
which is what we all want.

                        
这篇关于将数据从SQL Server Express导出到CSV（需要引用和转义）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！