Format text file so I can import it into excel

Question

I have a huge list of addresses and details I need to convert into an Excel spreadsheet and I think the best way would be to read the data and then write a second document that separates the lines so that they are tab-delimited whilst recognizing blank lines (between data entries) to preserve each separate address.

It is in the format:

AddressA1
AddressB1
Postcode1
Name1
PhoneNumber1

AddressA2
AddressB2
Postcode2
Name2
Name2
PhoneNumber2

AddressA3
AddressB3
Postcode3
Name3
PhoneNumber3

So the difficulty also comes when there are multiple names for a company, but I can hand format those if necessary (ideally they want to take on the same address as each other).

The resulting text document then, wants to be tab-delimited to: Name|AddressA|AddressB|Postcode|Phone Number

I am thinking this would be easiest to do within a simple .bat command? or should I open the list in excel and run a script through that..?

I'm thinking if I can run through where it adds each entry to an array ($address $name etc) then I can use that to build a new text file by writing $name[i] tab $address[$i] etc

There are hundreds of entries and putting it in by hand is proving.. difficult.

I have some experience in MEL (basically C++) so I do understand programming in general, but somewhat at a loss in how .bat and Excel (VB?) handle and define empty lines and tabs.

I know how to do this in PHP, but not sure if the same applies to VB script. I would split the string upon a double line return (blank lines), then iterate the resulting array splitting each item by single line returns, joining those back together with a comma, then join the outer array with line returns. In PHP line returns are done with \n I'm not sure if that same thing applies to VB though, sorry. — Darren Crabb, Oct 16 '13 at 12:19
one file/one-time-task? Use word (even write.exe will do), replace double linefeeds with a "special string" (like `##@@##`), then replace single linefeeds with a tab, then all special strings with a single line feed. Save as `.csv` — Stephan, Oct 16 '13 at 12:39
I managed to get into excel using word (thanks Stephan!) unfortunately because of the extra names and different length of addresses (some just list name and postcode, some have 5 lines etc) It's still a big mess, but no other way around it - going to have to format the rest of the table by hand. Thanks for all your help in any case! Regards, Pete — pchapman, Oct 16 '13 at 14:05
The problem is not reading it into a spreadsheet, but aligning the columns. Correct me, but it looks like a record will consist of at least 4 rows and at most 6. The format will be (required) address 1, (optional) address 2, (required) postal code, (required) name 1, (optional) name 2, (required) phone. Is that right? And does a postal code consist of numbers, letters, or both? And does a phone number consist of digits, dashes, pluses, and parentheses only? — rajah9, Oct 16 '13 at 14:05

score 0 · Answer 1 · answered Oct 16 '13 at 13:43

The first step would be to bring the data into an Excel file. Once the data has been imported, we can re-package it to meet your specs. The first step:

Sub BringFileIn()
    Dim TextLine As String, CH As String
    Close #1
    Open "C:\TestFolder\question.txt" For Input As #1
    Dim s As String
    Dim I As Long, J As Long
    J = 1
    I = 1
    Do While Not EOF(1)
        Line Input #1, TextLine
        Cells(I, J) = TextLine
        I = I + 1
    Loop
    Close #1
End Sub

score 0 · Answer 2 · edited May 23 '17 at 10:25

Any text editor that can do regex search and replace across multiple lines can do the job nicely.

I have written a hybrid JScript/batch utility called REPL.BAT that performs a regex search and replace on stdin and writes the result to stdout. It is pure script that works on any modern Windows machine from XP forward - No 3rd party executable required. Full documentation is embedded within the script.

Assuming REPL.BAT is in your current directory, or better yet, somewhere within your PATH, then:

type file.txt|repl "\r?\n" "\t" mx|repl "\t\t" "\n" x|repl "^(([^\t]*\t){4})([^\t]*)$" "$1\t$3" x >newFile.txt

The above modifies the file in 3 steps and writes the result to a new file, leaving the original intact:

convert all newlines into tabs
convert consecutive tabs into newlines
insert an empty column (tab) before the last column on any line that contains only 5 columns.

score 0 · Answer 3 · answered Oct 16 '13 at 16:30

Here's a method using only Word and Excel. I used the data that you posted. I am assuming that Name2 is the only optional field.

Paste your text into Word.
Replace all paragraph marks with a special characters. (Ctrl-h, Search for ^p, Replace with |)
Replace all line breaks with a different special character. (Ctrl-h, Special character, search for Manual line break, replace with ;)

This is what it looks like in Word:

AddressA1;AddressB1;Postcode1;Name1;PhoneNumber1|AddressA2;AddressB2;Postcode2;Name2;Name2;PhoneNumber2|AddressA3;AddressB3;Postcode3;Name3;PhoneNumber3||

Then convert text to table (Insert -> Table -> Convert text to table), delimiting by ;. This gives 3 rows (plus 2 blank rows) of 1 column.

Then copy the table.

Now in Excel:

Paste the table. (It'll be one row in each row, with all of your fields in column A.)
Convert the text to columns (Data tab, Text to columns, Delimited, check semicolon)
Sort by column E. The phone numbers should be grouped together.
Cut the phone numbers in column E and copy to column F.

Format text file so I can import it into excel

3 Answers3