时间戳

首页 > 解决方案 > 使用字段中的引号将 CSV 导入 SQL Server 的简单方法

sql-server - 使用字段中的引号将 CSV 导入 SQL Server 的简单方法

问题描述

我正在尝试将包含字段中的逗号和引号的 CSV 文件导入 SQL Server 数据库。网上有大约一百万个关于它的问题和主题，但没有一个真正有效。我开始明白，当谈到 CSV 时，标准略有不同，但 SSMS 似乎也无法导入，我觉得确实应该有一种方便的方法。

这些文件包含自由文本字符串，它们在字段中使用双引号和逗号。

这是我正在使用的测试 CSV 文件：

"Value 1","Notes"
""8-pooln" grupp 7:6 To11:13","As extracted"
"""8-pooln"" grupp 7:6 To11:13","With escaped quotes"
"""""""""""8-pooln"""""""""""""""" grupp 7:6 To11:13","With loads of quotes"

我使用第 3 方程序将数据提取到 CSV。所以第一条记录是我是如何从那个程序中得到它的。根据某些站点，您需要通过添加另一个双引号来转义字段中的双引号，这就是您在记录 2 中看到的内容。最后一个包含很多用于测试的内容。我还使用另一个应用程序将文件验证为 CSV，其中第二条和第三条记录通过。

通过使用 SSMS 导入向导，我得到：

_Value_1_,_Notes_
8-pooln" grupp 7:6 To11:13,As extracted
8-pooln"" grupp 7:6 To11:13,With escaped quotes
8-pooln"""""""""""""""" grupp 7:6 To11:13,With loads of quotes

所以开头的双引号总是被忽略，不管它们有多少。我还没有找到任何可以改变这一点的设置。

我还尝试手动编写 SQL 命令，例如：

BULK INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
    (FIELDTERMINATOR = ',',
     ROWTERMINATOR = '\n')

这给了我们：

Value_1,Notes
"Value 1","Notes"
""8-pooln" grupp 7:6 To11:13","As extracted"
"""8-pooln"" grupp 7:6 To11:13","With escaped quotes"
"""""""""""8-pooln"""""""""""""""" grupp 7:6 To11:13","With loads of quotes"

它只将逗号和换行符识别为任何类型的控制字符，并且似乎没有任何额外的行可以添加来修复它。

最后，我找到了一些解决方案，您可以在其中编写“格式文件”，基本上可以手动为每列定义列分隔符。这可能会起作用，但我有一个文件和大约 20 个文件的 50 多列。

我还在 SSMS 导入向导的设置中找到了一个可能的解决方案，但它是针对旧版本的，看起来它不再存在。

澄清：

在这一点上，我很高兴了解它为什么不起作用或为什么我的问题似乎是独一无二的。

标签： sql-servercsvssms

解决方案

我不确定使用 SSIS 是否适合您，但如果是这样，在文本字段中导入带引号的数据将相当容易。该过程的概述如下。

  • 创建到目标表所在的 SQL Server 实例的 OLE DB 连接。这可以通过在 Connections Managers 窗口中右键单击，选择New Connection...然后选择 OLE DB 选项来完成。为数据将加载到的位置配置登录凭据和初始目录。
  • 接下来创建一个平面文件连接管理器。对于文件名字段，导航到现有文件夹并选择示例数据文件。如果您没有看到该文件，请在文件资源管理器中更改为所有文件的文件扩展名。选择Delimited格式字段并选中“第一个数据行中的列名”选项，如果这适用于您的文件。适当设置标题行分隔符。从你的例子来看，我猜你会使用回车/行字段组合，这是{CR}{LF}值。

  • 在 Columns 窗格上，相应地设置行分隔符，这似乎也{CR}{LF}来自您的示例。对于列分隔符，使用,. 这将适用于文件中的所有列，因此您无需为每个字段设置此项。我不能完全通过您的问题来判断，但是如果 a,分隔所有字段，则使用此选项，否则键入Mixed列分隔符。此选项可能不会出现在下拉列表中，但绑定它可以让您为每列使用不同的分隔符。更多关于这在下面的项目中

  • 在“高级”窗格中，为列添加名称、数据类型和长度。如果您不确定哪些 SSIS 数据类型对应于 SQL Server 数据类型，请参阅此链接中的映射表，其中显示了哪些数据类型相互关联。如果您使用上述选项，您可以在此处为每列Mixed设置字段中的分隔符。ColumnDelimiter您也可以在此处输入值。例如，如果字段将始终由某种字符组合分隔，则也可以使用此方法。

  • 创建连接管理器后，创建一个数据流任务并在其中添加一个平面文件源组件。使用您刚刚为该组件的连接管理器创建的连接管理器。

  • 接下来添加一个 OLE DB 或 SQL Server 目标。我发现 SQL Server 目标往往性能更好，但当然这可能因不同环境而异。使用为目标 SQL Server 实例创建的 OLE DB 连接管理器并映射“映射”窗格上的列。将平面文件源连接到 SQL Server 目标，您现在可以将数据从源文件加载到表中。

  • 如果这是您将定期执行的操作，请考虑将其设置为 SQL 代理作业。您可以在此处找到有关此过程的更多详细信息。

推荐阅读