Solved

Convert rtf to csv to outlook contacts

Posted on 2004-08-10
12
528 Views
Last Modified: 2008-03-04
I'm trying to convert 110 pages of pdf addresses to a csv file or into my outlook contacts.  I can save the pdf file as an rtf file in word as shown below.

LastName, FirstName PhoneNumber
Fax..........................FaxNumber
CompanyName
CompanyAddress
City, State  ZipCode

As you can see there are only four lines of data and then the next record begins with commas in the same place and spaces in the same place.  My question is how can I get this data to know I want all of the last names to be in column one of excel.  All of the first names to be placed into column two and so on.  From there I can move columns around to make it line up to be easily imported into my contacts.  Please Help, I'm awarding full points.

Thanks
0
Comment
Question by:MortgageGuy
  • 7
  • 5
12 Comments
 
LVL 5

Expert Comment

by:slycoder
ID: 11768338
I'm working on a solution, but you'll have to save the RTF file to a TXT file for Excel to parse it at a file level.

BTW - do you have unusual names in your list - for instance see -

slycoder's suggestion at -

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21088706.html

RE:
Personally I always separate First Name, Middle, Last Name, Generation (Jr, Sr II, III) into separate columns then I have a formula column that concatinates them appropriately, for instance -

A        B     C         D            E
John   M.   Smith   II         =c1 & " " & d1 & ", " & a1 & " " & b1

This will work for almost every name variation you come across.

(Except maybe the artist formerly known as Prince!)
0
 

Author Comment

by:MortgageGuy
ID: 11773525
I have no problem saving the file as a txt from rtf.  This list should be a one time thing, although different instances may arise that will have me to something similar.  Let me know what you come up with.
0
 

Author Comment

by:MortgageGuy
ID: 11774539
I think what I'm looking for is the opposite of concatinate.  Something that will take everything in cell A1 and spread it into B1, C1, D1 and E1.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 5

Expert Comment

by:slycoder
ID: 11774767
yep that's basically parsing, with spaces you "FIND" the space, then using LEFT, MID, and RIGHT functions, you create the formulas.

A                B                          C                           D
sly coder     =FIND(" ",A1,1)     =LEFT(A1,B1-1)     =MID(A1,B1+1,9999)

Here's a program for you - three things
1) open up a blank Excel worksheet
2) press Alt+F11, 'INSERT' a Module
3) paste the program in the Module
4) Change the v_Filename$ variable to the appropriate path

to run the program - close out the VBA screen (I suggest saving the blank spreadsheet at this point)
Press Alt+F8 to select your macro and double click on 'Read_TXT_File'

0
 
LVL 5

Expert Comment

by:slycoder
ID: 11774770

Public Sub Read_TXT_File()

'***************************************
' CHANGE THIS FILENAME

v_Filename$ = "c:\sourcedocument.txt"

'***************************************
   
   
    Dim i_filenum As Integer
   
    i_filenum = FreeFile
   
   
    ' Always start in A3 - I figured you have title rows
    Range("A3").Select
   

    Open v_Filename$ For Input As #i_filenum
       
        While Not EOF(i_filenum)
       
            ' Read line 1 - NAME
           
            ReadNamePhone i_filenum
            ReadFax i_filenum
            ReadCompany i_filenum
            ReadAddress i_filenum
            ReadArea i_filenum
           
            ' go to next row of cells for next writing
            ActiveCell.Offset(1, 0).Select
           
        Wend
       
    Close #1
End Sub


Public Function ReadNamePhone(i_locfilenum)
   
    ' this reads the line
    Name_Phone$ = readline(i_locfilenum)
   
    ' Parse out Last Name by searching for the ',' and taking the leftmost items
    LnameEndPos = InStr(1, Name_Phone$, ",")
    Lname$ = Trim(Left(Name_Phone$, LnameEndPos - 1))
   
    ' write in first cell
    ActiveCell.Offset(0, 0).Value = Lname$
   
    ' Parse Firstname and Phone
    FnameStart = InStr(LnameEndPos + 1, Name_Phone$, " ")
    FnameEnd = InStr(FnameStart + 1, Name_Phone$, " ")
   
    Fname$ = Mid(Name_Phone$, FnameStart + 1, FnameEnd - FnameStart)
   
    Phone$ = Mid(Name_Phone$, FnameEnd + 1)
   
    ' write in second cell
    ActiveCell.Offset(0, 1).Value = Trim(Fname$)
   
    ' write in third cell
    ActiveCell.Offset(0, 2).Value = Trim(Phone$)

