Reformatting a tab delimited text file

MikeW · Jul 19, 2014#12014-07-19T18:35+00:00

Really didn't know how to title this thread.

The following is how I am going to receive a tab delimited file. No hope for getting it properly formatted. This is all in one line. There are any number of names separated by a semi-colon in one cell, followed by corresponding addresses also separated by a semi-colon. In the sample below, there are 4 names, 4 addresses:

GPO; Upper Dorset Street Post Office; Parnell Street Post Office; Summerhill Post Office <TAB> O'Connell Street Lower, Dublin 1; 58 Upper Dorset Street, Dublin 1; 97 Parnell Street, Dublin 1; Summerhill Parade, Dublin 1

I highlighted where there is a single tab in the line. There are a minimum of 200 lines like this up to 400 or so lines.

I need to figure out a F/R or a script that will reformat this list like the following:

GPO <TAB> O'Connell Street Lower, Dublin 1
Upper Dorset Street Post Office <TAB> 58 Upper Dorset Street, Dublin 1
Parnell Street Post Office <TAB> 97 Parnell Street, Dublin 1
Summerhill Post Office <TAB> Summerhill Parade, Dublin 1

In reality, there is no space before/after the tab but I thought it provided a little clarity for viewing.

The closest I can come is to have a block of Names followed by a block of addresses, which works once back into Excel I can manually move them to their appropriate place. I was hoping that I didn't need to do it that way though.

Thank you for any pointers on how to proceed.

Mike

Mofi · Jul 19, 2014#22014-07-19T21:02+00:00

This is no problem with using a small UltraEdit macro:

Code: Select all

InsertMode
ColumnModeOff
HexOff
Top
PerlReOn
Find MatchCase RegExp " *([\t;]) *"
Replace All "\1"
Loop 0
Find MatchCase RegExp ";([^\r\n\t;]+)(\t.*);(.+)$"
Replace All "\2\r\n\1\t\3"
IfNotFound
ExitLoop
EndIf
EndLoop

The first Perl regular expression Replace All just removes all spaces left or right of a semi-colon or a tab character.

More interesting is the second Perl regular expression Replace All executed in a loop until nothing replaced anymore as this makes the main job.

;([^\r\n\t;]+)\t(.*);(.+)$ finds

a semi-colon which is removed during the replace,
and a string not containing a carriage return, line-feed, horizontal tab or semi-colon which is marked for backreferencing by \1 in replace string,
the next character must be a horizontal tab character which results in not matching any string between two semi-colons or between last semi-colon and end of line and therefore matching always only the string between last semi-colon before the horizontal tab in the line and the horizontal tab character by the preceding expression,
the string after the horizontal tab character before last semi-colon in the line marked with the tab character before as second string for backreferencing by \2
the last semi-colon in the line which is removed during the replace,
and last the string after last semi-colon in the line to end of line marked as third string for backreferencing by \3.

The matching pair of strings is moved to a new line below with an additionally inserted tab character between with the replace string.

So the macro converts your example line first to:

GPO;Upper Dorset Street Post Office;Parnell Street Post Office;Summerhill Post OfficetabO'Connell Street Lower, Dublin 1;58 Upper Dorset Street, Dublin 1;97 Parnell Street, Dublin 1;Summerhill Parade, Dublin 1

The file contains after first run of the second regular expression:

GPO;Upper Dorset Street Post Office;Parnell Street Post OfficetabO'Connell Street Lower, Dublin 1;58 Upper Dorset Street, Dublin 1;97 Parnell Street, Dublin 1
Summerhill Post OfficetabSummerhill Parade, Dublin 1

The file contains after second run of the second regular expression:

GPO;Upper Dorset Street Post OfficetabO'Connell Street Lower, Dublin 1;58 Upper Dorset Street, Dublin 1
Parnell Street Post Officetab97 Parnell Street, Dublin 1
Summerhill Post OfficetabSummerhill Parade, Dublin 1

And the file contains after third run:

GPOtabO'Connell Street Lower, Dublin 1
Upper Dorset Street Post Officetab58 Upper Dorset Street, Dublin 1
Parnell Street Post Officetab97 Parnell Street, Dublin 1
Summerhill Post OfficetabSummerhill Parade, Dublin 1

The fourth run of second regular expression Replace All does not replace anything and the loop exits.

Of course you can run the 2 replaces also manually by pressing Replace All button on second regular expression until the message is displayed that nothing could be found.

MikeW · Jul 19, 2014#32014-07-19T21:44+00:00

Works a treat. Thank you very much, Mofi.

Mike