将 Excel 数据导入 SQL Server数据库

原文：https://docs.microsoft.com/zh-cn/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017#bulk-insert

适用于： SQL Server（所有支持的版本）

将 Excel 文件中的数据导入 SQL Server数据库的方法有多种。某些方法允许你在单个步骤中从 Excel 文件直接导入数据，其他方法要求在导入数据前，必须将 Excel 数据先导出为文本 (CSV 文件)。本文总结了常用的方法，并提供有关更为详细的信息的链接。

一、方法列表

可使用以下工具从 Excel 导入数据：

直接从 Excel（仅本地 SQL Server）进行	首先导出到文本（SQL Server 和 SQL 数据库）
SQL Server 导入和导出向导	导入平面文件向导
SQL Server Integration Services (SSIS)	BULK INSERT 语句
OPENROWSET 函数	BCP

如果要从 Excel 工作簿导入多个工作表，通常必须为每个工作表运行一次其中任何工具。

SSIS 或 Azure 数据工厂等复杂工具和服务的完整描述不属于本表的范围。要详细了解感兴趣的解决方案，请单击所提供的链接。

重要

有关连接到 Excel 文件的详细信息，以及从 Excel 文件加载数据或将数据加载到 Excel 文件的限制和已知问题，请参阅使用 SQL Server Integration Services (SSIS) 从 Excel 加载数据或将数据加载到 Excel 中。

如果尚未安装 SQL Server，或已具有 SQL Server 但未安装 SQL Server Management Studio，请参阅下载 SQL Server Management Studio (SSMS)。

二、SQL Server 导入和导出向导（仅本地 SQL Server）

通过单步执行 SQL Server 导入和导出向导各页面，直接从 Excel 文件导入数据。（可选）将设置保存为可以稍后自定义和重用的 SQL Server Integration Services (SSIS) 包。

在 SQL Server Management Studio 中，连接到 SQL Server 数据库引擎的实例。
展开 “数据库” 。
右键单击某个数据库。
指向“任务” 。
单击以下选项之一。

导入数据
导出数据

连接到 Excel 数据源

有关使用向导将 Excel 导数据入 SQL Server 的示例，请参阅从导入和导出向导的这个简单示例入手。

要了解“导入和导出”向导的其他启动方法，请参阅启动 SQL Server 导入和导出向导。

三、 SQL Server Integration Services (SSIS)（仅本地 SQL Server）

如果熟悉 SSIS，并且不想运行 SQL Server 导入和导出向导，请创建在数据流中使用 Excel 源和 SQL Server 目标的 SSIS 包。

有关这些 SSIS 组件的详细信息，请参阅以下主题：

若要开始学习如何生成 SSIS 包，请参阅教程如何创建 ETL 包。

数据流中的组件

四、 OPENROWSET 和链接服务器（仅本地 SQL Server）

重要

在 Azure SQL 数据库中，无法直接从 Excel 导入。必须首先将数据导出到文本 (CSV) 文件。有关示例，请参阅示例。

备注

连接到 Excel 数据源的 ACE 提供程序（前身为 Jet 提供程序）旨在用于交互式客户端用途。如果在 SQL Server 上使用 ACE 提供程序，尤其是在自动进程或并行运行的进程中，可能会发生意外结果。

1、分布式查询

使用 Transact-SQL OPENROWSET 或 OPENDATASOURCE 函数直接从 Excel 文件导入 SQL Server。这种用法称为“分布式查询” 。

重要

在 Azure SQL 数据库中，无法直接从 Excel 导入。必须首先将数据导出到文本 (CSV) 文件。有关示例，请参阅示例。

必须先启用 ad hoc distributed queries 服务器配置选项（如以下示例所示），然后才能运行分布式查询。有关详细信息，请参阅即席分布式查询服务器配置选项。

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

下面的代码示例使用 OPENROWSET，将 Excel Sheet1 工作表中的数据导入新的数据库表。

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]);
GO

下面的示例用途相同，区别在于使用的是 OPENDATASOURCE。

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];
GO

若要将导入的数据追加到现有表，而不是新建表，请使用 INSERT INTO ... SELECT ... FROM ... 语法，而不是上面示例中使用的 SELECT ... INTO ... FROM ... 语法。

若要查询（而不是导入）Excel 数据，只需使用标准 SELECT ... FROM ... 语法。

有关分布式查询的详细信息，请参阅以下主题：

分布式查询（SQL Server 2016 仍支持分布式查询，但此功能的相关文档尚未更新。）
OPENROWSET
OPENDATASOURCE

2、链接服务器

还可以将从 SQL Server 到 Excel 文件的永久性连接配置为链接服务器。下面的示例将现有 Excel 链接服务器 EXCELLINK 上的 Data 工作表数据导入名为 Data_ls 的新 SQL Server 数据库表。

USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO

可以通过 SQL Server Management Studio 或运行系统存储过程 sp_addlinkedserver（如以下示例所示）创建链接服务器。

DECLARE @RC int

