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

  • 项目

适用于：SQL Server Azure 数据工厂中的 SSIS Integration Runtime

本文介绍了必须提供的连接信息，以及必须配置的设置，以便从 Excel 导入数据或使用 SQL Server Integration Services (SSIS) 将数据导出到 Excel。

以下各节包含在 SSIS 中成功使用 Excel 或者了解和排除常见问题所需的信息：

  1. 可以使用的工具。

  2. 所需文件。

  3. 通过 SSIS 从 Excel 加载数据或向 Excel 加载数据时必须提供的连接信息以及必须配置的设置。

  4. 已知问题和限制。

可以使用的工具

可使用以下某一工具通过 SSIS 从 Excel 导入数据或将数据导出到 Excel：

获取连接到 Excel 所需的文件

如果没有安装 Excel 的连接组件，首先需要下载这些组件，然后才能通过 SSIS 从 Excel 导出数据或将数据导入 Excel。 默认情况下，没有安装 Excel 的连接组件。

使用在 Office Click-to-Run 外无法使用 Access ODBC、OLEDB 或 DAO 接口中的表了解你的环境是否需要额外的组件。

注意：Office 系统驱动程序仅在特定场景中受支持，请参阅 Office 的服务器端自动化的注意事项，了解具体指导。

指定 Excel 作为数据源

第一步需要指出希望连接到 Excel。

在 SSIS 中

在 SSIS 中，创建 Excel 连接管理器以连接到 Excel 源或目标文件。 创建连接管理器有多种方法：

  • 在“连接管理器”区域，右键单击并选择“新建连接” 。 在“添加 SSIS 连接管理器”对话框中，选择“EXCEL”，然后选择“添加” 。

  • 在“SSIS”菜单上，选择“新建连接” 。 在“添加 SSIS 连接管理器”对话框中，选择“EXCEL”，然后选择“添加” 。

  • 创建连接管理器的同时，在“Excel 源编辑器”或“Excel 目标编辑器”页配置“Excel 源”或“Excel 目标” 。

在 SQL Server 导入和导出向导中

在导入和导出向导中，在“选择数据源”或“选择目标”页上，选择“数据源”列表中的“Microsoft Excel” 。

如果在数据源列表中看不到 Excel，请确保运行的是 32 位向导。 Excel 连接组件通常是 32 位文件，在 64 位向导中不可见。

Excel 文件和文件路径

提供的第一条信息是 Excel 文件的路径和文件名称。 在 SSIS 包的“Excel 连接管理器编辑器”中提供此信息，或在导入和导出向导的“选择数据源”页或“选择目标”页中提供 。

输入路径和文件名，格式如下所示：

  • 针对本地计算机上的文件，指定 C:\TestData.xlsx。

  • 针对网络共享上的文件，指定 \\Sales\Data\TestData.xlsx。

或者，通过使用“打开”对话框，单击“浏览”定位电子表格 。

重要

无法连接到受密码保护的 Excel 文件。

Excel 版本

提供的第二条消息是 Excel 文件版本。 在 SSIS 包的“Excel 连接管理器编辑器”中提供此信息，或在导入和导出向导的“选择数据源”页或“选择目标”页中提供 。

选择用于创建文件的 Microsoft Excel 的版本，或另一可兼容的版本。 例如，如果安装 2016 连接组件遇到问题，可安装 2010 组件，然后选择该列表中的“Microsoft Excel 2007-2010” 。

如果只安装了旧版本的连接组件，则可能无法在此列表中选择新版本的 Excel。 “Excel 版本”列表包括 SSIS 支持的所有 Excel 版本 。 此列表中显示的项并不表示已安装必需的连接组件。 例如，即使没有安装 2016 连接组件，列表中仍会显示“Microsoft Excel 2016” 。

首行包含列名称

如果要从 Excel 导入数据，则下一步是指示数据的首行是否包含列名称。 在 SSIS 包的“Excel 连接管理器编辑器”中提供此信息，或在导入和导出向导的“选择数据源”页中提供 。

  • 如果因为源数据不具有列名称而禁用此选项，则向导将使用 F1、F2 等作为列标题。
  • 如果数据包含列名称，但禁用了此选项，则向导会导入列名称作为数据的首行。
  • 如果数据不包含列名称，但启用了此选项，则向导会将源数据的首行用作列名称。 在此情况下，源数据的首行不再包含在数据本身当中。

