converting text to table in Word

[Richard Craigie]

Hi everyone, my problem is this;

I have a very large text file with many line entries each containing
distinct fields. I want to convert this text data into a table where each
line occupies a single row and each field is in a separate cell. Each line
starts with an upper case letter I and may have from 14 to 17 fields each
separated by one or more blank spaces as seen in list 1 below. It can be seen
that some fields overrun onto the next line with the last few fields. I am
attempting to manipulate the text to get it into the form shown in list 2
i.e. all fields are on the one line. When in this form I can then use MS
Words convert "text to table" function to convert into a nice table.

The problem I have is that I cannot manipulate the raw data to get into the
form in list 2. When I copy/cut the raw data from notepad and paste into Word
a paragraph symbol is placed after each line. This means that it’s not
possible to use the convert “text to table†function directly on this data
since the converter can’t distinguish between lines starting with I and those
lines that are overflowing from the previous line.

Does anyone know how I can sort to get into list 2 format? Is it possible to
use a macro perhaps to resolve this? Or a different application altogether?


Many thanks in advance,

Richard Craigie



LIST 1

I BN_OP_2B 1 12/01/04 14:11:59 AGA Recovery: MVF Slot
Alarm outlet 4
I BN_OP_3B 1 12/01/04 14:18:53 AGA Recovery: MVF Slot
outlet 1
I BN_OP_3B 1 12/01/04 14:18:53 AGA Failure: MVF Slot
outlet 1
I BN_OP_3B 1 12/01/04 14:18:53 FLT 3:Satellite Port3
I BN_OP_3B 3 12/01/04 14:18:58 Hub Failure - Level 03
I BN_OP_3B 1 12/01/04 14:19:19 AGA Recovery: MVF Slot
US outlet 1
I BN_OP_3B 1 12/01/04 14:19:19 CLR 3:Satellite Port3
I BN_OP_3B 1 12/01/04 14:19:20 AGA Failure: MVF Slot
outlet 1
I BN_OP_3B 1 12/01/04 14:19:20 FLT 3:Satellite Port3
I BN_OP_3B 1 12/01/04 14:28:43 AGA Failure: MVF Slot
outlet 1


LIST 2

I BN_OP_2B 1 12/01/04 14:11:59 AGA Recovery: MVF Slot Alarm outlet 4
I BN_OP_3B 1 12/01/04 14:18:53 AGA Recovery: MVF Slot outlet 1
I BN_OP_3B 1 12/01/04 14:18:53 AGA Failure: MVF Slot outlet 1
I BN_OP_3B 1 12/01/04 14:18:53 FLT 3:Satellite Port3 Outlet 1 Type1
: Slot 8
I BN_OP_3B 3 12/01/04 14:18:58 Hub Failure - Level 03 : FNC Slot 02
I BN_OP_3B 1 12/01/04 14:19:19 AGA Recovery: MVF Slot US outlet 1
I BN_OP_3B 1 12/01/04 14:19:19 CLR 3:Satellite Port3 Outlet 1 Type1
: Slot 8
I BN_OP_3B 1 12/01/04 14:19:20 AGA Failure: MVF Slot outlet 1
I BN_OP_3B 1 12/01/04 14:19:20 FLT 3:Satellite Port3 Outlet 1 Type1
: Slot 8
I BN_OP_3B 1 12/01/04 14:28:43 AGA Failure: MVF Slot outlet 1

 

[Richard Craigie]

Please note List 2 didn't print correctly the first time, it should look like
this:


LIST 2

I BN_OP_2B 1 12/01/04 14:11:59 AGA Recovery: MVF Slot Alarm outlet 4
I BN_OP_3B 1 12/01/04 14:18:53 AGA Recovery: MVF Slot outlet 1
I BN_OP_3B 1 12/01/04 14:18:53 AGA Failure: MVF Slot outlet 1
I BN_OP_3B 1 12/01/04 14:18:53 FLT 3:Satellite Port3 Outlet 1 Type1
Slot 8
I BN_OP_3B 3 12/01/04 14:18:58 Hub Failure - Level 03 : FNC Slot 02
I BN_OP_3B 1 12/01/04 14:19:19 AGA Recovery: MVF Slot US outlet 1
I BN_OP_3B 1 12/01/04 14:19:19 CLR 3:Satellite Port3 Outlet 1 Type1
Slot 8
I BN_OP_3B 1 12/01/04 14:19:20 AGA Failure: MVF Slot outlet 1
I BN_OP_3B 1 12/01/04 14:19:20 FLT 3:Satellite Port3 Outlet 1 Type1
Slot 8
I BN_OP_3B 1 12/01/04 14:28:43 AGA Failure: MVF Slot outlet 1

 

[Graham Mayor]

It is not clear from your message what the layout of the original text
document is (eg are the orphan words Alarm Outlet 4 etc. supposed to be
separate or are they wrapped from the previous line with the mail reader)
nor what you want it to end up like in terms of column numbers and what they
contain, so while it should be possible to do what you want, it is difficult
to advise how to proceed. The likely solution would be to use the wildcard
replace function to separate the text into distinct fields that Word can
recognise and to separate each 'record' (table row) with a paragraph mark
before using the convert to table function or copying into Excel.
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Suzanne S. Barnhill]

Even if the lines are wrapping in Word, you can still convert to a table
provided each "line" is a paragraph. You'll need to replace the appropriate
spaces between fields with tabs, then use Table | Convert | Text to Table,
separating at tabs.

 

[Klaus Linke]

The likely solution would be to use the wildcard

replace function to separate the text into distinct
fields that Word can recognise [...]

To remove spurious paragraph marks, you can probably remove all ¶ marks
that aren't followed by "I":

Edit > Find, check "Match wildcards",
Find what: ^13([!I])
Replace with: \1

Regards,
Klaus