...

Import a txt file where separator is missing in a column to Excel

 

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:

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

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

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

Use SPL XLL to enter the following formula:

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

Picture1png

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

Source:https://stackoverflow.com/questions/78253871/automatically-separate-a-data-without-a-delimiter-into-the-designated-column-whe


 
 •  119 View  •