Solved

Import contect from MS Word into Access or Excel

Posted on 2007-04-09
9
744 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:semerj
9 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18878013
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
 
LVL 11

Expert Comment

by:fanopoe
ID: 18878603
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
 

Author Comment

by:semerj
ID: 18879988
Newspaer style. Only one email address is required.
0
 
LVL 8

Expert Comment

by:nedfine
ID: 18880322
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18880838
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
 

Author Comment

by:semerj
ID: 18881828
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
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 18882208
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
 

Author Comment

by:semerj
ID: 18924160
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
A short article about a problem I had getting the GPS LocationListener working.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now