Excel 文件结构化解析示例


【摘要】
本文介绍如何将各种结构的 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中,计算项目总造价,部分数据如下图:

..

esProc SPL脚本如下:


A

注释

1

=file(“e:/excel/itemPrices.xlsx").xlsimport(;1,5)  

参数“1,5”表示读第一个sheet,从第5行开始读,一直读到sheet结尾

2

=A1.rename(#1:No,#2:ItemCode,#3:ItemName,#4:Unit,#5:Quantity,#6:Price,#7:Sum)  

修改各列的列名

3

=A2.sum(Sum)

计算项目总造价

 

3.  自由格式

一条数据记录分散在多行,字段列值单元格紧跟在列名单元格后面或下面,可能有跨列或跨行的合并单元格。但每条记录所占的行数以及对应行结构是相同的。循环读取时要以每条记录所占行数为单位组成一条记录。

示例:把自由格式的员工信息文件employee.xlsx存入到数据库表employee中,部分数据如下图:

..

esProc SPL脚本如下:


A

B

C

1

=create(ID,Name,Sex,Position,Birthday,Phone,Address,PostCode)  

2

=file(“e:/excel/employe.xlsx").xlsopen()  

3

[C,C,F,C,C,D,C,C]

[1,2,2,3,4,5,7,8]


4

for

=A3.(~/B3(#)).(eval($[A2.xlscell(]/~/")"))


5


if len(B4(1))==0

break

6


>A1.record(B4)


7


>B3=B3.(~+9)


8

=connect(“db”)



9

=A8.update(A1,employee)



10

=A8.close()



A1   创建列名为“IDNameSexPositionBirthdayPhoneAddressPostCode”的空序表

A2   打开Excel数据文件

A3   定义雇员信息所在单元格列号序列

B3   定义雇员信息所在单元格行号序列

A4   for循环读取每个雇员信息

B4   A3.(~/B3(#))先算出当前雇员单元格编号序列,再读出这些单元格值组成雇员信息序列。第一次循环时为[C1,C2,F2,C3,C4,D5,C7,C8],第二次循环时为[C10,C11,F11,C12,C13,D14,C16,C17]……每次行号加9$[A2.xlscell(]"A2.xlscell("相同,都是表示一个字符串,它的好处是在IDE中编写程序时,如果A2单元格的编号发生了变化,$[A2.xlscell(]中的A2会自动变化,比如在A2前插入了一行,这个表达式就会变成$[A3.xlscell(],而用引号的话,就不会自动变了。

B5   判断雇员ID值是否为空,为空则退出循环,结束读数

B6   将一条雇员信息存入A1序表尾

B7   让雇员信息的行号序列都加上9,读取下一条雇员信息

A8-A10   连接数据库,将雇员信息存入数据库表employee,关闭数据库

读取出来的A1单元格数据如下图所示:

..

 

4.  交叉表

交叉表是统计学中常见的一种矩阵式表格,可以清晰地表达两个变量间的数量关系。交叉表数据逐行读入后,需要以某个列变量为基准,另一个变量及交叉值进行行转置;或者以某个行变量为基准,另一个变量及交叉值进行列转置。

示例:将订单地区与货运方式交叉表cross.xlsx文件解析成结构化数据,文件数据如下图所示。

..

esProc SPL脚本如下:


A

注释

1 =file(“e:/excel/cross.xlsx").xlsimport@t(;1,2) 读入文件第一个sheet,从第2行开始,第2行作列名

2

=A1.rename(#1:Type)

第一列列名改为Type

3

=A2.pivot@r(Type;Area,Amount)  

A2Type为基准进行转置,选项@r表示列转行,转换后新的列名分别为AreaAmount

A3格的部分数据如下图所示:

..

 

5.  主子表

每个sheet是一条主表记录,同时sheet中也包含N条子表记录。文件中有多少主表记录,就有多少个sheet。对这种主子表结构的数据,需要创建两个数据表分别保存主表和子表的记录。

示例:在员工信息登记表文件staff.xlsx中,每个sheet有员工信息及他的家庭成员信息,请将员工信息及家庭成员信息分别解析成两个结构化数据表。其中一个sheet如下图:

..

esProc SPL脚本如下:


A

B

C

1

=create(IDCard,Name,Sex,Birthday,Nation,Phone,Depart,Home,Marital,Entry)  

2

=create(IDCard,Name,Relation,Workplace,Phone)  

3

[B4,B3,D3,F3,H3,F4,H4,B5,F5,H5]  

4

=file(“e:/excel/staff.xlsx").xlsopen()  

5

for   A4

=A3.(eval($[A4.xlscell(]/~/",\""/A5.stname/"\")"))  

>A1.record(B5)  

6


=A4.xlsimport@t(Family,Name,Relation,Workplace,Phone;A5.stname,6)  


7


=B6.rename(Family:IDCard)  

>B7.run(IDCard=B5(1))  

8


>A2.insert@r(0:B7)  


A1   创建列名分别为IDCardNameSexBirthdayNationPhoneDepartHomeMaritalEntry的空序表,用于保存主表员工信息

A2   创建列名分别为IDCardNameRelationWorkplacePhone的空序表,用于保存子表员工家庭成员信息

A3   定义主表员工信息所在单元格序列

A4   打开Excel数据文件

A5   循环读取Excel文件各sheet数据

B5   读取员工信息序列

C5   B5读取的员工信息保存到序表A1

B6   从第6行开始读取员工家庭成员信息,只读指定的5FamilyNameRelationWorkplacePhone

B7   B6序表的Family列改名为IDCard

C7   B7序表的IDCard列赋值为员工信息中的IDCard

B8   B7中的员工家庭成员信息追加到序表A2

A1读到的部分数据如下:

..

A2读到的部分数据如下:

..

 

6.  大文件

大文件结构化解析及计算的相关原理可参看《大文件上的结构化数据计算示例》,那篇文章是以文本文件为例,本文在此以Excel文件为例再作示范。

示例:在订单信息大数据文件orders.xlsx中,统计各地区的订单金额总和。部分数据如下图:

..

esProc SPL脚本如下:


A

注释

1

=file(“e:/excel/orders.xlsx").xlsimport@tc()

读文件,@t表示第一行是列名,@c表示返回游标

2

=A1.groups(Area;sum(Amount))

按地区分组,统计各地区订单总金额

 

本文内容改编自《SPL CookBook》,《SPL CookBook》中还有更多敏捷计算示例。


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