如何使用包内 Excel 文件第一行中指定的列名? [英] How to use the column names specified in Excel file's first row within a package?

问题描述

谁能告诉我从 Excel 源导入数据时如何更改默认列名(F1、F2、F3 等)到 SSIS 2005 中的 OLEDB 目标.

Could anyone tell me how to change default column names (F1, F2, F3 etc..) when importing data from Excel source to an OLEDB destination in SSIS 2005.

示例:我的 Excel 电子表格中的数据如下所示

Example: Data in my Excel spreadsheet looks as following

Title         | ProvinceID | DistrictID |
--------------|------------|------------|
Construction  |      12    |      32    |

问题是，当我从工作表导入数据时，它会获取数据，但列标题名称显示为 F1、F2 和 F3，我希望列名称与 Excel 电子表格中的定义相同.非常感谢您的帮助

The problem is that when I import data from sheet it gets the data but column header names are displayed as F1,F2 and F3, I want the column names to be as defined in the Excel spreadsheet. Highly appreciate your assistance

推荐答案

解决方案:

您可以通过在配置 Excel 连接管理器 时选中框First row has column names 来完成此操作.

Solution:

You can do this by checking the box First row has column names while configuring the Excel Connection Manager.

以下示例使用 SSIS 2008 R2 说明了这一点.

Here is an example that illustrates this using SSIS 2008 R2.

• 假设您有一个如下所示的 Excel 文件，其中第一行包含列名.

• 当您配置 Excel 连接管理器时，您需要选择 Excel 文件.在 Excel 连接管理器上，您会注意到有一个名为 First row has column names 的复选框.如果您希望使用在 Excel 文件第一行中指定的列名称在 SSIS 包中可见，请选中此框.

• When you configure an Excel Connection Manager, you need to select the Excel file. On the Excel connection manager, you will notice that there is a checkbox named First row has column names. Check this box if you want to use the column names specified in the first row of your Excel file to be visible in SSIS package.

• 因此，当您在数据流任务中配置 Excel 源时，您会注意到 Excel 文件第一行中指定的列名称正确显示.以下屏幕截图显示了在 Excel 源 中使用 Excel 连接管理器时列名的显示方式.

• So, when you configure an Excel Source within a Data Flow task, you will notice the column names specified in the first row of the Excel file being displayed correctly. Following screenshots show how the column names are displayed when an Excel Connection Manager is used in an Excel Source.

• 您看到列名称为 F1、F2 和 F3 的原因是因为您可能没有选中 Excel 连接管理器上的 First row has column names 框.在这种情况下，它会出现在 Excel 源代码中，如下所示.

• The reason why you are seeing the column names as F1, F2 and F3 is because you probably didn't check the box First row has column names on the Excel Connection Manager. In that case, it will appear as shown here on the Excel source.

希望有所帮助.

这篇关于如何使用包内 Excel 文件第一行中指定的列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！