Solved

Convert rtf to csv to outlook contacts

Posted on 2004-08-10
12
531 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
[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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
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…
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 …

726 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