将某列缺失分隔符的文字读入 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")
函数 import 读入文本，@c 分隔符是逗号，@w 读成序列的序列，~ 是当前行，insert 在指定位置插入成员。
英文版