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.
Top comments (2)
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/
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...