convert table to Access/Excel; Col 1 = header, Col 2 = data

[Melee]

I have extracted data from a Lotus application to a word file. There are 19 fields that I want to pull out and put into an Access database. The information is structured as follows:

FirstName: 1x
Mid_Init: y
Address1:mmmmm
Address2: kkkkkkk
City: Chicago
State: IL
Zip: 33333
Alt_Address1:

etc.
Then the list repeats with the next contact.

I want to use the first part of the info as the header (ie "FirstName" and "Address1&quot with the right side of the : as the data. Is there a utility and an easy process to do this? I have about 1000 records to convert.

 

[vistor]

You have to manipulate the Word document using find/replace (find, More button, Special button) so that it is tab delimated. Then you can bring into excel to do any necessary clean-up. Then import into Access as a table.

1. I assume in the example you give above that each of the lines has a hard return and that there are two hard returns between "records".

2. You want need the "row headers" - FirstName: and so on at this point, as long you know what your data fields are and all "records" are complete, no missing data in a "field".

3. Replace the two hard returns (paragraph character) with a special set of characters like #%#.

4. Replace the single paragraph character with a tab.

5. Replace the special character from 3. #%# with a single paragraph character and a manuel line break.

this will separate your records into rows and cells for excel. At this point you can check your data in Word. All records have to be on a single line. If need do Page Set up at 11 x 17, landscape, and use a small font size

 

[Melee]

Thanks for your suggestion. I have already been working on that. It is just way too time consuming when the data comes out of a Lotus application and should be more readily formatable.