多种技能相结合,将Excel(PowerQuery+VBA)的能力嫁接到SSIS中

在数据ETL过程中,除了常规的规范工整的关系数据库之外,很大一部分数据来源于用户自行生产的Excel数据源,此部分数据最容易产生脏乱差的数据现象。

前面推文虽有介绍使用dotNET和python这样的脚本语言去处理,但仍然有一片广阔的领域待开发,使用Excel自身的能力,即VBA和轻量化ETL工具PowerQuery的结合。本篇给大家送上Excel与SSIS结合的精彩解决方案。

使用场景

在PowerQuery的数据处理中,有相当多的一些功能使用起来非常方便,对应于企业级的SSIS,反而缺少了这些的灵活性,真正要完全使用SSIS来实现,非常繁琐。如PowerQuery里的逆透视功能,行列转置功能,标题行提升功能等。

所以,若可以在标准的SSIS流程中引入PowerQuery的轻量化数据处理功能,将原有复杂的数据结构,先进行清洗整合后,变为一个干净的数据源供SSIS调用上传至数据库中,这时整个方案的可行性和性价比都非常可观。

同时PowerQuery的弊端也因为SSIS的介入得到解决,如数据不能加载到其他目标位置仅能进入模型层,数据抽取性能问题,每次全量抽取一没必要二性能严重受影响。

技术原理

本篇将使用在SSIS中,使用循环容器遍历文件夹内所有Excel文件,将其文件路径获取到,再使用dotNET脚本打开用于数据转换的Excel模板文件(里面事先存储好PowerQuery的抽取清洗逻辑代码)。

将模板文件内的PowerQuery查询使用替换的方式,将其引用的文件路径替换为当次循环引用文件。

再进行数据加载过程,在模板文件中实现仅对当次循环文件的数据处理加工,并将其保存后,供下游的SSIS数据流任务调用此模板文件,实现模板文件的内容上传到数据库中。

每次循环,模板文件使用PowerQuery将不同数据加载进来并保存,实现所有的循环遍历文件的数据上传。

当次处理一个Excel文件而不是整个文件夹的文件,可以保障性能同时也防止Excel工作表行数不足存储所有数据记录行的报错数据丢失情况。

dotNET与VBA在Excel对象模型上的差别

在dotNET脚本中,引用Excel对象模型,理论上可以替代VBA的脚本,但本轮测试发现,在dotNET上的Excel对象模型,貌似未能有最全的开放给VBA对象模型的调用,特别是最新版Excel2016及以后的使用VBA调用PowerQuery的能力。

就算用上了最新的PIA,16版的Microsoft.Office.Interop.Excel.dll,仍然会对一些新方法如调用Workbook的Queries对象报错。可能使用Visual Studio2019开发会支持,亲测Visual Studio2017不支持。

所以本来预期全程由dotNET脚本来完成的文件路径替换操作,最终不得不再次启用VBA代码的方式,让dotNET脚本调用其VBA代码,实现没有接口方法的情况下仍然可以控制PowerQuery对象模型的操作。

测试数据及其他说明

本次的测试数据,和上篇python篇一样,使用课程表数据,将其转换为标准的一维表数据再上传到数据库中。

每个人使用工具的熟悉度不同,有人喜欢python的方式清洗,但使用PowerQuery来清洗,也是非常方便,大部分都是界面式操作即可完成。

本次不止于一个文件的清洗,使用源文件和存档文件两个文件夹存放要处理的多个文件,多个文件结构是一样的,只有这样才能让PowerQuery的代码通用于多个文件。

区别于一般的PowerQuery的方式仅提供读取数据的功能,本篇使用SSIS还会对数据进行抽取完的归档操作,归档过程中,通过重命名文件名的方式,方便查阅数据归档的操作时间。

存储VBA代码和PowerQuery的M代码的模板文件是【转换结果.xlsm】。

其中PowerQuery的操作,有多个步骤。

具体的M代码如下,定义了一个参数变量filePath,用于在VBA上调用方法来赋值。

因dotNET的接口上缺少此方法,只能在VBA上定义好再调用来赋值。

具体实现

整个流程如下所示,在测试过程中同样发现,当一个Excel的进程多次被使用时,会存在报错现象,所以索性牺牲一点点性能,每次循环都将Excel的进程给清除,并在一开始时也清除所有Excel进程,保证模板文件和其他数据源没有被打开。

干掉所有Excel进程,也是比较简单,写个遍历即可。

核心代码中,使用脚本任务,将当前循环下的文件全路径进行转换,得到归档路径,模板文件路径等。

使用Application.Run方法来调用模板文件里的VBA代码,替换掉PowerQuery里的参数filePath的值。

再使用QueryTable的刷新代码,将替换后的M代码对应的智能表刷新重新加载一下新数据。

每次循环都新开Application对象及最后将其对应的Excel进程给杀掉,释放COM非托管对象。

最终效果

将SSIS包进行执行后,结果如下:

加载过后的文件已归档成功,加上时间戳信息。

数据库数据成功加载。

在源文件中,特意做的不同文件不同标识,证明文件已按预期上传成功。

性能及费用问题

不可否认,使用PowerQuery来清洗数据很慢,同样加上使用COM接口的方式来读取Excel文件也很慢。

但这也没多大关系,毕竟大的数据量不会存在Excel文件中,PowerQuery供一些少量数据清洗,还是非常有优势的。

相对于开发成本来说,使用多工具组合的方式,完成不失去性价比,何况一般数据ETL都在夜间自动化作业,无需人工干预和等待。

同样地使用COM接口,在我们数据处理环节还是可接受的,并没有像业务系统那般存在大量并发性,需忍受COM接口通信的缓慢,在数据处理环节,能够按时准确性完成任务即可。

同时也不必纠结服务器安装个OFFICE软件有什么版权licence问题,一个OFFICE软件,也没几个钱,相对日益昂贵的开发人工成本来说,不算什么,何况OFFICE365的订阅模式,可以一个licence多台电脑使用,仍然合规,不增加成本。

结语

不管黑猫白猫,最终给我们完成任务的都是好猫好方法。在SSIS的平台上,已经没有什么不可能,并且还将可能实现的代价降到最低,充分运用多种工具组合,实现最大化的产出。

同时也是对自身的知识存储的综合多方使用,现在已经可以发现,我们日常积累的众多技能,如VBA、Excel功能、PowerQuery、dotNET、Python、java、WebAPI、数据库、SQL等等,都能在SSIS上得以一展身手,实现强强联合。

笔者未来聚焦在数据领域的分享,不限于Excel,会分享更多Sqlserver、dotNET、Azure、PowerBI等话题,升级数据分析的能力,欢迎继续关注。*

欢迎投稿本站:紫金网 » 多种技能相结合,将Excel(PowerQuery+VBA)的能力嫁接到SSIS中