如果要从 Excel 导出数据，并且启用了此选项，则导出的数据的首行包括列名称。

工作表和范围

以下有三种可用作数据的源或目标的 Excel 对象类型：工作表、已命名范围或用其地址指定的未命名单元格范围。

  • 工作表。 若要指定工作表，请将 $ 字符附加到表名称的末尾，并用分隔符包围字符串 - 例如，[Sheet1$] 。 或者，在现有表和视图的列表中寻找以 $ 字符结尾的名称。

  • 命名区域。 若要指定命名区域，请提供区域名称 - 例如，MyDataRange 。 或者，在现有表和视图的列表中寻找不是以 $ 字符结尾的名称。

  • 未命名区域。 若要指定未命名的单元格区域，请将 $ 字符附加到表名称的末尾，添加区域说明，并用分隔符包围字符串 - 例如， [Sheet1$ A1: B4] 。

要选择或指定想要用作数据的源或目标的 Excel 对象类型，请执行以下操作之一：

在 SSIS 中

在 SSIS 中，在“Excel 源编辑器”或“Excel 目标编辑器”的“连接管理器”页上，执行以下操作之一 ：

  • 要使用“工作表”或“命名区域”，请选择“表或视图”作为“数据访问模式” 。 然后，在“Excel 表的名称”列表中，选择包含工作表或命名范围 。

  • 要使用用其地址指定的“未命名区域”，请选择“SQL 命令”作为“数据访问模式” 。 然后，在“SQL 命令文本”字段中，输入如下查询 ：

    SELECT * FROM [Sheet1$A1:B5]

在 SQL Server 导入和导出向导中

在导入和导出向导中，执行以下操作之一：

  • 从 Excel 导入时，执行以下操作之一 ：

    • 要使用“工作表”或“命名区域”，请在“指定表副本或查询”页上，选择“从一个或多个表或视图复制数据” 。 然后，在“选择源表和视图”页的“源”列中，选择源工作表和命名区域 。

    • 要使用用其地址指定的“未命名区域”，请在“指定表副本或查询”页上，选择“编写查询以指定要传输的数据” 。 然后，在“提供源查询”页，提供如下查询 ：

      SELECT * FROM [Sheet1$A1:B5]

  • 导出到 Excel 时，执行以下操作之一 ：

    • 要使用“工作表”或“命名区域”，请在“选择源表和视图”页的“目标”列中，选择目标工作表和命名区域 。

    • 要使用用其地址指定的“未命名区域”，请在“选择源表和视图”页的“目标”列中输入区域，格式如下所示（不含分隔符）：Sheet1$A1:B5。 该向导会添加分隔符。

选择或输入要导入或导出的 Excel 对象后，还可在向导的“选择源表和视图”页上执行以下操作 ：

  • 通过选择“编辑映射”查看源和目标之间的列映射 。

  • 通过选择“预览”预览示例数据以确认是否需要 。

数据类型的问题

数据类型

Excel 驱动程序只识别有限的一组数据类型。 例如，所有数值列均解释为双精度 (DT_R8)，并且所有字符串列（除了 memo 列）均解释为 255 个字符的 Unicode 字符串 (DT_WSTR)。 SSIS 按如下所示方式映射 Excel 数据类型：

  • 数值 - 双精度浮点 (DT_R8)

  • 货币 - 货币 (DT_CY)

  • 布尔 - 布尔 (DT_BOOL)

  • 日期/时间 - 日期 (DT_DATE)

  • 字符串 - Unicode 字符串，长度为 255 (DT_WSTR)

  • Memo - Unicode 文本流 (DT_NTEXT)

数据类型和长度转换

SSIS 不隐式转换数据类型。 因此，在将其加载到非 Excel 目标之前，可能需要使用“派生列”或“数据转换”转换来显式转换 Excel 数据，或在将它加载到 Excel 目标之前，从非 Excel 源转换数据。

