适用于: SQL Server Azure SQL 数据库

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

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

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

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

首先导出到文本(SQL Server 和 SQL 数据库) 直接从 Excel(仅本地 SQL Server)进行

导入和导出向导

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

  • 在 SQL Server Management Studio 中,连接到 SQL Server 数据库引擎 的实例。

  • 展开 “数据库”

  • 右键单击某个数据库。

  • 选择“任务”。

  • 选择“导入数据”或“导出数据”

    Integration Services (SSIS)

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

    若要了解详细信息,请查看:

  • Excel 源
  • SQL Server 目标
  • 若要开始学习如何生成 SSIS 包,请参阅教程 如何创建 ETL 包

    OPENROWSET 和链接服务器

    在 Azure SQL 数据库中,无法直接从 Excel 导入。 必须首先 将数据导出到文本 (CSV) 文件

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

    分布式查询

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

    在 Azure SQL 数据库中,无法直接从 Excel 导入。 必须首先 将数据导出到文本 (CSV) 文件

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

    sp_configure 'show advanced options', 1;
    RECONFIGURE;
    sp_configure 'ad hoc distributed queries', 1;
    RECONFIGURE;
    

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

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

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

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

    若要将导入的数据追加到现有表,而不是新建表,请使用 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;
    SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
    

    可以通过 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;
    BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
       WITH (
          FIELDTERMINATOR = ',',
          ROWTERMINATOR = '\n'
    

    有关 SQL Server 和 SQL 数据库的详细信息和示例,请参阅以下文章:

  • 使用 BULK INSERT 或 OPENROWSET(BULK...) 导入批量数据
  • BULK INSERT
  • 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 的详细信息,请参阅以下文章:

  • 使用 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 实用工具
  • 使用复制活动移动数据
  • 即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:https://aka.ms/ContentUserFeedback

    提交和查看相关反馈