将某列缺失分隔符的文字读入 Excel

作者：正经夜光杯 | 2024-07-01 16:32:33

踩

将某列缺失分隔符的文字读入 Excel

有个逗号分隔的 txt，共 10 列，第 3 列有时候缺少分隔符，导致该列缺失，数据不齐只剩 9 列。比如最后两行：

01-0104-0133,MAYO, RONIE #2,202403,2024-03-21 22:51:43.000,1449.49,0.00,0.00,08,6CC6BDAC7E45

17-1782-0203,DANIELES, ESTELA # 3,202403,2024-03-21 22:04:16.000,2379.40,0.00,0.00,08,7C4D66134652

17-1782-0297,DANIELES, ESTELA # 2,202403,2024-03-21 22:33:34.000,886.61,0.00,0.00,08,C93BF124DE14

04-0408-0500,DE LA CENA, JOSE JR.,202403,2024-03-21 21:18:04.000,3125.80,0.00,0.00,08,136E4D2959BA

17-1741-0521,SEVERINO, JOSE JR.,202403,2024-03-21 21:10:48.000,1694.19,0.00,0.00,08,BB1F0814A58F

17-1744-0310,FUENTES, FERNANDO SR.,202403,2024-03-21 15:00:49.000,1828.77,0.00,0.00,08,310EAE3D6DBB

15-1522-0095,LUCERNA, JAIME SR.,202403,2024-03-21 08:21:23.000,2195.88,0.00,0.00,08,79D83EC0F51D

01-0120-0137,THE CORNERSTONE BIBLE BAPTIST,,202403,2024-03-21 20:36:25.000,225.07,0.00,0.00,08,B6D7B504AE79

14-1403-0361,PALAWAN PAWNSHOP,202403,2024-03-21 08:59:51.000,4601.33,0.00,0.00,08,9BD6BD131E9C

03-0302-0481,M. LHULLIER PAWNSHOP,202403,2024-03-21 13:22:17.000,4236.66,0.00,0.00,08,6DB91200E2E6

要求将该 txt 读入 excel，如果第 3 列有缺失，需用空格补齐该列。最后按第 1 列排序：

	A	B	C	D	E	F	G	H	I	J
3	01-0104-0133	MAYO	RONIE #2	202403	2024-03-21 22:51:43.000	1449.49	0	0	8	6CC6BDAC7E45
4	01-0120-0137	THE CORNERSTONE BIBLE BAPTIST		202403	2024-03-21 20:36:25.000	225.07	0	0	8	B6D7B504AE79
5	03-0302-0481	M. LHULLIER PAWNSHOP		202403	2024-03-21 13:22:17.000	4236.66	0	0	8	6DB91200E2E6
6	04-0408-0500	DE LA CENA	JOSE JR.	202403	2024-03-21 21:18:04.000	3125.8	0	0	8	136E4D2959BA
7	14-1403-0361	PALAWAN PAWNSHOP		202403	2024-03-21 08:59:51.000	4601.33	0	0	8	9BD6BD131E9C
8	15-1522-0095	LUCERNA	JAIME SR.	202403	2024-03-21 08:21:23.000	2195.88	0	0	8	79D83EC0F51D
9	17-1741-0521	SEVERINO	JOSE JR.	202403	2024-03-21 21:10:48.000	1694.19	0	0	8	BB1F0814A58F
10	17-1744-0310	FUENTES	FERNANDO SR.	202403	2024-03-21 15:00:49.000	1828.77	0	0	8	310EAE3D6DBB
11	17-1782-0203	DANIELES	ESTELA # 3	202403	2024-03-21 22:04:16.000	2379.4	0	0	8	7C4D66134652
12	17-1782-0297	DANIELES	ESTELA # 2	202403	2024-03-21 22:33:34.000	886.61	0	0	8	C93BF124DE14

使用 SPL XLL，输入公式：

=spl("=file(?).import@cw().(if(~.len()==9,~.insert(3,null),~)).sort(~(1))","d:/data.txt")

Picture1png

函数 import 读入文本，@c 分隔符是逗号，@w 读成序列的序列，~ 是当前行，insert 在指定位置插入成员。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/正经夜光杯/article/detail/777058