【摘要】
本文介绍如何将各种结构的 Excel 文件解析成结构化数据,如普通行式、复杂表头、自由格式、交叉表、主子表、大文件等,并用 esProc SPL 举例实现。请点击Excel 文件结构化解析示例了解详情
在数据分析业务中,经常要把Excel文件数据结构化解析以后再进行计算或导入关系数据库,但许多Excel文件的格式并不规整,而且文件结构也多种多样,导致编程进行结构化的工作量会比较大,而且很难通用,每次都要针对文件格式进行分析后再进行开发。
本文将介绍如何进行各种格式的 Excel 文件结构化解析,如普通行式、复杂表头、自由格式、交叉表、主子表、大文件等,并提供用 esProc SPL 编写的代码示例。esProc 是专业的数据计算引擎,其采用的 SPL 中有完善的 Excel 文件处理函数,进行结构化解析及后续的计算、入库等操作非常方便。
1. 普通行式
这是一种最简单的文件格式,文件中每行都是一条数据记录,更常见的是第一行是列标题。
示例:在学生成绩文件scores.xlsx中,查询各班语文平均成绩。部分数据如下图:
esProc SPL脚本如下:
A | 注释 | |
1 | =file(“e:/excel/scores.xlsx").xlsimport@t() | 读取文件,@t选项把第一行读作标题 |
2 | =A1.groups(Class;avg(Chinese):avg_Chinese) | 按班级分组,计算各班语文平均成绩 |
3 | =file("e:/excel/class_avg_c.xlsx").xlsexport@t(A2) | 将计算结果存入新的文件 |
2. 复杂表头
多数时候,Excel文件的表头格式并不简单,往往是由多行构成的,比如有表标题、项目名称、填表人、填写日期、页码等信息。解析这种格式的文件时,需要跳过复杂的表头,指定从数据行的位置开始读取,然后指定结构化后的数据各列的列名。
示例:在项目造价文件itemPrices.xlsx中,计算项目总造价,部分数据如下图:
esPr