如何将 CSV 文件导入 MS SQL Server
4.93/5 (18投票s)
如何将 CSV 和 XML 数据导入 MS SQL Server。
引言
我之前写的一篇关于 '开发人员的 DBA 技能' 的文章，总结了各种技巧，希望能够让那些“接管”了公司内部 DBA 职责的开发人员的生活变得好过一些。我原本打算扩展那篇文章，并添加关于导入数据到 SQL Server 的信息，但考虑到这是一个会随着时间推移而扩展的话题和文章，所以我决定将其单独列出——这就是本文。
将数据导入 SQL Server
当您负责的、由那些可爱的支付您薪水的人设计的、神奇而又出色的数据库运行良好，并通过简单的应用程序内 CRUD 生成自己的数据，或者从 Web 服务和其他很棒的来源获取数据时，总有一天您会遇到一种情况：需要将一大堆以 CSV 或 XML 格式呈现的平面文件数据导入进去，并被告知——请导入这些数据…… #唉
我发现，通常最好使用由精心设计并维护 MS SQL Server 的聪明人提供的工具。因此，在遇到平面文件数据时，我倾向于根据数据的类型和格式选择三种导入方法。
(1) 大批量 CSV 数据加载器
Bulk Insert 是一个 Transact-SQL 语句，允许您从 SQL 命令行调用和导入数据。我通常在手动调用或按计划触发的存储过程中使用它。
以下是一个示例……假设我们已经准备好了如下表：
create table SomeImportTestTable
(ID int,
PersonsName varchar(50),
Country varchar(100))
还有一个名为 'c:\import\testdata.csv' 的文件，其中包含类似以下的数据：
1, AJSONDEV, United Kingdom
2, Mahabir, India
3, Joanna, Netherlands
为了将这些数据导入到我们的表中，我们调用批量导入，如下所示……
bulk insert SomeImportTestTable
from 'c:\import\testdata.csv'
with
(Fieldterminator = ','
RowTerminator = '\n')
它将按预期导入到我们的表中。
ID PersonsName Country ---------------------------------------------- 1 AJSONDEV United Kingdom 2 Mahabir India 3 Joanna Netherlands
您会注意到上面有选项可以指定字段之间的分隔符、行终止符等。在此处了解更多关于 Bulk CSV 加载器的信息：https://msdn.microsoft.com/en-GB/library/ms188365.aspx
(2) BCP 数据导入实用程序
有时，您可能因为某种原因无法在数据库内部构建和运行脚本——在这种情况下，您可以调用批量导入的最佳搭档，即 BCP 实用程序。这个小工具是一个命令行实用程序，它基本上与批量导入语句做的事情相同（我读到过它会调用批量导入实用程序，但我尚未证实这一点）。BCP 不仅可以被那些懂得 SQL“黑暗语言”的人使用，几乎任何人只要能拼凑出命令行脚本都可以使用——这是因为它非常依赖命令行/开关驱动。
将 CSV 文件中的数据导入数据库非常简单……
bcp MyDatabaseName.SomeImportTestTable in c:\import\testdata.csv -t, -U MyLoginName
很简单——调用实用程序名称，后跟您希望导入的数据库/表名，关键字“IN”（表示方向！），“-t”告诉实用程序它是制表符分隔的，而“-U”表示“下一个令牌是我的登录用户名”。单击 <Enter> 后，实用程序将询问您的密码然后执行。
除了使用 BCP 实用程序将数据导入数据库外，您还可以使用它来导出数据。此 MSDN 示例说明了这一点。
bcp AdventureWorks2012.Sales.Currency out "Currency Types.dat" -T -c
在此链接中了解更多关于 BCP 实用程序的信息：https://msdn.microsoft.com/en-gb/library/ms162802.aspx
注意：导入 CSV 数据时的注意事项
您知道那种感觉吗？当您学习一项新技能或工具，处于蜜月期，一切都那么美好，您迫不及待地想起床闻玫瑰的芬芳，哇，它们真是太美妙了……<深呼吸！！>……直到一切都完蛋了！！！！ #天哪……好吧——以下是一些关于导入 CSV 风格数据到 SQL 的惨痛教训……
(a) 注意分隔符的完整性……也就是说，确保需要用逗号括起来的每个对象都已括起来，并且每个需要用逗号分隔的记录对象都被分隔开……还有（！！！！！！）……查找任何*破坏*格式的数据……我指的是一行使用了单引号来隔离数据标记，而这些数据标记实际上被包含相同分隔符的用户数据所破坏。
示例 - 有效： '1','AJSONDEV','United Kingdom'
示例 - 无效： '1','AJSON O'DEV','United Kingdom'
在上面无效的示例中，请注意 AJSON 已经变得有点像爱尔兰人了，现在变成了 Ajson O'Dev（begorrah!）……不幸的是，分隔“O”和“Dev”的单引号也是分隔符，因此导入会失败。
(b) 注意导入的数据质量，并留意任何看起来很奇怪的字符（例如，非预期的、非标准的 ASCII 字符），这些字符可能会破坏导入。
那么该怎么办呢？……我通常采用两种方法之一，具体取决于我在项目进展过程中对导入失败的严重程度的看法。有些数据天生就很干净，而其他数据则非常混乱。第一种方法通常是使用允许导入的字符白名单，并预先解析 CSV 文件以强制执行列表（例如：只允许 'A..z'、'0..9'）。第二种方法稍微麻烦一些，比较粗暴，就是获取将要导入的每个令牌，对其进行编码（我有时会使用 base64），然后导入，之后再解码。然而，后者允许任何杂乱的字符被传输，并且一旦设置正确，就能给我带来最少的麻烦。
(3) XML 数据导入
SQL Server 在处理 XML 数据方面提供了很大的灵活性，但有时您需要费一番周折才能实现。如果您有一个 XML 文件需要导入，并且数据被整齐地组织在易于访问的节点中，那么您就很幸运了——事情会非常顺利。另一方面，如果您有大量的子节点、许多深层树结构等，那么您就需要坐下来仔细研究才能获得所需的内容。您可以选择从磁盘加载整个 XML 文件，并将其存储在具有 XML 类型的一个特殊表列中，或者加载一个 XML 文件，查询其内容，并将这些内容提取出来插入到标准的表中，以便进行常规的纯 SQL 操作。
示例 1 - 简单导入到 XML 类型字段。
假设我们有一个具有 XML 类型列的表，那么我们可以使用以下 SQL 命令将文件 'testdata.txt' 导入到该列中。
Insert into MyTestImportTable(XmlColumn)
select * from OpenRowSet( bulk 'c:\import\testdata.txt', single_blob ) as X;
（……哦天哪，看看，又是我们熟悉的好朋友“BULK”在为我们辛勤工作……多酷啊！）
请注意，执行后的数据现在是以 XML 格式存在的，存储在单个列中，您仍然需要工作来提取数据并使用它。
示例 2 - 导入到 VarChar/String 变量
有时我们需要将 XML 作为文本进行检查，可能是在将其作为 XML 进行处理之前进行一些预处理。此示例显示了如何将文件加载到 VarChar 变量中。
Select @fileData=BulkColumn
from OpenRowSet(Bulk 'C:\import\testdata.xml', Single_blob ) x;
现在可以使用变量 @fileData，以字符串形式，根据需要进行处理。要将其转换回 XML 对象，只需转换即可。
示例 3 - 导入 XML 数据并将单个值插入表行列
我们的输入 XML 示例将如下所示：
1 AjsonDev United Kingdom 2 Mahabir India 3 Joanna Netherlands
以下 SQL 将打开文件，并将数据读取到各个记录列中。
-- setup a variable to take the file data
Declare @fileData XML
-- import the file contents into the variable
Select @fileDataX=BulkColumn from OpenRowSet(Bulk'C:\data\hpra\latestVMlistX.xml',Single_blob) x;
-- insert the xml data into our test table (ID, PersonsName, Country)
insert into SomeImportTestTable
(ID, PersonsName, Country)
select
xData.value('ID[1]','int') ID, -- 'xData' is our xml content alias
xData.value('PersonsName[1]','varChar(50)') PersonsName,
xData.value('Country[1]','varChar(100)') Country,
from @fileData.nodes('/Persons/Person') -- this is the xpath to the individual records we want to extract
示例 4 - 提取嵌套的 XML 子数据并将其展平
在这个例子中，我们将要看一个包含嵌套子节点的输入 XML 文件——我们先来看一下：
1
AjsonDev
United Kingdom
C#
JavaScript
Python
Objective-C
Pascal
F#
R
2
Mahabir
India
C#
JavaScript
C++
VB
SQL
3
Joanna
Netherlands
Dutch
我们想要实现的是提取核心数据（姓名、国家），但不是构建另一个表（1:M）来存储语言，也不是将它们放入表中的 XML 字段，我们决定将语言展平，通过逗号分隔到一个字段中。和开发中的大多数事情一样，有很多方法可以处理这个问题。在本例中，我展示了如何使用 xpath 导航首先深入提取所有子节点，然后使用“..”表示法向上遍历 XML 树，并选取我们需要的父数据。
insert into SomeImportTestTable
(ID, PersonsName, Country, Languages)
SELECT
xData.value('../ID[1]','int') ID,
xData.value('../PersonsName[1]','VARCHAR(50)') PersonsName,
xData.value('../Country[1]','varchar(100)') Country,
xData.value('(.)[1]', 'varchar(1000)')
FROM @fileDataX.nodes('./Persons/Person/Languages') as
x(xData)
提取数据后，它将像往常一样进入 ID、Person 和 Country 字段，但是，“Languages”数据将包含每种语言，并用换行符分隔。在这种情况下，为了区分数据，我可能会选择用逗号分隔这些令牌。
注意：XML 的注意事项！ XML 可以非常简单，也可以像您想的那么复杂。有时，在使用此处描述的方法加载 XML 数据时会出现问题。最近，我遇到了一个命名空间问题，当存在命名空间时，数据无法加载，移除后则可以加载。它似乎需要被正确引用。如果您遇到此类问题，有两种选择：
(a) 您可以显式引用 xml，例如，使用 XMLNameSpaces，但是，对我来说，这不起作用，所以我决定只是删除链接，因为我不需要它。
(b) 我快速/粗糙的解决方案是将 XML 加载为 VarChar，然后删除 NameSpace 字符串。完成之后，我将 varchar 转换回 XML 类型，然后继续按要求工作。
导入/导出向导
即使在最低版本的 MS SQL Server 中，也有一些 GUI 辅助功能用于数据导入，您可以避免编写代码（为什么？！ :P）……但正如您所料，也有一些限制，最主要的是您无法保存您的 ETL 计划。尽管如此，当您需要匆忙完成某项任务时，此工具非常有用。
引用在 SQL Server Express、Web 或 Workgroup 版本中，您可以运行导入和导出向导创建的包，但无法保存它。要保存向导创建的包，您必须升级到 SQL Server Standard、Enterprise、Developer 或 Evaluation 版本。
使用 SQL Express，从“所有程序”菜单中，选择“SQL Server 导入和导出向导”——它布局清晰，并且易于理解，因此选择数据源，如有必要，编辑与解析和映射传入数据相关的任何参数，选择数据目标，然后让它开始！
(1) 启动向导
(2) 选择数据源
(3) 指定数据源类型
(4) 编辑任何导入参数以及源和目标之间的映射
(5) 确认源/目标
(6) 运行导入！
高级 BCP
在本文前面，我们简要讨论了使用 BCP 实用程序导入和导出数据。然而，它还有更多的功能，如果您开始大量使用该实用程序，值得深入了解一下，看看它还能如何帮助您。以下是我最常使用的几个开关：
- -a packet_size
-
这告诉实用程序在与服务器交换数据时使用的（每个网络数据包传输）字节数。默认值为 4096，但我发现对于大型数据集，增加此大小可以提高导入例程的性能。
- -b batch_size
-
您可以使用此标志传递一次（一个批次）要处理的导入行总数。当您处理可能存在问题的数据时，这*极其*有用，导入会不断中断，您需要 (a) 追踪问题并 (b) 隔离问题，以便其余数据可以继续流动。此开关确保即使一个批次失败，其余过程也将继续——此外，由于事务而执行的任何操作都将被回滚。
- -e err_file
-
我始终与上面的 **-b** 结合使用它。它为实用程序提供了一个位置，我希望它在此处写入遇到的任何错误的日志文件，它将输出任何给出错误且未导入的行。大忌！……如果您在使用批处理大小选项时*不*包含此项，那么将不会创建日志文件，并且在出现任何问题时，您也不知道哪些数据被遗漏了！
- -F first_row
-
这个很有用，可以与 **-L**（下面的“last row”）结合使用——实际上，将它们一起使用类似于我们在处理分页场景时使用的“skip/take”模式。此开关告诉实用程序从哪里开始导入数据的第一行。
- -L last_row
-
与 first_row 相反，它指示要导入的最后一行编号。同时使用 -F 和 -L，允许您在大型导入文件的中间提取一部分数据，而忽略其余部分。
- -m max_errors
-
显然，如果文件充满了错误，那么处理它就没有意义了，您可能会决定在放弃之前只能接受一定数量或百分比的错误。**-m** 允许您指定此数量。当某一行数据由于任何原因无法导入时，就会发生故障。
- -t field_term
-
根据您的数据来源，每行的字段数据可能由制表符、逗号、管道符等分隔。此开关允许您告诉 BCP 分隔符是什么。默认是 **\t**（制表符字符）。
选择性导出
有时我只需要从表中获取部分数据，而不是导出全部内容。为了帮助实现这一点，我们可以使用“queryout”开关。
示例
bcp "SELECT Name FROM SomeTable" queryout MyExportedFile.txt -T
bcp "SELECT * FROM Country WHERE PopulationMillion > 10 AND ExportKGMonth > 170 " queryout "CountryExports.dat" -T
云中的数据导入/导出
随着我们将更多数据和服务迁移到云端，我们面临着与桌面和传统托管服务器不同的做事方式。然而，无论平台如何，导入/导出 csv/xml/等格式数据的需求依然存在。有时，如何操作可能并不明显，您需要根据情况想出各种变通方法。以下是我在 Azure 和其他云托管环境中协助数据导入/导出的方法。
设置外部驱动器。
我使用的一种解决方案是创建一个外部于我的 SQL VM 的虚拟驱动器，并将其用作数据的暂存点。我可以直接与 SQL 交互以处理驱动器上的数据，或者将其作为映射驱动器连接到 SQL VM 并用于自动作业，如备份。我在这里有一篇关于使用此方法进行 Azure SQL 虚拟机数据计划备份的文章：https://codeproject.org.cn/Articles/668626/Scheduled-backup-of-Azure-SQL-Virtual-Machine-data
远程访问您的驱动器
有各种工具可用于连接到云存储——我主要使用的是 Cloudberry Labs 的 Azure Cloudberry Explorer。（注意：也有 EC2 等版本的）。免费版本功能非常强大，足以满足您进行基本导入/导出的绝大多数需求。如果您需要更强大的功能，那么他们的专业版本在功能方面是一个巨大的提升，而且对于其拥有的功能以及重要的是它为您节省的时间来说，价格非常合理。
（上图来自 Cloudberry 的截图，左侧显示本地文件浏览器，右侧显示远程虚拟驱动器）。
其他方法
还有许多其他加载数据到 SQL Server 的方法——我将在本文的后续更新中介绍它们。其中要考虑的有 SSIS，它可以让您并行加载数据块，这在处理大量数据时总是一件好事。
以下是一些值得查看的关于 SSIS 的有趣资源：
我附上了 SQL 脚本和测试 XML 数据来演示代码工作情况——您可以随意下载并尝试。最后，如果您喜欢这篇文章，请在页面顶部给它投一票！
历史
2016 年 1 月 21 日 - 版本 1
2016 年 2 月 10 日 - 版本 2 - 添加了导入/导出向导
2016 年 2 月 17 日 - 版本 3 - 添加了 Azure/云部分，添加了高级 BCP 导入/导出部分
2016 年 4 月 12 日 - 版本 4 - 更新了代码，添加了示例 SQL 下载脚本