如何在SQL Server管理工具中以“真正”的CSV格式获得导出输出？

Question

我有一个在SQL Server Management Studio中运行的查询（连接到SQL Server 2005数据库）。我想以CSV格式导出数据。不是那种伪装成CSV格式，只需在每个列之间放置逗号，而是“真正”的CSV格式，其中在字符串周围放置引号。这样您就可以导出具有逗号或引号的数据。

我看到的所有示例都限制了自己的伪CSV格式。我无法弄清楚如何选项来引用字符串。

如果SSMS真的无法完成这个基本的功能，还有其他工具可以轻松完成吗？我不想每次需要数据转储时都编写C#程序。

- Peter Recore

---

因为这让我非常烦恼，所以我写了一个自己的程序，使用适当的CSV编写器：https://github.com/deeja/SQLtoCSV/releases - Dan

这个问题到底为什么还没有得到解决... - Rei Miyasaka

引用符封装现在是自2016年起 SSMS 的默认行为。 - iacob

Answer 1

在SSMS 2012中，有一个选项，在“工具”->“选项”->“查询结果”->“SQL Server”->“网格结果”中，它被称为“在保存.csv结果时引用包含列表分隔符的字符串”。我不知道这个选项存在多长时间了，但我对两件事感到困惑：

1.为什么默认情况下没有打开它

2.为什么它是一个选项而不是CSV导出代码的固有部分

让人难以置信的是，默认行为是具有无法正确导入的CSV导出。我注意到Excel也是这样，我将去看看它是否也有一个选项。

与此同时，感谢我的同事，当我在抱怨CSV导出器完全无用时，指向了我这个奇怪的功能位，并且这是我发现的最好的链接，因此我认为我会把这些知识放在这里，以造福未来的搜索者。

更新：

以下是截图：

- Matthew Walton

---

同样存在于SSMS 2008中。 - Lloyd

注意：SSMS会对包含定界符或限定符的字段进行限定，但不会对包含换行符的字段进行限定。因此在这方面，SSMS生成的CSV文件在技术上是无效的。 - KyleMit

如果有其他人遇到了和我一样的问题：你需要打开一个新的查询编辑器窗口才能使更改生效。在更改行为之前/之后对相同的结果集执行“另存为...”操作对导出的CSV文件没有影响。 - Jason Larke

在SSMS v17中，我发现特定选项已经消失了，但是它似乎已经与其他选项“在复制或保存结果时包含列标题”合并了，因为勾选这个选项对我有所作用。打开新查询窗口仍然需要才能使设置生效。 - Russell Horwood

微软：“让 CSV 成为 SSMS 默认的导出格式。” 同时，微软：“让我们忽略基本的实现细节。” - Owen

Answer 2

我的常规解决方法是将它构建到查询中：

SELECT '"' + REPLACE(CAST(column AS NVARCHAR(4000)), '"', '""') + '"' AS Header, ... FROM ...

你可以将它构建为一个用户自定义函数，这样会更加方便，但是你需要为每个数据类型构建一个单独的函数。

- Joel Coehoorn

---

可能并不是必要的，但我发现只需使用'"' + REPLACE(CAST(column AS VARCHAR), '"', '""') + '"'更容易。这样我就不用担心削弱字段了。 - Rob

今天，我使用了varchar(max)。当我最初写这个时，我刚从一家仍在使用（呃）Sql Server 2000并且只开始研究2005的商店离开。 - Joel Coehoorn

您可能还需要保留列类型，例如如果原始类型为NVARCHAR，则需要保留NVARCHAR类型。 - N. M.

好的观点。varchar => nvarchar 是扩展，不会破坏任何东西，但是nvarchar => varchar 可能会丢失数据。最好的做法是使用与原始列匹配的内容，但由于人们倾向于从堆栈溢出中复制/粘贴示例代码，因此我的答案中的代码可能更适合使用nvarchar（并且现在已进行更改）。 - Joel Coehoorn

由于微软无法执行基本的导出到CSV文件的操作，因此这是最佳解决方案。使用得票最多的解决方案无法正确导出带有换行符的文件。 - neves

REPLACE(REPLACE(tbl.Notes, CHAR(10),''), CHAR(13),'') as Notes的翻译为中文是：将tbl.Notes中的换行符（CHAR(10)）和回车符（CHAR(13)）替换为空字符串，并将结果命名为Notes。 - Ben McIntyre

Answer 3

很遗憾，选项处于混乱状态且不完全可操作。以下至少有效：

1. 从DB上下文菜单中选择"Tasks>Export Data"（在表级别也不起作用）
2. 对于源，请选择"Microsoft OLE DB Provider for SQL Server"
3. 对于目标，选择"Flat File..."，并指定"Format"为分隔符和"text qualifier"为双引号
4. 选择表或查询（我使用的是查询）
5. 完成向导

你应该可以开始了！

- r.sumesh

---

你可能会认为这会起作用，但是不会——包含双引号的列在数据中未得到适当转义处理。在导出向导中，SQL Server 只能处理伪 CSV 格式。 - mattmc3

我或我的客户在文本字段中包含逗号和单引号的数据上没有投诉的理由。正如我已经提到的，我正在使用查询选项，如果您了解脏字段，您可以始终使用quotename将它们包装起来。幸运的是，我没有遇到双引号。我建议使用本地选项，而不是依赖外部解决方案。 - r.sumesh

尽管它不会自动转义双引号，但这个选项仍然是最实用的。使用replace()函数很容易转义双引号。 - Brett Donald

Answer 4

不同组合的这些设置可能会导致输出结果不正确或部分数据。这是因为微软认为修复这些问题并不重要。我只是在解释将结果发送到文件时CSV文件会发生什么。

