Import contect from MS Word into Access or Excel

I have a conatct list that is in MS Word. the content is in 2 columns and each person's information is seperated my a space within each column. I want this information in a databse and was wondering if there is a way to import it into access or excel. Below is a sample of the data I have to work with. The actual Word doc is in 2 columns. There are 82 pages of this, so I don' twant to have to do it manually. All we really need it the name,  full address, and the email address.

1  PHR  H2  BH Assoc.
Mr. William Kreutzjans Sr.
Mrs. Elizabeth Kreutzjans Sr.
2020 Eden Derry Drive
Fort Mitchell, KY 41017
H 859-341-0996
O 859-341-0050
OF 859-341-4625
C 859-760-6565
C 859-393-1467
williamk@ashleybuilders.com
waktrip@ekit.com
  2411 & 1

2  PHR  H3  BH Assoc.
Mrs. Bobbi Gasser
Attn: Dave Gasser - P.O. Box 161565
Big Sky, MT 59716
C 406-570-1324 Dave
U 239-395-8780
gashouse88@aol.com - Bobbi
mgm894@cs.com - Dave
Reservations:
2 Arbor Lane Apt. #108
Evanston, IL 60201
847-733-2301
November, March & April
P.O. Box 984
Captiva, FL 33924
BH #2 239-395-8780
  5122 & 2

3  PHR  H3  BH Assoc.
Mr. Steven Kohn
Mrs. Ellen Kohn
566 South Meadow Vista Drive
Evergreen, CO 80439
H 303-670-1023
O 303-679-1638
OF 303-379-1643
C 303-503-3125
skohn888@aol.com
horsegal52@aol.com





4  OOP  H2  BH Assoc.
Mr. Nathan Dardick
1720 Maple Avenue Apt #2750
Evanston, IL 60201
H 847-328-9595
HF 847-328-9554
O 312-207-2400
OF 312-946-7304
U 239-395-0094
UF 239-395-4392
ndardick@aol.com
Jodardick@aol.com
Board Member

5  OOP  H3  BH Assoc.
Mr. Jean DeDouvre
P.O. Box 685
Captiva, FL 33924
H 239-472-3865
HF 239-472-0269
O 314-215-2822
OF 314-755-9604
U 239-472-0381
dedouvre@aol.com
matador@noos.fr
  5 & 1121 & 1073

6  PHR  H3  BH Assoc.
Mr. Roger Hruby
Mrs. Nadeane Hruby
500 State Street
Chicago, IL 60411
H 630-655-1470
HF 630-655-1432
O 708-891-3456
OF 314-755-9604
rhruby@cfcintl.com
Home
6505 Elm Street
Burr Ridge, IL 60521
semerjAsked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
What I have given you is a Word macro, and runs entirely in Word, creating a Word table in a new document. This avoids the need to code in the database name and the table name, so you should be able to copy and paste the result to any three-field table in any Access database.

To add the code, open the VBA editor with Alt+F11, just as in Access. From the project pane on the left, highlight the Normal project and, from the menu, do Insert/Module. Paste the code into the right-hand code pane.

To run, ensure that your source document is the active document, toggling to the Word window with Alt+F11 in necessary. Come back to the code, put the cursor in the procedure code and press F5.

This new version converts the carriage returns in the address to commas which are more appropriate to a single record in Access.

Sub Parse3()
    Dim doc1 As Document
    Dim doc2 As Document
    Dim tbl As Table
    Dim para As Paragraph
    Dim rw As Row
    Dim strAddress As String
    Dim phase As Integer
    Const phSeekingNextBlock = 0
    Const phSeekingName = 1
    Const phSeekingAddress = 2
    Const phSeekingEmail = 3
   
    Set doc1 = ActiveDocument
    Set doc2 = Documents.Add
    Set tbl = doc2.Tables.Add(doc2.Range, 1, 3)
    tbl.Cell(1, 1).Range.Text = "Name"
    tbl.Cell(1, 2).Range.Text = "Address"
    tbl.Cell(1, 3).Range.Text = "eMail"
    phase = phSeekingName
    Set rw = tbl.Rows.Add
    For Each para In doc1.Paragraphs
        Select Case phase
            Case phSeekingNextBlock
                If Len(para.Range.Text) > 1 Then
                   Set rw = tbl.Rows.Add
                   phase = phSeekingName
                End If
            Case phSeekingName
                Select Case para.Range.Words(1)
                    Case "Mr", "Mrs"
                        rw.Cells(1).Range.Text = Left$(para.Range.Text, Len(para.Range.Text) - 1)
                        phase = phSeekingAddress
                End Select
            Case phSeekingAddress
                 Select Case Trim$(para.Range.Words(1))
                    Case "Mr", "Mrs"
                    Case "H", "HF", "O", "OF", "C", "U", "UF"
                        phase = phSeekingEmail
                        If Right$(strAddress, 2) = ", " Then
                            strAddress = Left$(strAddress, Len(strAddress) - 2)
                        End If
                        rw.Cells(2).Range.Text = strAddress
                        strAddress = ""
                    Case Else
                        strAddress = strAddress & Replace(para.Range.Text, Chr$(13), ", ")
                End Select
           
            Case phSeekingEmail
                If InStr(para.Range.Text, "@") > 0 Then
                    rw.Cells(3).Range.Text = Left$(para.Range.Text, Len(para.Range.Text) - 1)
                    phase = phSeekingNextBlock
                End If
        End Select
    Next para
