将数据从 SQL Server Express 导出到 CSV（需要引用和转义）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7382846/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting data from SQL Server Express to CSV (need quoting and escaping)
提问by Xac Stegner
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 ("").
我花了 2 天时间试图从 SQL 服务器安装中导出包含用户输入数据的大文本字段的 75,000 行表。此数据包含每个纯 ascii 字符、制表符和换行符。我需要导出 CSV，其中每个字段都被引用，并且引用列中的引号被正确转义 ("")。
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.
以下是我到目前为止所尝试的： - 在 Management Studio 中右键单击数据库并导出到 Excel：由于字段太长而失败。- 数据从 Management Studio 导出到带有 " 文本分隔符和逗号分隔的平面文件 - 完全没用，不会转义字段内的引号，使文件完全不明确。 - 命令行中的 BCP - 也不支持引用字段。
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.
我需要使用 FasterCSV ruby 库导入。它不允许引号分隔符是非标准的 ascii 字符或多个字符。它也不允许在未加引号的列中出现 \n 或 \r。
Any help is greatly appreciated.
任何帮助是极大的赞赏。
回答by Robert Calhoun
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.
可以办到！但是，您必须专门配置 SSMS 以使用带引号的输出，因为出于某种愚蠢的原因，它不是默认设置。
In the query window you want to savego to Query -> Query Options...
在要保存的查询窗口中，转到查询 -> 查询选项...
Check the box "quote strings containing list separators when saving .csv results".
选中“保存 .csv 结果时引用包含列表分隔符的字符串”框。
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.
这是我们都想要的。
回答by ckpepper02
I've been trying to figure this out as well. Not sure if this will work for you since your table is much larger than mine, but this is what I did just out of a whim:
我也一直在想办法解决这个问题。不确定这是否适合你，因为你的桌子比我的大得多，但这是我一时兴起做的：
- I pulled up my table in Express by doing a
SELECT *statement - Simply selected the resulting rows and Ctrl+ C
- Opened Excel
- Highlighted the amount of columns the table I was pasting had
- Pasted, and it friggin' worked!
- Now just have to Export Excel as CSV and done.
- 我通过做一个
SELECT *声明在 Express 中拉起了我的桌子 - 只需选择结果行和Ctrl+C
- 打开 Excel
- 突出显示我正在粘贴的表格的列数
- 粘贴，它该死的工作！
- 现在只需将 Excel 导出为 CSV 即可。
I know it probably sounds stupid, but it actually worked for me.
我知道这听起来可能很愚蠢，但它实际上对我有用。
回答by Chris Christodoulou
The easiest way to do this:
最简单的方法：
Use the Excel Data Import tools
使用 Excel 数据导入工具
- Go to Data > From Other Sources > From Sql Server
- Fill in the Server name etc.
- Select the table or view that you want to import.
- 转到数据 > 从其他来源 > 从 Sql Server
- 填写服务器名称等。
- 选择要导入的表或视图。
Then Save the imported data to a CSV file. If you want to export a query then save your query as a view
然后将导入的数据保存为 CSV 文件。如果要导出查询，则将查询另存为视图
回答by Unixmonkey
Here's the essence of a script I use to do just this:
这是我用来执行此操作的脚本的本质：
require 'rubygems'
require 'active_record'
require 'tiny_tds'
require 'activerecord-sqlserver-adapter'
require 'acts_as_reportable'
require 'ruport'
ActiveRecord::Base.logger = Logger.new("log/debug.log")
ActiveRecord::Base.establish_connection(
:adapter => 'sqlserver',
:mode => 'dblib',
:dataserver => 'servername',
:username => 'username',
:password => 'password',
:timeout => '60000'
)
class Table1 < ActiveRecord::Base
set_table_name 'table_name'
set_primary_key 'table_id'
acts_as_reportable
end
Table1.report_table(:all).save_as("finished/table1.csv")
Hope it helps!
希望能帮助到你！
回答by Rolf Herbert
The simplest solution Ive found is to add double quotes in your query;
我找到的最简单的解决方案是在查询中添加双引号；
SELECT '"'+MYCHARACTERDATA+'"' FROM MYTABLE
If your spreadsheet allows you to use a custom text qualifier then you can use a more exotic character like | to avoid double quotes in the text.
如果您的电子表格允许您使用自定义文本限定符，那么您可以使用更奇特的字符，例如 | 避免在文本中使用双引号。
Daft..SSMS should export as proper CSV with quoted text fields and properly escaped quotes in those fields.
Daft..SSMS 应该导出为正确的 CSV 文件，其中包含带引号的文本字段并在这些字段中正确转义引号。
回答by nick
I'm curious why no one has suggested using SSIS (SQL Server Integration Services) for this process? All of the wizards and tools for Import/Export from within SSMS (SQL Server Management Studio) are absolutely not intended to be comprehensive (and they certainly are not, and yes, there is a lot that Microsoft should have to answer for with the limitations). But SSIS is a very full-featured ETL tool designed to tackle problems exactlylike this one. Learning curve can be a little steep, but exporting a table to a comma/quote delimited csv file is not particularly hard.
我很好奇为什么没有人建议在这个过程中使用 SSIS（SQL Server 集成服务）？从 SSMS (SQL Server Management Studio) 中导入/导出的所有向导和工具绝对不是全面的（它们当然不是，是的，Microsoft 应该为限制做出很多回答）。但是，SSIS是旨在解决问题的一个非常全功能的ETL工具正是像这样的。学习曲线可能有点陡峭，但将表格导出到逗号/引号分隔的 csv 文件并不是特别困难。
Might need this add on for Visual Studio in order to have a dev environment to create a package: http://www.microsoft.com/en-us/download/details.aspx?id=42313(link is for VS 2013, other links are available for other versions of VS).
可能需要为 Visual Studio 添加这个插件，以便有一个开发环境来创建一个包：http: //www.microsoft.com/en-us/download/details.aspx?id=42313（链接适用于 VS 2013，其他链接可用于其他版本的 VS）。
回答by VictorySaber
Robert Calhoun's solution did not work for me. We had a lot of text with commas and carriage returns / newlines etc. We used the Export functionality with a few changes to Chris Christodoulou's solution above.
Robert Calhoun 的解决方案对我不起作用。我们有很多带有逗号和回车符/换行符等的文本。我们使用了导出功能，并对上面 Chris Christodoulou 的解决方案进行了一些更改。
In SQL Management Studio, right click the database and select Tasks -> Export Data.
在 SQL Management Studio 中，右键单击数据库并选择任务 -> 导出数据。
Then choose SQL Server as the source and Flat File as the destination. Name the file MyFile.csv.
然后选择 SQL Server 作为源和平面文件作为目标。将文件命名为 MyFile.csv。
Set the Text qualifier as "
将文本限定符设置为“
Select 'Write a query to specify the data transfer' and paste in your query. You can leave the next settings as the defaults.
选择“编写查询以指定数据传输”并粘贴到您的查询中。您可以将下一个设置保留为默认值。
With the data exported, open with Excel and save in the Excel format.
导出数据后，用 Excel 打开并保存为 Excel 格式。
回答by doesntknownanything
The best solution I could come up with is to select all the rows and do a copy as to XML.
我能想出的最佳解决方案是选择所有行并复制 XML。
The paste it into a notepad file and save it as a XML file. Then open that XML file from excel and voila! Atleast that how I got my file.
将其粘贴到记事本文件中并将其另存为 XML 文件。然后从 excel 打开那个 XML 文件，瞧！至少我是如何得到我的文件的。
回答by masoud ramezani
回答by Niklas B.
We made a small script to convert SSMS "broken" CSV into proper CSV, find it in this answer:
我们制作了一个小脚本来将 SSMS“损坏的”CSV 转换为正确的 CSV，在这个答案中找到它：
