行式文本中一行对应一条数据，是很常见的文件格式，比如下面这个水果价格表。
文件名【Fruits.txt】:
Name UnitPrice
Apple 1.69
Banana 0.69
Peach 0.88
Strawberry 1.97
读入Excel后如下所示:
实现代码:
|
|
A |
|
1 |
=file("Fruits.txt").import@t() |
|
2 |
=file("Fruits.xlsx").xlsexport@t(A1) |
更复杂一些的情况，如果文本文件的数据为了对齐好看，多加了一些tab空格，如下所示：
Name UnitPrice
Apple 1.69
Banana 0.69
Peach 0.88
Strawberry 1.97
那么代码还需要增加去空格处理，实现代码如下：
|
|
A |
B |
|
1 |
=file("Fruits.txt").import@s() |
/读入文本文件，@s表示整行读入，不拆字段 |
|
2 |
=A1.(~._1.split@t()) |
/逐行用tab分隔符拆，@t表示删除多余的空白分隔符 |
|
3 |
=create(${A2(1).concat@c()}) |
/把第一行作为列名产生序表 |
|
4 |
=A3.record(A2(to(2,A2.len())).conj()) |
/把后续的行拼接成序列再按顺序放入序表中 |
|
5 |
=file("Fruits.xlsx").xlsexport@t(A1) |
/输出至excel文件 |
如果有多份价格表，需要把它们读入一个Excel文件的同一个Sheet中，如:
文件名【Fruits.txt】:
Name UnitPrice
Apple 1.69
Banana 0.69
Peach 0.88
Strawberry 1.97
文件名【Meats.txt】:
Name UnitPrice
mutton 7.69
Pork 4.58
Chicken 5.77
Duck 6.89
Beef 7.96
希望导入后的结果是:
实现代码:
|
|
A |
|
1 |
=file("Fruits.txt").import@t() |
|
2 |
=file("Meats.txt").import@t() |
|
3 |
=A1|A2 |
|
4 |
=file("Foods.xlsx").xlsexport@t(A3) |
有些时候会有这样的需求，就是多个文本文件分别读入Excel不同的Sheet，且用文本文件的名字作为Sheet的名字，如:
文件名【Fruits.txt】:
Name UnitPrice
Apple 1.69
Banana 0.69
Peach 0.88
Strawberry 1.97
文件名【Meats.txt】:
Name UnitPrice
mutton 7.69
Pork 4.58
Chicken 5.77
Duck 6.89
Beef 7.96
导入后:
实现代码:
|
|
A |
B |
|
1 |
[Fruits.txt,Meats.txt] |
|
|
2 |
for A1 |
=file(A2).import@t() |
|
3 |
|
==file("Foods.xlsx").xlsexport@ta(B2 ; A2) |
某商场统计了一些回头客的联系方式，格式如下:
文件名【Customers.txt】:
Peter
Mobile:13302111756
Facebook:asd003
Address:Room...,Unit...,Building...,...Road,...District,...City,...Prov,China
Mark
Tel:83781385
Email:lisi@sina.com
Twitter:13445245
Address:Room...,Unit...,Building...,...Road,...District,...City,...Prov,China
Alice
Mobile:12235345434
Facebook:dfg546
Twitter:456547567
Address:Room...,Unit...,Building...,...Road,...District,...City,...Prov,China
Lily
Mobile:18902131756
Facebook:liu073
Tel:82781395
Email:liuliu@google.com
Twitter:12225245
Address:Room...,Unit...,Building...,...Road,...District,...City,...Prov,China
分析数据后发现，由于每个客户拥有的联系方式不同，所以最后统计出来的数据里，每个客户的行数不一样，现在希望把这个文本文件转成Excel后如下所示:
实现代码:
|
|
A |
B |
|
|
1 |
=file("Customers.txt").import@i() |
/读入文本文件 |
|
|
2 |
=1 |
|
|
|
3 |
=A1.group@o(if(~==null,(A2=A2+1,A2),A2)).(~.select(~!=null)) |
/按空行作为分隔标准，拆分分组 |
|
|
4 |
=create(Name,Mobile,Facebook,Tel,Email,Twitter,Address) |
/产生结果序表 |
|
|
5 |
for A3 |
=A5.(~.split(":")) |
/用冒号拆分每一行 |
|
6 |
|
=B5.(if(~.len()==1,"\""+~(1)+"\":Name","\""+~(2)+"\":"+~(1))).concat@c() |
/无冒号的行作为姓名，其它行以冒号左边为列名，右边为列值 |
|
7 |
|
>A4.insert(0,${B6}) |
/将拆分结果插入序表记录 |
|
8 |
=file("Customers.xlsx").xlsexport@t(A4) |
/结果输出到Excel文件 |
|
某商场从系统中导出了一张客户表，数据存在txt文件中，如下所示：
|
Name Mobile Facebook Tel Email Twitter Address Peter 13302111756 asd003 Room...,Unit...,Building...,...Road,...District,...City,...Prov,China Mark 83781385 lisi@sina.com 13445245 Room...,Unit...,Building...,...Road,...District,...City,...Prov,China Alice 12235345434 dfg546 456547567 Room...,Unit...,Building...,...Road,...District,...City,...Prov,China Lily 18902131756 liu073 82781395 liuliu@google.com 12225245 Room...,Unit...,Building...,...Road,...District,...City,...Prov,China |
因为字段数太多，且有些字段为空值，这样看起来很不方便，需要把一行拆成多行，每一行按照“字段名:字段值”的格式，如果值为空则该行不要显示。转完后导出到Excel文件，结果如下所示：
实现代码：
|
|
A |
B |
C |
D |
|
1 |
=file("CustomerTable.txt").import@t() |
|||
|
2 |
=A1.fname() |
=[] |
|
|
|
3 |
for A1 |
=[] |
|
|
|
4 |
|
for A2 |
if(A3.${B4}!=null) |
>B3.insert(0,B4+":"+string(A3.${B4})) |
|
5 |
|
>B3.insert(0,null) |
|
|
|
6 |
|
>B2.insert(0:B3) |
|
|
|
7 |
=file("CustomerTable.xlsx").xlsexport(B2) |
|||
某商场统计了部分重点关注的顾客购买某些易耗品的次数和数量，分别存在不同的文本文件中:
文件名【Egg.txt】:
Name Times Quantity
Peter 7 42
Mark 2 2
Alice 6 6
Lily 3 6
文件名【Tissue.txt】:
Name Times Quantity
Peter 5 5
Mark 2 10
Alice 6 24
Lily 6 24
文件名【Bread.txt】:
Name Times Quantity
Peter 1 8
Mark 9 36
Alice 9 81
Lily 2 18
现在需要把文本文件的数据填入到Excel模板的对应位置上，Excel模板的样子如下：
填好后的结果如下所示:
实现代码:
|
|
A |
B |
|
1 |
[Egg,Tissue,Bread] |
|
|
2 |
=file("Template.xlsx").xlsopen() |
|
|
3 |
for A1 |
=file(A3+".txt").import@t() |
|
4 |
|
=A2.xlscell(cellname(6,#A3*2),1;B3.(Times).concat("\n")) |
|
5 |
|
=A2.xlscell(cellname(6,#A3*2+1),1;B3.(Quantity).concat("\n")) |
|
6 |
|
=A2.xlscell(cellname(10,#A3*2),1;B3.(Times).sum()) |
|
7 |
|
=A2.xlscell(cellname(10,#A3*2+1),1;B3.(Quantity).sum()) |
|
8 |
=file("Template.xlsx").xlswrite(A2) |
|
某商场统计了部分重点关注的顾客购买某些易耗品的次数，为文本文件格式，如下:
文件名【Times.txt】:
Name Times Commodity
Peter 0 Egg
Mark 6 Egg
Alice 6 Egg
Lily 2 Egg
Peter 9 Tissue
Mark 9 Tissue
Alice 8 Tissue
Lily 8 Tissue
Peter 6 Bread
Mark 6 Bread
Alice 8 Bread
Lily 3 Bread
现在需要转成Excel，转成Excel后如下所示:
这是一个比较常见的数据转置的需求，实现代码如下:
|
|
A |
B |
|
1 |
=file("Times.txt").import@t() |
/读入文本文件 |
|
2 |
=A1.pivot(Name;Commodity,Times;"Egg":"Egg","Tissue":"Tissue","Bread":"Bread") |
/转置 |
|
3 |
=file("Times.xlsx").xlsexport@t(A2) |
/结果输出到Excel文件 |
某商场统计了部分重点关注的顾客购买某些易耗品的次数和数量，为文本文件格式，如下:
文件名【TimesQuantity.txt】:
Name Times Quantity Commodity
Peter 7 7 Egg
Mark 9 81 Egg
Alice 3 21 Egg
Lily 7 35 Egg
Peter 1 2 Tissue
Mark 3 24 Tissue
Alice 7 0 Tissue
Lily 2 12 Tissue
Peter 1 4 Bread
Mark 8 40 Bread
Alice 2 8 Bread
Lily 3 24 Bread
现在需要转成Excel，便于做各种统计，转成Excel后如下所示:
这是一个比较常见的数据转置的需求，实现代码如下:
|
|
A |
B |
|
1 |
=file("TimesQuantity.txt").import@t() |
|
|
2 |
=A1.pivot(Name;Commodity,[Times,Quantity];"Egg":"Egg","Tissue":"Tissue","Bread":"Bread") |
/交叉点两列的情况，用序列的方式处理两列 |
|
3 |
=A2.new(Name,Egg(1):EggTimes,Egg(2):EggQuantity,Tissue(1):TissueTimes,Tissue(2):TissueQuantity,Bread(1):BreadTimes,Bread(2):BreadQuantity) |
/最后再new一下，把序列中的成员读成字段 |
|
4 |
=file("TimesQuantity.xlsx").xlsexport@t(A3) |
|
在数据处理中，有时数据来源于格式复杂的文本文件，要从中提取有用的数据，需从以下几点来思考:
1、 确定要提取数据的结构，有哪几个要提取的字段
2、 确定一行文本是否包含有效数据
3、 从有效数据行中找到提取各字段的规律
不同文本数据的规律可能不一样，但总是要有规律才能解析。
现有一个文本格式的客户报价单数据item.txt如下图所示:
横线之前的行是复杂的表头，之后的每一行是一条报价记录，记录之间有空行。图中所示只是一个表头和报价记录区，这样的区域在文本文件中会不断地重复出现。红框所示分别是Unit Price和Exp. Date字段列，中间还有Quotation Number、Customer Code、Customer Name字段列，各列数据之间都是空格。
现在需要把文本文件中的报价单数据提取出来，存到Excel文件中如下图所示:
1、 观察并发现文本中的规律
我们发现这个文本有这样的规律:
(1)、少于136个字符的行都没有有效信息，可以跳过
(2)、所需数据位于每行59列至136列
(3)、把每行有效信息部分按空格为分隔符拆分，若第1个拆分值是数值类型，则此行是报价记录，否则可跳过。第1个拆分值是Unit Price列，第2个是Quotation Number列，第3个是Customer Code列，最后1个是Contract
Expiry Date列，第4个至倒数第2个用空格连接起来是Customer Name列。
2、 编写脚本:
|
A |
B |
C |
|
|
1 |
=create(Customer_Code,Customer_Name,Quotation_No,Unit_Price,Contract_Expiry_Date) |
||
|
2 |
=file("E:/txt2Excel/item.txt").read@n() |
||
|
3 |
for A2 |
if len(A3)<136 |
next |
|
4 |
=right(left(A3,136),-58) |
=B4.split@tp() |
|
|
5 |
if !ifnumber(C4(1)) |
next |
|
|
6 |
=C4.m(4:C4.len()-1).concat(" ") |
||
|
7 |
>A1.insert(0,C4(3),B6,C4(2),C4(1),C4(C4.len())) |
||
|
8 |
=file("E:/txt2Excel/item.xlsx").xlsexport@t(A1) |
||
A1 创建目标数据集
A2 打开报价单文本文件item.txt，读入文件内容，选项@n表示每一行读成一个字符串
A3 循环处理每一行文本，实施前面找出来的规律
B3C3 如果本行长度小于136，则跳过此行
B4 提取本行数据的第59至136列
C4 对B4中提取出来的数据按空白符进行拆分，选项t表示拆分后去除两端的空白，选项p表示把拆分后的串解析成对应的数据类型
B5C5 如果C4拆分出的第一个值不是数值类型，则跳过此行
B6 将C4拆分出来的第4个值到倒数第二个值用空格连接成串
B7 将C4拆分出的第3个值、B6、C4拆分出的第2个值、第1个值、最后一个值按顺序插入到A1的新记录中
A8 将所有提取的数据保存到Excel文件item.xlsx中
某商场从系统下载了一份客户信息数据，为xml格式，如下所示:
现需要转成Excel，转成Excel后结果如下所示:
这是一个比较常见的数据从xml读出，再导出成Excel的需求，实现代码如下:
|
|
A |
B |
|
1 |
=file("Customers.xml").read() |
/把整个文本文件读入成一个大字符串 |
|
2 |
=xml(A1).xml.row |
/xml()把字符串解析成序列/序表，然后访问序列中的成员，读出中间核心数据序表 |
|
3 |
=file("Customers.xlsx").xlsexport@t(A2) |
/将结果输出到Excel文件 |
某商场从系统下载了一份客户信息数据，为json格式，如下所示:
现需要转成Excel，转成Excel后结果如下所示:
这是一个比较常见的数据从json读出，再导出成Excel的需求，实现代码如下:
|
|
A |
B |
|
1 |
=file("Customers.json").read() |
/把整个文本文件读入成一个大字符串 |
|
2 |
=json(A1) |
/json()把字符串解析成序表 |
|
3 |
=file("Customers.xlsx").xlsexport@t(A2) |
/将结果输出到Excel文件 |