End Sub


0
 
GrahamSkanRetiredCommented:
It doesn't look easy. There doesn't seem to be enough consistence to parse accurately/

You ask for the name, the address and the email address, but in the sample there are instances of two of each of them. How would you want those dealt with?

In what way are there two columns? Do you mean snaking (newspaper-style) columns, or is the information in a table? What does the space that you mention separate?

If is gets too difficult to explain, you might try uploading a larger zipped sample here:

www.ee-stuff.com 
0
 
fanopoeCommented:
farm it out to a service that will re-type it for you. I am REALLY good at parsing data, and you have some real difficulties with automating a parsing of that list. Additionally, based on what you are asking for there are some questions such as: which of the two names do you want?, which of multiple emails do you want?, etc... A service will get it nicely parsed out for you quickly and accurately in a way that you can then import and use as you like.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
semerjAuthor Commented:
Newspaer style. Only one email address is required.
0
 
nedfineCommented:
if you are willing to use perl for this, i can give you a script to parse the word doc and write to excel.
0
 
GrahamSkanRetiredCommented:
For simplicity, this macro actually creates a table in a new document. We would need to specify a database and table to load the data directly into Access.

The resulting table can be copied and pasted into Access or Excel.

Sub Parse2()
    Dim doc1 As Document
    Dim doc2 As Document
    Dim tbl As Table
    Dim para As Paragraph
    Dim rw As Row
    Dim strAddress As String
    Dim phase As Integer
    Const phSeekingNextBlock = 0
    Const phSeekingName = 1
    Const phSeekingAddress = 2
    Const phSeekingEmail = 3
   
    Set doc1 = ActiveDocument
    Set doc2 = Documents.Add
    Set tbl = doc2.Tables.Add(doc2.Range, 1, 3)
    tbl.Cell(1, 1).Range.Text = "Name"
    tbl.Cell(1, 2).Range.Text = "Address"
    tbl.Cell(1, 3).Range.Text = "eMail"
    phase = phSeekingName
    Set rw = tbl.Rows.Add
    For Each para In doc1.Paragraphs
        Select Case phase
            Case phSeekingNextBlock
                If Len(para.Range.Text) > 1 Then
                   Set rw = tbl.Rows.Add
                   phase = phSeekingName
                End If
            Case phSeekingName
                Select Case para.Range.Words(1)
                    Case "Mr", "Mrs"
                        rw.Cells(1).Range.Text = Left$(para.Range.Text, Len(para.Range.Text) - 1)
                        phase = phSeekingAddress
                End Select
            Case phSeekingAddress
                 Select Case Trim$(para.Range.Words(1))
                    Case "Mr", "Mrs"
                    Case "H", "HF", "O", "OF", "C", "U", "UF"
                        phase = phSeekingEmail
                        rw.Cells(2).Range.Text = strAddress
                        strAddress = ""
                    Case Else
                        strAddress = strAddress & para.Range.Text
                End Select
           
            Case phSeekingEmail
                If InStr(para.Range.Text, "@") > 0 Then
                    rw.Cells(3).Range.Text = Left$(para.Range.Text, Len(para.Range.Text) - 1)
                    phase = phSeekingNextBlock
                End If
        End Select
    Next para
End Sub


0
 
semerjAuthor Commented:
GrahamSkan,

I do not use MS office products very much except for Access. Where do I put that code? Program and place? Do I create a table in Access with the columns name, address, etc? If so, what do I call the table? do the database and Word doc need to live in the same directory?
0
 
semerjAuthor Commented:
GrahamSkan:

It worked well. Now I have 3 columns. Name, full address, email address.

Is there a to break out the 2nd column into address, city, state zip? If my client wants to do mailing labels then as is, the addrees field will probably all display on one line.

If you want me to open another question for this so youget more points I will.
0
All Courses

From novice to tech pro — start learning today.