DECLARE @server     nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider   nvarchar(128)
DECLARE @datasrc    nvarchar(4000)
DECLARE @location   nvarchar(4000)
DECLARE @provstr    nvarchar(4000)
DECLARE @catalog    nvarchar(128)

-- Set parameter values
SET @server =     'EXCELLINK'
SET @srvproduct = 'Excel'
SET @provider =   'Microsoft.ACE.OLEDB.12.0'
SET @datasrc =    'C:\Temp\Data.xlsx'
SET @provstr =    'Excel 12.0'

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog

有关链接服务器的详细信息，请参阅以下主题：

有关链接服务器和分布式查询的更多示例和详细信息，请参阅以下主题：

五、先决条件 - 将 Excel 数据保存为文本

若要使用本页上的其他方法（BULK INSERT 语句、BCP 工具或 Azure 数据工厂），必须先将 Excel 数据导出到文本文件中。

在 Excel 中，依次选择“文件”|“另存为”，再选择“文本文件(制表符分隔)(.txt)”或“CSV (逗号分隔)(.csv)”作为目标文件类型 * * 。

如果要从工作簿中导出多个工作表，请选择每个工作表，然后重复此过程。 “另存为”命令仅导出活动工作表。

提示

为在使用数据导入工具时获得最佳结果，保存仅包含列标题和数据行的工作表。如果保存的数据包含页标题、空白行、注释等，稍后可能会在导入数据时发生意外结果。

六、导入平面文件向导

通过单步执行导入平面文件向导各页面，导入保存为文本文件的数据。

如前面先决条件部分中所述，必须先将 Excel 数据导出为文本，然后才能使用导入平面文件向导导入它。

有关导入平面文件向导的详细信息，请参阅将平面文件导入到 SQL 向导。

七、 BULK INSERT 命令

BULK INSERT 是可以通过 SQL Server Management Studio 运行的 Transact-SQL 命令。下面的示例将 Data.csv 逗号分隔文件中的数据加载到现有数据库表中。

如前面先决条件部分中所述，必须先将 Excel 数据导出为文本，然后才能使用 BULK INSERT 导入它。 BULK INSERT 无法直接读取 Excel 文件。使用 BULK INSERT 命令，可以导入存储在本地或在 Azure Blob 存储中的 CSV 文件。

USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
   WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO

有关 SQL Server 和 SQL 数据库的详细信息和示例，请参阅以下主题：

八、 BCP 工具

BCP 是通过命令提示符运行的程序。下面的示例将 Data.csv 逗号分隔文件中的数据加载到现有 Data_bcp 数据库表中。

如前面先决条件部分中所述，必须先将 Excel 数据导出为文本，然后才能使用 BCP 导入它。 BCP 无法直接读取 Excel 文件。用于从保存在本地存储的测试 (CSV) 文件中导入 SQL Server 或 SQL 数据库。

重要

对于存储在 Azure Blob 存储中的文本 (CSV) 文件，请使用 BULK INSERT 或 OPENROWSET。有关示例，请参阅示例。

bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

有关 BCP 的详细信息，请参阅以下主题：

九、常见错误

1、Microsoft.ACE.OLEDB.12.0 尚未注册

发生此错误的原因是未安装 OLEDB 提供程序。请通过 Microsoft Access 数据库引擎 2010 可再发行组件进行安装。如果 Windows 和 SQL Server 都是 64 位，请务必安装 64 位版本。

完整错误为：

Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

2、无法为链接服务器 "(null)" 创建 OLE DB 提供程序 "Microsoft.ACE.OLEDB.12.0" 的实例

这表示 Microsoft OLEDB 配置错误。运行以下 Transact-SQL 代码可解决此问题：

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

完整错误为：

Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

3、32 位 OLE DB 提供程序 "Microsoft.ACE.OLEDB.12.0" 无法在 64 位 SQL Server 上的进程内加载

32 位版本的 OLD DB 提供程序与 64 位 SQL Server 一起安装时，会发生此情况。要解决此问题，请卸载 32 位版本，改为安装 64 位版本的 OLE DB 提供程序。

完整错误为：

Msg 7438, Level 16, State 1, Line 3
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

4、链接服务器 "(null)" 的 OLE DB 提供程序 "Microsoft.ACE.OLEDB.12.0" 报告了错误。提供程序未给出有关错误的任何信息

5、无法初始化链接服务器 "(null)" 的 OLE DB 提供程序 "Microsoft.ACE.OLEDB.12.0" 的数据源对象

这两个错误通常表示 SQL Server 进程和文件之间存在权限问题。请确保运行 SQL Server 服务的帐户对文件具有完全访问权限。建议不要尝试从桌面导入文件。

完整错误为：

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

十、另请参阅

使用 SQL Server Integration Services (SSIS) 将数据导入 Excel 或从 Excel 导出数据

posted on 2020-11-20 17:32 springsnow 阅读(1257) 评论(0) 编辑收藏举报

刷新页面返回顶部

工作在浏览器上人-YangBobin