要获得良好的结果，请执行以下操作：

打开新的查询窗口（新选项卡/会话）...如果不这样做，则下面的配置将丢失并恢复默认值。

编写查询以处理引号内的引号，并将所有字符串数据类型都用引号括起来。还要注意，不同的DBMS和编程语言语法接受不同的转义双引号语法（如果将此输出用作另一个系统的输入）。有些使用\"。有些使用""。 XML使用"。 微软选择忽略此功能的原因可能是他们不想处理争议。

.. 如果新系统的转义序列是""。

SELECT '"' + REPLACE(CAST(column1 AS VARCHAR(MAX)), '"', '""') + '"' FROM table1

如果新系统的转义序列为\"。

SELECT '"' + REPLACE(CAST(column1 AS VARCHAR(MAX)), '"', '\"') + '"' FROM table1

配置：

查询选项 > 结果 > "将列标题包含在复制或保存结果中" 已勾选

查询选项 > 结果 > "保存 .csv 结果时引用包含列表分隔符的字符串" - 已损坏；不要使用！

查询选项 > 结果 > 其他未勾选

查询选项 > 结果 > 文本 > 逗号分隔（右上角设置）

查询选项 > 结果 > 文本 > "将列标题包含在结果集中" 已勾选

查询选项 > 结果 > 文本 > 其他未勾选

查询选项 > 结果 > 文本 > "每个列中显示的最大字符数" - 设置为最大长度，以避免字符串被截断。

查询 > 将结果保存到文件（这是所有3个选项之间的切换）

执行查询（F5）

提示报告文件名

打开文件查看结果

注意：如果您需要定期执行此操作，则最好在 .NET 或 Java 中开发可以为您执行此操作的程序，或者任何您熟悉的语言。否则，您很可能会犯错误。然后，请在定义 SQL Server 导出之前，非常注意导入系统的语法。

- MacGyver

Answer 5

你对通过PowerShell从SSMS导出CSV文件有何感想？本文介绍如何在SSMS中定义一个外部工具，将当前选定的查询发送到一个PowerShell脚本，该脚本将其导出为CSV文件。

- billinkc

---

到目前为止，这似乎是我花费最少的最佳选择。我甚至还没有将其添加到SSMS的外部工具菜单中 - 我只是从命令行运行它。 - Peter Recore

Answer 6

截至2016年，当在查询选项中选择以下选项时，这将是默认行为：

逗号将用于分隔列，并且包含逗号的字段将使用双引号进行封装。

- iacob

Answer 7

在阅读答案（尤其是iacob的答案）并尝试在较新版本的SSMS中使用建议的选项之后，我决定深入研究并编写一份综合概述，介绍这些选项以及它们如何影响导出格式。

将查询结果导出到文件选项

（这是使用SSMS v18.4的情况）

• 未选择任何选项：
  => 数据被导出为“CSV”，但分隔符是分号而不是逗号。
  => 包含分号和/或双引号的值会被正确地包含在双引号中。
  => 双引号会以2个双引号进行正确转义。
• “在复制或保存结果时包括列标题”
  => 正如其名称所示，只需将列标题作为第一行添加即可。
  => 与iacob的答案相反，此选项不会将分隔符更改为逗号，并且不需要将值用双引号括起来并正确转义双引号。
• “在复制或保存时保留CR / LF”
  => 包含换行符的值会被正确地包含在双引号中并且换行符会被保留。

选择这两个选项似乎可以产生最方便使用的CSV格式，即使它使用分号而不是逗号作为分隔符。

另一个问题是将NULL值导出为“NULL”而不是空字段。

结论：

无论选择哪种方法（导出数据任务/将结果导出到CSV）以及选择哪些选项，似乎仍然没有单一的方法能够使SSMS正确地导出具有逗号、分号、双引号以及NULL值支持的CSV文件。

- 7he3rain

---

我正在使用版本18.9.2，它的行为与我描述的一样；这种行为似乎是特定于版本/区域设置的。 - iacob

Answer 8

我认为这里的人们已经提供了正确的解决方案，特别是Matthew Walton的回答关于更改选项，但对于我来说这不可用（可能是因为我的数据库是旧版本）

或者，如果您能够将其导出为管道或逗号分隔符，并且您只想在列周围添加引号，则只需在Powershell中运行此命令。

Import-Csv 'C:\input.txt' -Delimiter '|' |
    Export-Csv 'C:\output.csv' -Delimiter '|' -NoType

input.txt文件

1|A|Test|ABC,PQR

执行命令后。

"1"|"A"|"Test"|"ABC,PQR"

- grepit

---

如果在输入文件input.txt中逗号已经混淆了，那么它也没有意义。 - Tấn Nguyên

Answer 9

通常我使用这种函数：

：

CREATE FUNCTION [dbo].[toExport]
(
    @txt varchar(max)

)
RETURNS varchar(max)
AS
BEGIN
    
    return REPLACE(REPLACE(REPLACE(@txt, ';', ','), CHAR(10), ' '), CHAR(13), ' ');

END

而在中，我把它放在这里： SELECT dbo.toExport( column_name ) AS column_name FROM .... 在SMSS 2012中，只需右键单击网格并保存结果，或复制所有网格（ctrl-A）并使用ctrl-V粘贴到Excel中，这是在例如MS Excel中管理数据最简单的方法，而无需担心列问题。 当然，您必须在“工具 ->选项 ->查询结果 -> Sql Server ->结果显示区域”中单击“引用包含列表分隔符的字符串”，并增加最大检索字符数（如果需要）。

- Maciej Niemir

Answer 10

相反，您可以选择将其导出为制表符分隔格式。

- Brian Wirt