Solved

Convert rtf to csv to outlook contacts

Posted on 2004-08-10
12
526 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

20 Experts available now in Live!

Get 1:1 Help Now