End Function

Public Function ReadFax(i_locfilenum)
    ' this reads the line
    Fax$ = readline(i_locfilenum)
    ' remove the first three letters 'fax'
       
    Fax$ = Trim(Mid(Fax$, 4))
   
    ' remove all periods
    Fax$ = Replace(Fax$, ".", "")

    ' write in fourth cell
    ActiveCell.Offset(0, 3).Value = Fax$

End Function
Public Function ReadCompany(i_locfilenum)
    ' this reads the line
    Company$ = readline(i_locfilenum)
   
    ' write in fifth cell
    ActiveCell.Offset(0, 4).Value = Trim(Company$)
   
End Function

Public Function ReadAddress(i_locfilenum)
    ' this reads the line
    Address$ = readline(i_locfilenum)
   
    ' write in sixth cell
    ActiveCell.Offset(0, 5).Value = Trim(Address$)
   
End Function

Public Function ReadArea(i_locfilenum)
    ' this function is the same as the Name_Phone, just changes variables and cell destinations
   
    ' this reads the line
    Location$ = readline(i_locfilenum)
   
    ' Parse out Last Name by searching for the ',' and taking the leftmost items
    CityEndPos = InStr(1, Location$, ",")
    City$ = Trim(Left(Location$, CityEndPos - 1))
   
    ' write in seventh cell
    ActiveCell.Offset(0, 6).Value = City$
   
    ' Parse Firstname and Phone
    StateStart = InStr(CityEndPos + 1, Location$, " ")
    StateEnd = InStr(StateStart + 1, Location$, " ")
   
    State$ = Mid(Location$, StateStart + 1, StateEnd - StateStart)
   
    Zip$ = Mid(Location$, StateEnd + 2)
   
    ' write in eighth cell
    ActiveCell.Offset(0, 7).Value = Trim(State$)
   
    ' write in ninth cell
    ActiveCell.Offset(0, 8).Value = Trim(Zip$)

End Function


Public Function readline(i_locfilenum) As String
    Line Input #i_locfilenum, s_line
    readline = s_line
End Function


0
 
LVL 5

Expert Comment

by:slycoder
ID: 11774780
BTW - guess it's "four things"  I can program, I just can't count.
0
 

Author Comment

by:MortgageGuy
ID: 11775412
You are almost my hero.  It will import the first 6 or 8 records and then it has a run time error '5' Invalid procedure or argument.  If I click on debug this line is highlighted  City$ = Trim(Left(Location$, CityEndPos - 1))

What do you think?
0
 
LVL 5

Expert Comment

by:slycoder
ID: 11775573
When you click on "debug" and mouse over "Location$", it should show the value in question.  May be an empty field. if you edit the text file with the correct city on the 6th or 8th record, it should continue flowing until it hits another "empty field".


let me know if I can be of further assistance.
0
 

Author Comment

by:MortgageGuy
ID: 11775888
Its looking good, but my zip code is only displaying the last three digits.  Some of them show five and some of them show 12345-xxxx.
0
 
LVL 5

Accepted Solution

by:
slycoder earned 500 total points
ID: 11776172
This should fix it.  let me know if you have tabs, tabs threw a wrench in my monkey works!

Please replace the "ReadArea" function:



Public Function ReadArea(i_locfilenum)
    ' this function is the same as the Name_Phone, just changes variables and cell destinations
   
    ' this reads the line
    Location$ = readline(i_locfilenum)
   
    ' Parse out City by searching for the ',' and taking the leftmost items
    CityEndPos = InStr(1, Location$, ",")
    City$ = Trim(Left(Location$, CityEndPos - 1))
   
    ' write in seventh cell
    ActiveCell.Offset(0, 6).Value = City$
   
    ' Parse State and Zip
    StateStart = InStr(CityEndPos, Location$, " ") + 1
    StateEnd = InStr(StateStart + 1, Location$, " ")
   
    State$ = Trim(Mid(Location$, StateStart, StateEnd - StateStart))
   
    Zip$ = Trim(Mid(Location$, StateEnd + 1))
   
    ' write in eighth cell
    ActiveCell.Offset(0, 7).Value = Trim(State$)
   
    ' write in ninth cell
    ActiveCell.Offset(0, 8).Value = Trim(Zip$)

End Function







0
 

Author Comment

by:MortgageGuy
ID: 11778207
Thank you very much.  1709 records would have taken months to input.
0
 
LVL 5

Expert Comment

by:slycoder
ID: 11778798
Glad to be of service - Thank you very much for the points.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

786 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