SPL 读写文件

本文介绍SPL读写文本文件和Excel文件的基本方法,包括简单的文件目录操作。

文本文件

读写结构化文本

结构化文本的格式比较规范,即每行一条记录,列之间用分隔符区分。SPL可用import/export函数读写结构化文本。

比如:ordersNT.txt存储订单记录,列之间用制表符tab分隔,业务意义依次为:订单ID、客户编号、销售ID、订单金额、订单日期。部分数据如下:

26    TAS  1       2142.4     2009-08-05

33    DSGC        1       613.2       2009-08-14

84    GC    1       88.5 2009-10-16

133  HU   1       1419.8     2010-12-12

32    JFS   3       468.0       2009-08-13

39    NR   3       3016.0     2010-08-21

43    KT    3       2169.0     2009-08-27

将该表按客户编号的字母顺序从小到大的排序,相同的客户编号再按订单金额从大到小排序,最后保持原格式存入新文件。部分结果应当如下:

136  ARO 25    899.0       2009-12-16

16    BDR 27    2464.8     2009-07-23

81    BDR 29    1168.0     2010-10-14

108  BDR 12    480.0       2010-11-15

139  BDR 30    166.0       2010-12-18

93    BON 6       2564.4     2010-10-29

106  BSF  27    10741.6   2009-11-13

要计算出上述结果,可用如下SPL脚本:


A

1

=file("D:/data/ordersNT.txt").import()

2

=A1.sort(_2,-_4)

3

=file("D:/data/ordersNT_sort.txt").export(A2)

A1A3:读入、写出结构化文本文件。

A2:函数sort进行排序,_2_4分别代表第2列和第4列,默认按顺序排序,负号代表逆序。

 

SPL也可以处理带列名(标题)的文本文件。比如orders.txt的第1行为列名,部分数据如下:

OrderID   Client       SellerId    Amount   OrderDate

26    TAS  1       2142.4     2009-08-05

33    DSGC        1       613.2       2009-08-14

84    GC    1       88.5 2009-10-16

133  HU   1       1419.8     2010-12-12

32    JFS   3       468.0       2009-08-13

39    NR   3       3016.0     2010-08-21

43    KT    3       2169.0     2009-08-27

同样对该文件进行排序,结果带列名写入新文件,可用如下SPL脚本:


A

1

=file("D:/data/orders.txt").import@t()

2

=A1.sort(Client,-Amount)

3

=file("D:/data/orders_sort.txt").export@t(A2)

A1A3:选项@t表示带列名读入、写出文本文件。

A2:用列名而不是序号进行排序。

 

函数import/export默认的分隔符tab,选项@c表示以逗号为分隔符(常用于csv文件)。如果遇到其他特殊分隔符,SPL同样可以处理。比如orders_semi.txt||为分隔符,可用如下SPL脚本:


A

1

=file("D:/data/orders_semi.txt").import@t(;,"||")

2

=A1.select(Amount>=1000 && Amount<2010)

3

