北京
润乾公司大数据计算专家
1 回帖  •  1,042 浏览  • 
     ...

将文本转换成 Excel

 
文本(1) 文件(1) 文本文件(1) Excel(13) 导入(6) 转置(13) 集算器(484) Excel处理(15) 文本计算(15) 计算＆ai(537) xml(5) json(6) 多sheet(1)

1    单个行式文本文件转换成Excel

行式文本中一行对应一条数据，是很常见的文件格式，比如下面这个水果价格表。

 

文件名【Fruits.txt】:

Name     UnitPrice

Apple     1.69

Banana   0.69

Peach     0.88

Strawberry     1.97

读入 Excel 后如下所示:

                                                undefined

实现代码:

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 文件

 

2    多个文本文件拼入一个Excel的一个Sheet中

如果有多份价格表，需要把它们读入一个 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

希望导入后的结果是:

 undefined

 

实现代码:

A

1

=file("Fruits.txt").import@t()

2

=file("Meats.txt").import@t()

3

=A1|A2

4

=file("Foods.xlsx").xlsexport@t(A3)

3    多个文本文件生成多个Sheet的Excel

 

有些时候会有这样的需求，就是多个文本文件分别读入 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

 

 

导入后: undefined undefined

  

 

实现代码:

A

B

1

[Fruits.txt,Meats.txt]

2

for A1

=file(A2).import@t()

3

==file("Foods.xlsx").xlsexport@ta(B2 ; A2)

 

 

4    把多行变成一行并生成Excel

某商场统计了一些回头客的联系方式，格式如下:

 

文件名【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 后如下所示:

 undefined

实现代码:

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 文件

 

5    把一行拆成多行再生成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 文件，结果如下所示：

undefined

实现代码：

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)

      

 

6    将文本数据读进指定Excel模板的特定位置

某商场统计了部分重点关注的顾客购买某些易耗品的次数和数量，分别存在不同的文本文件中:

文件名【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 模板的样子如下：

undefined

 

填好后的结果如下所示:

 undefined

实现代码:

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)

 

7    数据转置后生成Excel

某商场统计了部分重点关注的顾客购买某些易耗品的次数，为文本文件格式，如下:

 

文件名【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 后如下所示:

undefined

这是一个比较常见的数据转置的需求，实现代码如下:

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 文件

 

8    数据转置后生成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 后如下所示:

undefined

这是一个比较常见的数据转置的需求，实现代码如下:

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)

 

9    从复杂格式的文本文件生成Excel

在数据处理中，有时数据来源于格式复杂的文本文件，要从中提取有用的数据，需从以下几点来思考:

1、  确定要提取数据的结构，有哪几个要提取的字段

2、  确定一行文本是否包含有效数据

3、  从有效数据行中找到提取各字段的规律

不同文本数据的规律可能不一样，但总是要有规律才能解析。

 

现有一个文本格式的客户报价单数据item.txt如下图所示:

undefined

横线之前的行是复杂的表头，之后的每一行是一条报价记录，记录之间有空行。图中所示只是一个表头和报价记录区，这样的区域在文本文件中会不断地重复出现。红框所示分别是 Unit Price 和 Exp. Date 字段列，中间还有 Quotation Number、Customer Code、Customer Name 字段列，各列数据之间都是空格。

 

现在需要把文本文件中的报价单数据提取出来，存到Excel文件中如下图所示:

undefined

 

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 中

 

10      从xml生成Excel

某商场从系统下载了一份客户信息数据，为 xml 格式，如下所示:

 undefined

现需要转成 Excel，转成 Excel 后结果如下所示:

undefined

 

这是一个比较常见的数据从 xml 读出，再导出成 Excel 的需求，实现代码如下:

A

B

1

=file("Customers.xml").read()

/把整个文本文件读入成一个大字符串

2

=xml(A1).xml.row

/xml()把字符串解析成序列 / 序表，然后访问序列中的成员，读出中间核心数据序表

3

=file("Customers.xlsx").xlsexport@t(A2)

/将结果输出到 Excel 文件

 

11      从json生成Excel

某商场从系统下载了一份客户信息数据，为 json 格式，如下所示:

 undefined

现需要转成 Excel，转成 Excel 后结果如下所示:

undefined

 

这是一个比较常见的数据从 json 读出，再导出成 Excel 的需求，实现代码如下:

A

B

1

=file("Customers.json").read()

/把整个文本文件读入成一个大字符串

2

=json(A1)

/json()把字符串解析成序表

3

=file("Customers.xlsx").xlsexport@t(A2)

/将结果输出到 Excel 文件

 


文本(1) 文件(1) 文本文件(1) Excel(13) 导入(6) 转置(13) 集算器(484) Excel处理(15) 文本计算(15) 计算＆ai(537) xml(5) json(6) 多sheet(1)