Converting some text to an Excel Document...

mateo1221 · Sep 2nd 2003

I have a Word document that more or less looks the same as the following:

~~~~~~~~~~~~~~~~~~~~~~~~~~~

N012326144|3809939|GONZALES|RAMON|||860-826-5189||155 MARKET ST||NEW BRITAIN|CT|06051|624|39.99|M|2160397||2002-03-04 19:53:55| N012326116|3806263|DIOT|MATTHEW|||203-380-2051||115 KNOWHON ST||STRATFORD|CT|06615|744|34.99|M|2160397||2002-03-04 19:41:12| N010714977|3786629|HORTON, JR|RANDOL|||661-871-9353||1318 PESANTE RD||BAKERSFIELD|CA|93306|674|39.99|M|2160387|A|2002-03-04 19:21:25| N010715665|3803399|||AGS STAINLESS|GARY GIFFIN|206-842-9492||7873 NE DAY RD||BAINBRIDGE ISLAND|WA|98110|705|29.99|M|2159888||2002-03-04 19:02:47| N012326134|3806820|FAULKNER|BRYUAN|||781-605-1060||27 BLAINE ST||MALDEN|MA|02148|677|34.99|M|2160397||2002-03-04 19:01:38| N012321878|3804713|BUTCHER|LETA||LETA BUTCHER|520-579-5656||3450 W MONTGOMERY ST||TUCSON|AZ|85742|761|29.99|M|2159799||2002-03-04 18:56:19| N013069240|3807573|SCOTT|GARFIELD|||305-653-1615||1370 NW 191 ST||MIAMI|FL|33169|700|33.99|M|2149567||2002-03-04 18:55:53|

~~~~~~~~~~~~~~~~~~~~~~~~~~~

It apparently WAS a spreadsheet of some kind at one point, but somehow got messed up and I need to convert it back. Each new entry is denominated by the N######### unique number at the beginning. The file is about 5.5mb and 1800 pages long. It APPEARS to me that the " | " character is being used to separate the cells, and wherever values were not available, you see back to back, or sometimes even three " | " characters. My FIRST thought was to replace all of the " | "'s with " , "'s to make a CSV (Comma Separated Value) file and go from there... but I'm lost after that.

The problem I was having w/ the CSV thing was that there is no RETURN character at the end of each entry, before the next N######### value, so when you put it all directly into a spreadsheet, it places it all on one line...

Any suggestions? Or any better approaches? Thanks in advance for your help...!

**Andy Pope** · Sep 2nd 2003

Hi mateo1221 and welcome,

Try this,
Save the word file as a plain text file, Text1.txt.
From Excel run this macro.

Code

Sub LoadFile()
'
' Break single text stream on the 19 |
'
    Dim strBuf As String
    Dim intIndex As Integer
    Dim intPos As Integer
    Dim intUnit As Integer
    
    intUnit = FreeFile
    Open "Text1.txt" For Input As intUnit
    ' read the file stream in one go
    strBuf = Input(LOF(intUnit), #intUnit)
    Close intUnit
    
    intUnit = FreeFile
    Open "Text1x.txt" For Output As intUnit
    intPos = InStr(strBuf, "|")
    Do While intPos &gt; 0
        intIndex = intIndex + 1
        If intIndex = 19 Then
            Print #intUnit, Left(strBuf, intPos + 1)
            strBuf = Mid(strBuf, intPos + 2)
            intIndex = 0
            intPos = 0
        End If
        intPos = InStr(intPos + 1, strBuf, "|")
    Loop
    Close intUnit
End Sub

Display More

Now try opening the new text file in excel using | as the field separator.

Cheers
Andy

Dreamboat · Sep 7th 2003

Turn Word's show/hide button on.
Select all the text up to and including the paragraph return that follows it.
Hit Table-Convert, Text to Table.
Type the pipe | character into the "Separate text at" box. Tell it you want 19 columns. Hit Ok.
This created a perfect table when I did it using the text you pasted in here.
Copy and paste right into Excel.

Or

Save the file as TXT and use Excel's text import wizard to open it. This MAY not work as well.

Converting some text to an Excel Document...

Participate now!

Share

Users Viewing This Thread