|
|
本帖最后由 kaixinbuy 于 2023-4-5 10:42 编辑
大多数情况下，表格布局规范，一个sheet一个表格，表头一致，对这样的Excel sheet进行数据整合，只需要使用PowerQuery可以快速完成。
但也有很多情况下，Excel表格是不规范的，格式不统一的表格数据，对这些数据进行抽取，整合非常困难。
例如，在我们的审计项目中，需要合并，抽取，整合上万个表头和内容都不规范的非结构化Excel报价单文件。
这些Excel文档存在的困难点包括：
- 1.布局不规范：每个Excel文件可能包含数十个Sheet（最多可能40-50个sheet），每个Sheet可能包含不固定位置，不固定表头的不固定表格
- （同一个表头字段可能有多种写法，例如产品名的表头可能有：产品名称，产品名，ProductName，名称，商品名，MeteralName等）。
- 2.表头不统一：合并单元格表头、多栏、多行表头、表头顺序不一致、表头写法描述不一致
- 3.内容不规范：同一个公司名、项目名、产品名、产品规格或属性有多种写法和描述方式
基于手工复制粘贴整理这些数据，几乎无法完成任务。最终我们基于 tablemerge.com 非结构化文档结构化抽取模型，使用SQL语言，在数万个sheet上运行SQL抽取： - 自动识别复杂表头和行列：
- 通过定制的SQL解析引擎和正则表达式解析引擎，在SQL中嵌入正则表达式实现抽取
- 自动识别合并单元格、多栏、多行表头
- 自动分析文档布局和版面，自动将文档分块和表格抽取
- 一个SQL在上万个不规范的非结构化Excel文档上运行
- 导出成结构化数据
下面是一个SQL语法和例子：在SQL中模糊抽取和匹配需要的列名：稍微看过SQL的同学都能看懂。
- INSERT into tb_results --抽取结果保存到数据库表
- SELECT name,--抽取字段表头，最大1000个字段name as fieldname,--传统的SQL字段，匹配Excel表头
- `regular_expression` as fieldname,--用正则匹配多个可能的表头值，例如(产品名称|产品名|商品名|ProductName)
- `regular_expression`.match(text) as fieldname,--基于文本匹配表头（默认不写match部分就是匹配文本）
- `regular_expression`.match(text).output(color) as fieldname,--基于字体颜色去匹配表头
- `regular_expression`.match(text).offset(1,0).output(color) as fieldname,--提取某个匹配单元格的右边单元格的值
- FROM excels;--选择需要抽取的1-1000个sheet，同时运行数据抽取
（注意：这个SQL是只能在tablemerge.com客户端才能支持的语法。在传统数据库中是不能运行的）
这样就实现了一次SQL在上千个Sheet上进行模糊抽取和自动布局分析，筛选出所需要的目标表格数据。
同时可以利用SQL函数对抽取结果进行进一步数据清洗和数据转换（类似ETL）。
这里的核心是扩展SQL数据库，对需要抽取的目标字段可以进行扩展和模糊识别。二是自动进行目标表格区域识别，自动对sheet布局进行分析，因为一个sheet上有不固定位置不固定出现的非目标表格和描述内容。让SQL只关心我们需要的目标字段，排除非结构化数据的各种干扰。以及自动处理合并单元格，多栏表头，多行表头等情形，这些都在SQL引擎后台自动处理。
如果您有类似的需求场景，或者有这里未考虑到的情况（例如我们还可以用这个SQL引擎处理逆透视表，矩阵表格，等情况，这里只讨论了最基本的情形），欢迎讨论。
|
评分
-
1
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2023-4-5 10:33
楼主
