Judith-Excel-SharingProblem 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.