=file(""D:/data/orders_semi_select.txt").export@t(A2;"||")

 

函数export默认的功能是将数据写入新文件,或覆盖同名文件,但有时候我们需要在原文件后面追加结构相同的新数据,这种情况使用@a选项:

=file(""D:/data/orders_semi_select.txt").export@at(A2;"||")

   

读写字符串序列

         有些文本的格式不规范,不能直接进行结构化计算,但可以读为字符串序列。这样的半结构化数据格式繁多,下面用多行记录举例,说明SPL读写字符串序列的一般方法。

文件3lines.txt中每3行的前2行对应一条记录,第3行无用,部分数据如下:

26    TAS  1       2142.4

2009-08-05

some comment

33    DSGC        1       613.2

2009-08-14

some comment

27    TAS  1       2142.4

2009-08-05

some comment

         去掉文件中的无用行,结果写入新文件,可用如下SPL脚本:


A

1

=file("D:/data/3lines.txt").read@n()

2

=A1.step(3,1,2)

3

=file("D:/3lines_reuslt.txt").write(A5)

A1:读入文本文件。@n表示按行读成序列,序列的每个成员对应一行。

A2:对序列A1每隔3个成员取第1个成员和第2个成员。

A3:将序列写入文本文件,序列的每个成员对应一行。

 

 

Excel文件

读写结构化表格

结构化的Excel表格比较规范, SPL可用xlsimport/xlsexport函数进行读写。

比如:ordersNT.xlsx中第1sheet的各列的业务意义依次是订单ID、客户编号、销售ID、订单金额、订单日期。部分数据如下:


A

B

C

D

E

1

26

TAS

1

2142.4

2009/8/5

2

33

DSGC

1

613.2

2009/8/14

3

84

GC

1

88.5

2009/10/16

4

133

HU

1

1419.8

2010/12/12

5

32

JFS

3

468

2009/8/13

6

39

NR

3

3016

2010/8/21

7

43

KT

3

2169

2009/8/27

将该表按客户编号的字母顺序从小到大的排序,相同的客户编号再按订单金额从大到小排序,最后保持原格式存入新Excel

要计算出上述结果,可用如下SPL脚本:


A

1

=file("D:/data/ordersNT.xlsx").xlsimport()

2

=A1.sort(_2,-_4)

3

=file("D:/data/ordersNT_sort.xlsx").xlsexport(A2)

A1A3:读写excel的第1sheet。如果要读入指定sheet,可用xlsimport(;sheet序号或sheet),如果要写入指定的sheet,可用xlsexport(A2; sheet序号或sheet)

 

SPL也可以处理带列名(标题)的结构化表格,方法与文本文件类似。比如orders.xlsx部分数据如下:


A

B

C

D

E

1

OrderID

Client

SellerId

Amount

OrderDate

2

26

TAS

1

2142.4

2009/8/5

3

33

DSGC

1

613.2

2009/8/14

4

84

GC

1

88.5

2009/10/16

5

133

HU

1

1419.8

2010/12/12

6

32

JFS

3

468

2009/8/13

7

39

NR

3

3016

2010/8/21

8

43

KT

3

2169

2009/8/27

对该文件进行排序,结果带列名写入新文件,可用如下SPL脚本:


A

1

=file("D:/data/orders.xlsx").xlsimport@t()

2

=A1.sort(Client,-Amount)

3

=file("D:/data/orders_sort.xlsx").xlsexport@t(A2)

 

         有时候表格的前几行无用需要跳过,比如下面的数据:


A

B

C

D

E

1

orders from 2009 to 2010

2

3






4

OrderID

Client

SellerId

Amount

OrderDate

5

26

TAS

1

2142.4

2009/8/5

6

33

DSGC

1

613.2

2009/8/14

7

84

GC

1

88.5

2009/10/16

8

133

HU

1

1419.8

2010/12/12

9

32

JFS

3

468

2009/8/13

可用下面的SPL脚本,从第4行开始读取:

=file("D:/data/   orders.xlsx").xlsimport@t(;,4)

 

有时候我们需要在原表格后面追加结构相同的新数据,这种情况下只需使用@a选项,形如:

=file(""D:/data/orders_sort.xlsx").xlsexport@at(A2)

如果原表格的最后一个有内容的行设置了外观属性,则追加的数据会继承该行的风格。比如原表格D列风格为#,##0.00E列风格为mmm-dd-yyyy。如下表:


A

B

C

D

E

1

OrderID

Client

SellerId

Amount

OrderDate

2

26

TAS

1

2,142.40

Aug-05-2009

3

33

DSGC

1

613.20

Aug-14-2009

4

84

GC

1

88.50

Oct-16-2009

则追加数据后,结果如下表:


A

B

C

D

E

1

OrderID

Client

SellerId

Amount

OrderDate

2

26

TAS

1

2,142.40

Aug-05-2009

3

33

DSGC

1

613.20

Aug-14-2009

4

84

GC

1

88.50

Oct-16-2009

5

133

HU

1

1,419.80

Dec-12-2010

6

32

JFS

3

468.00

Aug-13-2009

如果原表格最后一个有内容的行之后的第一个空行设置了风格属性,则追加的数据会继承该行的风格属性。利用这个特点,我们可以实现按指定格式从无到有的输出数据。比如先建立空白Excel,设置第2行的D列风格为#,##0.00E列风格为mmm-dd-yyyy


A

B

C

D

E

1

OrderID

Client

SellerId

Amount

OrderDate

2






再向该空白表格追加数据,结果如下:


A

B

C

D

E

1

OrderID

Client

SellerId

Amount

OrderDate

2

133

HU

1

1,419.80

Dec-12-2010

3

32

JFS

3

468.00

Aug-13-2009

读写二维字符串序列

         有些Excel的格式不够规范,没有明确的列属性,这种情况下可当作二维字符串序列来处理。

比如下面的key-value数据:


A

B

C

D

E

1



A=123

B=456

C=789

2

A=678

B=783

A=900

U=89


3

A=330

Y=67

B=890

C=311

F=19

         现在要将上述数据按keyvalue拆分为2列,并按keyvalue排序,最后写入新的Excel。结果如下:


A

B

1

A

123

2

A

330

3

A

678

4

A

900

5

B

456

6

B

783

7

B

890

8

C

311

9

C

789

10

F

19

11

U

89

12

Y

67

要计算出上述结果,可用如下SPL脚本:


A

1

=file("D:/data/keyvalue.xlsx").xlsimport@w()

2

=A1.conj().select(~)

3

=A2.(~.split("="))

4

=A3.sort(~(1),~(2))

5

=file("D:/data/keyvalue_result.xlsx").xlsexport@w(A4)

A1:读入Excel@w表示读为二维字符串序列,整体是大序列,每行既是大序列的成员,同时也是个小序列;行内每格是小序列的成员。

A2:将二维序列合并为一维序列,并去除可能的空白格,比如A1B1E2

A3:将字符串序列拆分为keyvalue

A4:按keyvalue排序。

A5:将结果写入新Excel@w表示写入序列的序列。

读写单元格

前面的内容都是以表格或序列为单位读写Excel,有时候我们也会以精细的单元格为单位读写Excel

比如:下面的表格在第1行有编辑者和编辑日期。


A

B

C

D

E

1

editor:emily




date:Dec-30-2011

2

OrderID

Client

SellerId

Amount

OrderDate

3

26

TAS

1

2,142.40

Aug-05-2009

4

33

DSGC

1

613.20

Aug-14-2009

5

84

GC

1

88.50

Oct-16-2009

6






7






现在要将编辑者和编辑日期复制到第7行对应的位置上,结果应当如下:


A

B

C

D

E

1

editor:emily




date:Dec-30-2011

2

OrderID

Client

SellerId

Amount

OrderDate

3

26

TAS

1

2,142.40

Aug-05-2009

4

33

DSGC

1

613.20

Aug-14-2009

5

84

GC

1

88.50

Oct-16-2009

6






7

editor:emily




date:Dec-30-2011

要计算出上述结果,可用如下SPL脚本:


A

B

1

=file("D:/data/cell.xlsx")


2

=A1.xlsopen()


3

=str=A2.xlscell("A1")

=A2.xlscell("A7";str)

4

=str=A2.xlscell("E1")

=A2.xlscell("E7";str)

5

=A1.xlswrite(A2)


A2:以对象方式打开Excel文件。

A3:读Excel对象的A1格,赋予变量str。默认从第1sheet读,如果想读取指定sheet里的A1单元格,可用A2.xlscell("A1",sheet序号或sheet)

B3:将A1格内容写入A7格。类似地,如果想在指定sheet里写入A7单元格,可用A2.xlscell("A7",sheet序号或sheet;str)

A4-B4:读E1格内容,并写入E7

A5:将Excel对象写入Excel文件。

 

上面例子中,需要读入的A1-E1是连续单元格,需要写入的A7-E7也是连续单元格,对于这种连续单元格的读写,SPL可以用更简化的代码来实现。比如上例的代码可以改成:


A

1

=file("cell.xlsx")

2

=A1.xlsopen()

3

=arry=A2.xlscell@w("A1":"E1")

4

=A2.xlscell("A7":"E7";arry)

5

=A1.xlswrite(A2)

A3:以序列的格式读入连续单元格

A4:向连续单元格写入序列,序列的每个成员对应一个单元格。既可以用序列向连续单元格写入数据,也可以用\t\r分隔的字符串,其中\t表示横向分隔,\r表示纵向(跨行)分隔。

sheet处理

使用Excel对象,不仅可用读写单元格,也可以处理多个sheet,下面举例说明。

Excel用多个sheet存储订单表格,每个sheet的格式相同,但数量和名称不定。现在要将这些订单整理到新Excel中,每个sheet存一年的数据。

要计算出上述结果,可用如下SPL脚本:


A

1

=file("orders_sheet.xlsx").xlsopen()

2

=A1.(stname).(A1.xlsimport@t(;~)).conj()

3

=A2.group(string(year(OrderDate)):name;~:content)

4

=file("orders_result.xlsx").xlsopen@w()

5

=A3.(A4.xlsexport@t(~.content;string(~.name)))

6

=A4.xlsclose()

A1:以对象方式打开源Excel文件。

A2:遍历每个sheet,读取每个sheet的订单,再合并所有订单。A1.(stname)表示Excel对象A1中取出所有的sheet名。

A3:将订单按年份分组。

A4:以对象方式打开目标Excel文件。@w表示写模式,文件不存在时将新建文件。

A5:遍历A3的每组(每年)订单,依次写入到A4的新sheet中。

A6@w模式打开的Excel对象必须用函数xlsclose关闭。

文件和目录

解析文件名

         函数filename可解析出文件名的不同部分:


A

B

1

=filename("D://file/test.dfx")

输出带扩展名的文件名:test.dfx

2

=filename@e("D://file/test.dfx")

输出扩展名:dfx

3

=filename@n("D://file/test.dfx")

输出无扩展名的文件名:test

4

=filename@d("D://file/test.dfx")

输出路径:D://file

         已知文件名的各部分,可用concat函数拼出全路径。比如:


A

B

1

=concat("D://file/","test",".dfx")

输出全路径: D://file/test.dfx

遍历文件

某目录下有一批Excel文件,这些文件的第1sheet存储着订单数据,且结构相同。现在要将这些订单合并到新的Excel中。

要计算出上述结果,可用如下SPL脚本:


A

1

=direcotory@p("d:/data/*.xlsx")

2

=A1.conj(file(~).xlsimport@t())

3

=file("d:/result.xlsx").xlsexport@t(A2)

A1:搜索目录下所有扩展名为xlsx的文件名,@p表示返回全路径。

A2:按文件名循环读入Excel,再合并数据。

函数direcotory还有更多功能,比如用@s选项递归搜索子目录,用@d选项列出子目录,用@r删除目录,用@m创建目录。

 

系统目录

前面的例子中,我们使用全路径来访问数据文件,如果配置了系统目录中的main path,则可以将main path当作根目录,用相对路径来访问数据文件。具体的配置界面参见下图:

imagepng

比如,未配置main path时,脚本为:file("d:/data/p/orders.xlsx").xlsimport()

配置main path=d:\data,则脚本可写作:file("p/orders.xlsx").xlsimport()

如果没有配置main path而直接用相对路径,则实际的main path是启动集算器的目录,直接启动集算器时(或通过快捷方式启动),该目录是[集算器安装目录\bin],双击dfx文件间接启动集算器时,该目录是dfx文件所在的目录。使用下面的脚本可验证main pathfilename@p("")

除了main path之外,集算器还有其他较为重要的系统目录。

temp:计算引擎存放临时文件的目录,如果没有设置,则默认使用操作系统临时目录。

searching path:脚本文件的根目录,包括了main path,可以设置多个目录,目录之间用分号分隔。