• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Convert rtf to csv to outlook contacts

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
MortgageGuy
Asked:
MortgageGuy
  • 7
  • 5
1 Solution
 
slycoderCommented:
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
 
MortgageGuyAuthor Commented:
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
 
MortgageGuyAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

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
 
slycoderCommented:
BTW - guess it's "four things"  I can program, I just can't count.
0
 
MortgageGuyAuthor Commented:
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
 
slycoderCommented:
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
 
MortgageGuyAuthor Commented:
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
 
slycoderCommented:
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
 
MortgageGuyAuthor Commented:
Thank you very much.  1709 records would have taken months to input.
0
 
slycoderCommented:
Glad to be of service - Thank you very much for the points.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now