Solved

Convert rtf to csv to outlook contacts

Posted on 2004-08-10
12
524 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
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.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: dandraka
There are several quick shortcuts that can make your life easier in Microsoft Programs.  These simple tips and tricks will your work more productive and you faster at completing your tasks! MS Word (1) Creating Re-usable Scraps You can create s…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

746 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

10 Experts available now in Live!

Get 1:1 Help Now