Solved

Import contect from MS Word into Access or Excel

Posted on 2007-04-09
9
766 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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
 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

623 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