将 Excel 数据导入 SQL Server 或 Azure SQL 数据库

适用于：SQL ServerAzure SQL 数据库

将 Excel 文件中的数据导入 SQL Server 或 Azure SQL 数据库的方法有多种。 某些方法允许你在单个步骤中从 Excel 文件直接导入数据，其他方法要求在导入数据前，必须将 Excel 数据先导出为文本 (CSV 文件)。

本文总结了常用的方法，并提供有关更为详细的信息的链接。 本文不包括 SSIS 或 Azure 数据工厂等复杂工具和服务的完整描述。 要详细了解感兴趣的解决方案，请单击所提供的链接。

方法列表

可采用多种方式从 Excel 导入数据。 可能需要安装 SQL Server Management Studio (SSMS) 才能使用其中一些工具。

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

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

导入和导出向导

使用 SQL Server 导入和导出向导直接从 Excel 文件导入数据。 还可以将设置保存为可以稍后自定义和重用的 SQL Server Integration Services (SSIS) 包。

1. 在 SQL Server Management Studio 中，连接到 SQL Server 数据库引擎 的实例。

2. 展开 “数据库” 。

3. 右键单击某个数据库。

4. 选择“任务”。

5. 选择“导入数据”或“导出数据”：

   

这将启动向导：

若要了解详细信息，请查看：

• 启动 SQL Server 导入和导出向导
• 导入和导出向导的简单示例入门

Integration Services (SSIS)

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

若要了解详细信息，请查看：

• Excel 源
• SQL Server 目标

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

OPENROWSET 和链接服务器

分布式查询

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

必须先启用 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 2019 (15.x) 仍支持分布式查询，但此功能的相关文档尚未更新。）
• OPENROWSET
• OPENDATASOURCE

链接服务器

还可以将从 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 (SSMS) 或运行系统存储过程 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;

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

• 创建链接服务器
• OPENQUERY

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

• 如何通过 SQL Server 链接服务器和分布式查询使用 Excel

先决条件 - 将 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 数据库的详细信息和示例，请参阅以下文章：

• 使用 BULK INSERT 或 OPENROWSET(BULK...) 导入批量数据
• BULK INSERT

BCP 工具

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

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

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

有关 BCP 的详细信息，请参阅以下文章：

• 使用 bcp 实用工具导入和导出批量数据
• bcp 实用工具
• 准备用于批量导出或导入的数据

复制向导 (ADF)

通过逐步执行 Azure 数据工厂 (ADF) 复制向导各页面，导入保存为文本文件的数据。

如前面先决条件部分中所述，必须先将 Excel 数据导出为文本，然后才能使用 Azure 数据工厂导入它。 数据工厂无法直接读取 Excel 文件。

有关复制向导的详细信息，请参阅以下文章：

• 数据工厂复制向导
• 教程：使用数据工厂复制向导创建带有复制活动的管道。

Azure 数据工厂

如果熟悉 Azure 数据工厂，并且不想运行复制向导，请创建带有复制活动的管道，用于将文本文件复制到 SQL Server 或 Azure SQL 数据库。

如前面先决条件部分中所述，必须先将 Excel 数据导出为文本，然后才能使用 Azure 数据工厂导入它。 数据工厂无法直接读取 Excel 文件。

若要详细了解如何使用这些数据工厂源和接收器，请参阅以下文章：

• 文件系统
• SQL Server
• Azure SQL 数据库

若要开始学习如何使用 Azure 数据工厂复制数据，请参阅以下文章：

• 使用复制活动移动数据
• 教程：使用 Azure 门户创建带有复制活动的管道

常见错误

Microsoft.ACE.OLEDB.12.0 尚未注册

发生此错误的原因是未安装 OLEDB 提供程序。 请通过 Microsoft Access 数据库引擎 2016 可再发行组件进行安装。 如果 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.

无法为链接服务器 "(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)".

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.

链接服务器 "(null)" 的 OLE DB 提供程序 "Microsoft.ACE.OLEDB.12.0" 报告了错误。

无法初始化链接服务器 "(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 导出数据
• bcp 实用工具
• 使用复制活动移动数据