Posts - 157,
Articles - 0,
Comments - 698
人生如棋，我愿为卒，行动虽缓，可谁曾见我后退过！
16:31 by BIWORK, ... 阅读,
文章更新历史
日 - 加入了部分更新内容，在文章最后提到了关于不同 Office Excel 版本间的连接问题。
这篇文章主要总结在 SSIS 中访问和处理 Excel 数据的四个方面的主题内容 (都是处理以 .xlsx 结尾的 Excel 文件)&-
如何在 SSIS 中集成对 Microsoft Excel 的访问支持以及注意事项。
如何在 SSIS 中连接和访问 Microsoft Excel 文件以及注意事项。
如何加载不同 Sheet 页的数据到同一个表中。
如何加载不同 Sheet 页的数据到不同的表中。
连接和处理 Microsoft&Excel 文件的方式有很多种，包括使用 C#.NET 编程的形式加载处理数据，但本文只考虑在 SSIS 中如何加载 Microsoft Excel 文件中的数据。
在以前的 Microsoft Excel 文件版本中，使用 SQL Server 2008 R2 - BIDS 开发工具很容易处理，因为以前的版本是支持以 .xls 结尾的 Excel 文件，比如说 Excel 2003。Microsoft Office 版本升级之后，他们开始采用基于OpenXML的新的文件类型，也就是以 .xlsx 结尾的 Excel 文件类型。但是在 BIDS 中包括现在的 SQL Server 2012 - SSDT 版本的工具对以 .xlsx 结尾的 Excel 文件没有直接驱动支持，因此需要人工的去配置一下。
在 SSIS 中集成对 Microsoft Excel 的访问支持
在现有的 BIDS (SQL Server 2008 R2 及以前的版本中开发工具的简称)&和 SSDT (SQL Server 2012 的 BI 开发工具) 中打开一个 OLE DB Connection 看到的一些 OLE DB 的驱动。 为什么不去直接使用 Excel Source 组件？ 因为 Excel Source 组件不支持以 .xlsx 后缀结尾的 Excel 连接和访问，因此需要使用变通的 OLE DB 访问方式。
需要去微软官方网站下载并安装驱动 -&，可以选择相应的语言，我选择的是英文版本。
一定要注意的是，在这里只能选择和安装32位的安装文件，因为我们的 BIDS 也好 SSDT 也好本身都是安装的都是32位的环境，所以如果安装了64位的驱动，BIDS 和 SSDT 也没有办法看到的。并且如果已经安装了 64 位的 Microsoft Access Data Engine 2010，那么就需要先卸载下来，否则 32位版本的驱动是安装不了的，低版本无法覆盖高版本驱动。
安装之后重启 SSDT 就能看到这个驱动了，我们就可以使用这个驱动连接我们的 Excel 了。
在 SSIS 中连接和访问 Microsoft Excel 文件
基于上面的驱动的安装，我们可以做一个简单的数据加载测试，同时里面也有一些注意事项是需要我们注意的。
测试目标表
USE BIWORK_SSIS
IF OBJECT_ID('StaffExcel') IS NOT NULL
DROP TABLE StaffExcel
IF OBJECT_ID('DepartmentExcel') IS NOT NULL
DROP TABLE DepartmentExcel
CREATE TABLE DepartmentExcel
Department NVARCHAR(50),
Manager NVARCHAR(50)
CREATE TABLE StaffExcel
FullName NVARCHAR(50),
City NVARCHAR(50),
Occupation NVARCHAR(50)
源数据是 Excel 的 Sheet 页中的数据，并且我们的 Excel Sheet 页上的数据格式应该是规范的，不规范的数据不适合通过这种方式处理。
我们要做的就是把 Sheet 页是 Department 的数据从 Excel 导入到 DepartmentExcel 表中，新建一个 SSIS Package 并新建一个 OLE DB Connection。选择 Microsoft Office 12.0 Access Database Engine OLE DB Provider，并指定 Excel 源文件的路径和名称。
注意在 All 页面中，一直要指定 Excel 12.0 否则是无法连接到 Excel 数据源的。
拖放一个 Dataflow Task 并创建一个 OLE DB Source 并直接使用刚才创建好的 OLE DB Connection，指定 Sheet 名称。
创建 OLE DB Connection 连接到目标表并创建 OLE DB Destination 连接到 OLE DB Source， 这时会出现警告。因为从 Excel 中加载的数据，默认格式都变成了 255&长度的NVARCHAR 数据类型，这样会发生截断并且目标表类型也不匹配。
警告内容：Validation warning. DST_Department: {35C08A6A-D20-4ED829E68EF9}: Truncation may occur due to inserting data from data flow column "Department" with a length of 255 to database column "Department" with a length of 50.&
使用 Data Conversion&进行转换， Output Alias 是从 Data Conversion 向下输出的转换后的别名。
转换完了然后配置后面的 Data Mapping，注意 Data Mapping 中要 Map 的是 Output Alias 的那些 Column 而不是 Input Column 中的那些列。执行 SSIS Package 的时候出现错误，可以从下图中看出来有以下两种错误信息。
第一种说明是连接失败，发生在 Task DST_Department 中：
[OLE_DB_SRC_Department [43]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.& The AcquireConnection method call to the connection manager "E:\WorkSpace\Input\ExcelSource.xlsx" failed with error code 0xC0209303.& There may be error messages posted before this with more information on why the AcquireConnection method call failed.
第二种说明是数据源连接有问题，并且指明了错误的原因：因为我们的驱动是 32 位模式，但是这个包仍然是默认64位模式运行，因此在 64位运行环境下是无法检测到这种只支持32位链接的驱动的。
[Connection manager "E:\WorkSpace\Input\ExcelSource.xlsx"] Error:&The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode.&Error code: 0x. An OLE DB record is available.& Source: "Microsoft OLE DB Service Components"& Hresult: 0x& Description: "Class not registered".
在 SSIS 项目，右键属性来修改包的运行模式改成 False 让它在32位模式运行。
保存并运行成功，有4条数据顺利加载到数据表中。
查询 DepartmentExcel&表的结果。
如何加载不同 Sheet 页的数据到同一个表中
这里不同的 Sheet 页是指在同一个 Excel Source 文件中有很多的 Sheet 页，但是这些 Sheet 页中的数据格式都是一样的，如下图所示。
这个 Excel 文件有三个 Sheet 页，一个是 Department，另外一个是我在博客园的ID -&，另外一个随便就叫 Sheet1 了。 这种场景常常出现，比如有可能按天保存数据到同一个 Excel 文件中，每一个 Sheet 页上的数据格式是一致的，那么就可以用我们这里的解决方案来完成了。
BIWORK 页中的数据格式和 Department 中是一样的，也就是 Column 列格式是一样的， Sheet1 中也是如此。
这里可以考虑使用循环，思路是循环同一个 Excel 源上的 Sheet 页，每次在循环中替换 Sheet 名就可以了。
新建一个字符串类型的变量，变量的名称一定要是 Sheet Name，可以是任意中的一个比如 Department 或者 BIWORK，但一定要有，后缀加上 $。
新建一个 OLE DB Connection，操作方法和上面的示例一样的，别忘记修改 All 里面的 Excel 12.0。但是一定要注意，在我们这个例子有，是有两个连接到同一个 Excel 源的 OLE DB 连接的。一个是下面用来循环遍历 Sheet Name 的，一个是用来加载数据的。
添加一个 Foreach Loop 容器，按照下图显示操作和配置，这里的循环作用是循环指定 EXCEL 源上的 Table Name 即 Sheet Name。这个链接源就是上面一个步骤创建好的，再次强调 -&它只用来遍历 Excel Sheet Name，不会和数据有任何交互！
在变量配置中，指定 SheetName 变量来接受从 Excel 源中遍历来的 SheetName。特别要说明的是，这里的 Index 只能写成 2，表示 SheetName。它并不表示 Sheet 页的数量，这一点一定要注意，尝试使用 Index 0 或者 1 是取不到任何有意义的东西的。
把示例一种的 Data Flow Task 直接拷贝放入这个容器中，然后修改一下 OLE DB Source ，因为此时的表名是由变量来决定的了。
保存并运行，要注意到 ExcelSourceSame 和 中间的那个连接管理器其实都是连接到同一个数据源，但是它们的作用不同。
刚才新添加了几个 Sheet，名字随便取。
执行完上面的包之后查询一下数据库，不同 Sheet 页中的数据全部加载到同一张表了。
如何加载不同 Sheet 页的数据到不同的表中
所谓不同的 Sheet 页是指在同一个 Excel 数据源中的 Sheet 页中的数据格式不同，要把它们分别加载到不同的表中。
基于上面的示例 Excel，我加上一个 Staff 页数据，这个页的数据目标表是文章开头处创建的 StaffExcel 表。
所以在这个示例中，Excel 源中的 Sheet 页与数据库中的表就要求分开被处理了。
Sheet - Department, BIWORK, Sheet1, Sheet2, Sheet3 都应该指向 DepartmentExcel。
Sheet - Staff 指向 StaffExcel。
要解决这个问题首先要明白在建立从数据源到目的表的过程中，需要提前建立好数据源列到目标表列的 Column Mapping。因此不能使用 Department Sheet 到 DepartmentExcel 表的 Data Mapping 来代替 Staff Sheet 到 StaffExcel 表的 Data Mapping，所以动态根据表名直接连接到目的表而想绕过 Data Mapping 这一点很难做到。
那这里有一种变通的方法，就是先为源与目标表建立好 Data Mapping，然后根据对 Sheet Name 的判断自动将 Sheet 分发到不同的 Data Mapping。
对上面的例子做出如下修改：
1. 添加一个 Mapping 表，用来将 Sheet Name 进行归类，这种是需要提前收集的。
在一个自动化的操作流程中，这个数据是可以实现自动添加的。因为首先 Excel 源本身也有可能就是通过程序输出的，因此在输出的过程中是可以对 SheetName 的命名做出要求的，包括一个 Excel 上的 Sheet 表 Mapping 分类。那么这种相对规范的 Excel 表是完全有可能适用于我们这个示例提出的解决方案的，除此之外一切不规范的 Excel 建议通过 C# Script 解决。
IF OBJECT_ID('TableSheetMapping') IS NOT NULL
DROP TABLE TableSheetMapping
CREATE TABLE TableSheetMapping
ID INT IDENTITY(1,1) PRIMARY KEY,
DestTableName NVARCHAR(50),
SheetName NVARCHAR(50)
INSERT INTO TableSheetMapping VALUES
('DepartmentExcel','Department'),
('DepartmentExcel','BIWORK'),
('DepartmentExcel','Sheet1'),
('DepartmentExcel','Sheet2'),
('DepartmentExcel','Sheet3'),
('StaffExcel','Department')
2. 添加变量。
SheetName - 是从 Foreach Loop 循环得到的 Sheet 页的名称，但是只仅仅通过 N 多的 Sheet 页是不知道要匹配对应的表的。
TableName -&因此这里的 TableName 就是根据 Sheet 页的名称到数据库中去查来的一个结果。
DepartmentSheet - 用来指定与 DepartmentExcel 表建立连接的 SheetName。
StaffSheet - 同 DepartmentSheet。
因此只有添加了 Department$ 和 Staff$，OLE DB Source 才知道数据源是什么样的数据结构，该如何与下游目标表与匹配。
3. 添加一个 Staff Data Flow Task，并且修改相应的配置。
注意 Department Data Flow Task 也需要修改要将 SheetName 换成 DepartmentSheet。
下面是新添加的 DST_Staff_Sheet 控制流，命名应该叫 DFT，是我写错了就不改了。
DST_Staff_Sheet 中三个组件都需要修改。
OLE_DB_SRC_Staff 指定表名变量，其它配置省略。
那么现在要做的事情就是分发 Sheet 了，在 Foreach Loop 中添加一个 Execute SQL Task 用来根据 SheetName 获取 Table 名称。
参数变量 SheetName 是从 Loop 循环得到的。
查询的结果保存在 TableName 变量中。
新添加一个 Script Task 并且传入变量。
Script Task&中的逻辑主要是用来接收 TableName，根据TableName判断来到底是 DepartmentSheet 变量还是 StaffSheet 变量来接受循环中产出的 Sheet 名称，这样下游的 Data Flow Task 就可以根据 SheetName$ 来获取数据源的数据通道了。
public void Main()
// TODO: Add your code here
string tableName = Dts.Variables["User::TableName"].Value.ToString();
string sheetName = Dts.Variables["User::SheetName"].Value.ToString();
if (tableName == "DepartmentExcel")
Dts.Variables["User::DepartmentSheet"].Value = sheetN
if (tableName == "StaffExcel")
Dts.Variables["User::StaffSheet"].Value = sheetN
Dts.TaskResult = (int)ScriptResults.S
最后要做的一件事情就是使用条件表达式决定流程的走向，根据表名来判断 Script Task 的输出。
保存并执行 SSIS Package，所有的数据都分散到不同的执行流程中了。
查询一下数据库，数据全部进去了并且 Department 和 Staff 是从不同的 Sheet 页上取到的数据。
为什么看到 TableName 在逻辑判断的过程中出现了好几次？
我们假设是一个 Excel 文件中 Sheet 页比较多的情况，因此使用了一个数据库 Mapping 表，根据 Mapping 表来看一次循环中当前的 Sheet 页是属于哪一个目标表的数据源。
根据 SheetName 取到 Table Name 之后，需要在 C#&Script 中使用 Table Name 来决定在下面的两个 Data Flow Task 中表变量 DepartmentSheet 和 StaffSheet 到底是哪一个源 Sheet。
确定好了源 Sheet 后，再通过 Table Name 作为条件约束控制流程的分支走向，这样就避免了多次循环同一 Sheet 的问题。
其实在 SSIS 中处理 Excel 包括连我自己都不太喜欢这种方式，因为经常会碰到连接的问题，字符串格式转换的问题，不规范的数据格式问题等等。越是不规范的 Excel 文件越是应该在源头控制它，比如在 Excel 文件输出的时候通常会告诉上游产出者我们对 Excel 文件格式的要求。不要求做到绝对规范，但是最起码会做到一个 Excel 文件只保留一个 Sheet，一个 Excel 只表示一个数据源。这样在下游 SSIS 处理中就会减少很多的工作量，因此在数据整理收集阶段，沟通的工作是非常非常重要的，上游数据源一个很小的改动或者格式调整对下游数据处理就会产生极大的或好或坏的影响。
日 后记更新
非常奇怪的一件事情发生了！在我今天的一次测试中，我发现只要安装完了 AccessDatabaseEngine 之后，我完全就可以直接使用 Excel Source 和 Excel Destination 就可以操作 Excel 数据的抽取和导出了！并且在 64 位下也是可以正常运行的，不需要调整成&32 位的模式。
直接使用 Microsoft Excel 2007 也是可以的。
在检查它的 Provider 时，发现它本质上仍然是 -
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\BIWORKSPACE_FILE\TS_BIWORK_SSIS\INPUT_DIRECTORY\012\Hotel.Extended Properties="EXCEL 12.0 XML;HDR=YES";
我能想起来的区别就是：
本文中测试的环境安装的 Office 版本是 Office 365，而现在新的测试环境 日 - 是 Microsoft Office Professional Plus 2013。
现在唯一的问题是，由于我在写这篇文章时的测试环境已经丢失，我现在安装的都是 Professional Plus 2013，所以无法还原在写这篇文章时的配置，包括报错信息的出现以及问题解决前后的失败与成功对比。所以希望大家在看到这篇文章的时候，如果有可能也帮助验证一下在自己当前版本下关于这个 Excel 的连接情况。
但无论如何，多了一种选择，多了一种解决方案，希望能够帮助到大家。
更多 BI 文章请参看&& 如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。查看: 7384|回复: 10
请教 数据有效性 如何引用不同工作表中的数据 谢谢
阅读权限20
在线时间 小时
要求在sheet1中的部门列只能输入sheet2中的部门信息。谢谢！
08:54 上传
点击文件名下载附件
2.86 KB, 下载次数: 126
阅读权限20
在线时间 小时
引用其他工作表区域,你得先定义名称
阅读权限20
在线时间 小时
...................
(90.59 KB, 下载次数: 43)
09:02 上传
阅读权限20
在线时间 小时
zjcat 发表于
引用其他工作表区域,你得先定义名称
明白了，谢谢你的回复，非常感谢！
阅读权限20
在线时间 小时
& & & & & & & &
zjcat 发表于
...................
请问 你的操作中 数据有效性对话框的来源 为什么能选择 ”粘贴名称“。 多谢！
阅读权限20
在线时间 小时
jiniapin123 发表于
请问 你的操作中 数据有效性对话框的来源 为什么能选择 ”粘贴名称“。 多谢！
你直接按F3就可以把你的定义的名称调用出来
阅读权限20
在线时间 小时
真是学习了啊呵呵
阅读权限20
在线时间 小时
& & & & & & & &
zjcat 发表于
你直接按F3就可以把你的定义的名称调用出来
多谢你热心帮助，谢谢
阅读权限20
在线时间 小时
学习了，正在用这个功能！
阅读权限20
在线时间 小时
直接按F3,没有出现 ”粘贴名称“对话框啊？？
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师Excel表格中数据比对和查找的几种技巧 - Office办公助手
专业的Office办公软件学习和Office办公软件教程网站！
Excel表格中数据比对和查找的几种技巧
发布时间：09-11　来源：Office办公助手
经常被人问到怎么对两份Excel数据进行比对，提问的往往都很笼统；在工作中，有时候会需要对两份内容相近的数据记录清单进行比对，需求不同，比对的的目标和要求也会有所不同。下面Office办公助手()的小编根据几个常见的应用环境介绍一下Excel表格中数据比对和查找的技巧。
应用案例一：比对取出两表的交集(相同部分)
Sheet1中包含了一份数据清单A，sheet2中包含了一份数据清单B，要取得两份清单共有的数据记录(交集)，也就是要找到两份清单中的相同部分。
方法1：高级筛选
高级筛选是处理重复数据的利器。
选中第一份数据清单所在的数据区域，在功能区上依次单击【数据】&&【高级】(2003版本中菜单操作为【数据】&&【筛选】&&【高级筛选】)，出现【高级筛选】对话框。
在对话框中，筛选【方式】可以根据需求选取，例如这里选择&将筛选结果复制到其他位置&；【列表区域】就是之前所选中的第一份数据清单A所在的单元格区域；【条件区域】则选取另外那份清单B所在的单元格区域。如下图所示：
点击【确定】按钮后，就可以直接得到两份清单的交集部分，效果如下图。其中两个清单中虽然都有【西瓜】和【菠萝】，但是由于数量不一致，所以没有作为相同记录被提取出来。
这个操作的原理，就是利用了高级筛选功能对于匹配指定条件的记录进行筛选的功能，把两张表中的任意一张作为条件区域，在另外一张表中就能筛选出与之相匹配的记录，忽略掉其他不相关的记录。
需要注意的是，使用高级筛选的时候务必注意两个清单的标题行要保持一致(高级筛选中作为条件区域的前提)，并且在选取【列表区域】和【条件区域】的时候都要把标题行的范围包含在其中。
方法2：公式法
使用公式进行比对的方法有很多，如果是单列数据对比比较常用的函数是，如果是多列数据记录对比，比较胜任。
在其中一张清单的旁边输入公式：
=SUMPRODUCT((A2&B2=Sheet2!A$2:A$13&Sheet2!B$2:B$13)*1)
并向下复制填充。其中的Sheet2!A$1:A$13和Sheet2!B$2:B$13是另一张清单中的两列数据区域，需要根据实际情况修改。公式结果等于1的记录就是两个清单的交集部分，如下图所示：
应用案例二：取出两表的差异记录
要在某一张表里取出与另一张表的差异记录，就是未在另外那张清单里面出现的部分，其原理和操作都和上面第一种场景的差不多，所不同的只是筛选后所选取的集合正好互补。
方法1：高级筛选
先将两个清单的标题行更改使之保持一致，然后选中第一份数据清单所在的数据区域，在功能区上依次单击【数据】&&【高级】，出现【高级筛选】对话框。在对话框中，筛选方式选择&在原有区域显示筛选结果&；【列表区域】和【条件区域】的选取和前面场景1完全相同，如下图所示：
点击【确定】完成筛选，将筛选出来的记录全部选中按【Del】键删除(或做标记)，然后点击【清除】按钮(2003版本中为【全部显示】按钮)就可以恢复筛选前的状态得到最终的结果，如下图所示：
方法2：公式法
使用公式的话，方法和场景1完全相同，只是最后需要提取的是公式结果等于0的记录。
应用案例三：取出关键字相同但数据有差异的记录
前面的两份清单中，【西瓜】和【菠萝】的货品名称虽然一致，但在两张表上的数量却不相同，在一些数据核对的场景下，就需要把这样的记录提取出来。
方法1：高级筛选
高级筛选当中可以使用特殊的公式，使得高级筛选的功能更加强大。
第一张清单所在的sheet里面，把D1单元格留空，在D2单元格内输入公式：
=VLOOKUP(A2，Sheet2!$A$2:$B$13，2，0)&&B2
然后在功能区上依次单击【数据】&&【高级】，出现【高级筛选】对话框。在对话框中，筛选方式选择&在原有区域显示筛选结果&；【列表区域】选取第一张清单中的完整数据区域，【条件区域】则选取刚刚特别设计过的D1:D2单元格区域，如下图所示：
点击【确定】按钮以后，就可以得到筛选结果，就是第一张中货品名称与第二张表相同但数量却不一致的记录清单，如下图所示：
同样的，照此方法在第二张清单当中操作，也可以在第二张清单中找到其中与第一张清单数据有差异的记录。
这个方法是利用了高级筛选中可以通过自定义公式来添加筛选条件的功能，有关高级筛选中使用公式作为条件区域的用法，可参考本站发布的；另外一篇教程：
Excel中数据库函数和高级筛选条件区域设置方法详解
方法2：公式法
使用公式还是可以利用前面用到的SUMPRODUCT函数，在其中一张清单的旁边输入公式：
=SUMPRODUCT((A2=Sheet2!A$2:A$13)*(B2&&Sheet2!B$2:B$13))
并向下复制填充。公式中的包含了两个条件，第一个条件是A列数据相同，第二个条件是B列数据不相同。公式结果等于1的记录就是两个清单中数据有差异的记录，如下图所示。这个例子中也可以使用更为人熟知的来进行匹配查询，但是VLOOKUP只适合单列数据的匹配，如果目标清单中包含了更多字段数据的差异对比，还是的扩展性更强一些。
上一篇： 下一篇：