将某列缺失分隔符的文字读入 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 在指定位置插入成员。
英文版