Import A TXT File Where The Separator Is Missing In A Column To Excel

Problem description & analysis:

We have a comma-separated txt file that has a total of 10 columns. As certain values of the 3rd column do not have separators, that column is missing and the corresponding rows only have 9 columns, as shown in the last rows:

We need to import the txt file to an Excel file. If the 3rd column is missing, use space to fill it and then sort rows by the 1st column:

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
4   01-0120-0137    THE CORNERSTONE BIBLE BAPTIST       202403  2024-03-21 20:36:25.000 225.07  0   0   8
5   03-0302-0481    M. LHULLIER PAWNSHOP        202403  2024-03-21 13:22:17.000 4236.66 0   0   8
6   04-0408-0500    DE LA CENA  JOSE JR.    202403  2024-03-21 21:18:04.000 3125.8  0   0   8
7   14-1403-0361    PALAWAN PAWNSHOP        202403  2024-03-21 08:59:51.000 4601.33 0   0   8
8   15-1522-0095    LUCERNA JAIME SR.   202403  2024-03-21 08:21:23.000 2195.88 0   0   8
9   17-1741-0521    SEVERINO    JOSE JR.    202403  2024-03-21 21:10:48.000 1694.19 0   0   8
10  17-1744-0310    FUENTES FERNANDO SR.    202403  2024-03-21 15:00:49.000 1828.77 0   0   8
11  17-1782-0203    DANIELES    ESTELA # 3  202403  2024-03-21 22:04:16.000 2379.4  0   0   8
12  17-1782-0297    DANIELES    ESTELA # 2  202403  2024-03-21 22:33:34.000 886.61  0   0   8

[For a clearer result table, please visit our Reddit community: https://www.reddit.com/r/esProc_Desktop/comments/1dphpvz/import_a_txt_file_where_the_separator_is_missing/]

Solution:

Use SPL XLL to enter the following formula:

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

As shown in the picture below:

Explanation:

import()function reads the text file; the @c option enables using commas as the separator and the @w option reads data as a sequence of sequences. ~ represents the current row. insert() function inserts a member at a specified position.

Comments

[Judith-Excel-Sharing]

The desired result table is not clear enough due to the code layout on DEV. Please feel free to check the table out in our Reddit community, which is clearer and more specified: reddit.com/r/esProc_Desktop/

[Judith-Excel-Sharing]

Please free to download SPL XLL and explore the data processing journey on your own⬇️
SPL download address: scudata.com/download-Desktop
Plugin Installation Method: c.scudata.com/article/1652061135502
References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...