此处是一些可能必需的转换的示例：

  • Unicode Excel 字符串列与具有特定代码页的非 Unicode 字符串列之间的转换。

  • 在 255 个字符的 Excel 字符串列和不同长度的字符串列之间转换。

  • 双精度 Excel 数值列与其他类型的数值列之间的转换。

提示

如果使用的是导入和导出向导，并且数据需要一些转换，则向导会配置必要的转换。 因此，即使想使用 SSIS 包时，使用导入和导出向导创建初始包也十分有用。 使用该向导帮助你创建和配置连接管理器、源、转换和目标。

导入的问题

空行

将工作表或命名区域指定为源时，该驱动程序都将读取从工作表或区域左上角第一个非空单元开始的连续单元块 。 因此，数据无需从行 1 开始，但在源数据中不能有空行。 例如，列标头和数据行之间不能有空行，工作表顶部的标题后不能跟空行。

如果数据上面有空行，则不能将数据作为工作表进行查询。 在 Excel 中，必须选择数据区域，然后为区域分配名称，并查询命名区域而非工作表。

缺少值

Excel 驱动程序读取指定源中一定数量的行（默认情况下为八行）以推测每列的数据类型。 如果推测出列可能包含混合数据类型（尤其是混合了文本数据的数值数据时），驱动程序将决定采用占多数的数据类型，并对包含其他类型数据的单元返回 Null 值。 （如果各种数据类型的数量相当，则采用数值类型。）Excel 工作表中大部分单元格格式设置选项不会影响此数据类型判断。

可以通过指定导入模式将所有值导入为文本来修改 Excel 驱动程序的此行为。 若要指定导入模式，请在属性窗口中将 IMEX=1 添加到 Excel 连接管理器的连接字符串内的“扩展属性”值中 。

截断的文本

驱动程序在确定 Excel 列是否包含文本数据时，它将基于采样的最长值来选择数据类型（字符串或 memo）。 如果驱动程序没有在其采样的行中发现任何长于 255 个字符的值，那么它会将该列视为 255 个字符的字符串的列而不是 memo 列。 因此，长度超过 255 个字符的值可能会被截断。

要从备注列导入文件但不被截断，有以下两种选择：

  • 请确保至少一个示例行中的备注列包含长于 255 个字符的值

  • 增加驱动程序采样的行数，以包括这样的行。 可以通过增加以下注册表项下“TypeGuessRows”的值增加采样的行数 ：

可再发行组件版本 注册表项
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

导出的问题

创建新的目标文件

在 SSIS 中

使用想要创建的新 Excel 文件的路径和文件名称创建 Excel 连接管理器。 然后，在“Excel 目标编辑器”，针对“Excel 工作表名称”，选择“新建”以创建目标工作表 。 此时，SSIS 创建具有指定工作表的新 Excel 文件。

在 SQL Server 导入和导出向导中

在“选择目标”页上，选择“浏览” 。 在“打开”对话框中，导航到想要在其中创建新 Excel 文件的文件夹，提供新文件名称，然后选择“打开” 。

导出到足够大的区域

指定某个区域作为目标时，如果该区域具有的列比源数据少，则将收到错误消息 。 但是，如果指定的区域具有的行比源数据少，则向导将继续写入没有错误的行并扩展区域定义以匹配新的行数 。

导出长文本值

若要将大于 255 个字符的字符串成功地保存到 Excel 列中，驱动程序必须将该目标列的数据类型识别为 memo ，而不是 string。

  • 如果现有目标表已包含数据行，则由驱动程序采样的前几行在备注列中必须包含至少一个值超过 255 个字符的实例。

相关内容

有关本文所述的组件和过程的详细信息，请参阅以下文章：

关于 SSIS

Excel 连接管理器
Excel 源
Excel 目标
使用 Foreach 循环容器循环遍历 Excel 文件和表
使用脚本任务处理 Excel 文件

关于 SQL Server 导入和导出向导

连接到 Excel 数据源
导入和导出向导的简单示例入门

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