Import contact info from Mac Address Book 4.0.6

[the magician]

At job, Mac OSX and Address Book 4.0.6 with contact info including in many cases emails. I'd like to get it of there, into Excel to manipulate, and ultimately into a FileWrecker Pro database. How to export 1087 entries from Address Book including only selective data fields is the question, and I know that's "slightly" OT, but I do want to import into Excel, so I hope that vindicates me! If not, and you can point me to a good MacForum, I'll settle.

Thanks.

el mago

"poof"

 

[Norie]

Doesn't the address book have any export options?

 

[the magician]

From what I've found so far, File-Export creates a single "vcf" (text actually) file with all the information in it. Here's the first entry (modified to protect the innocent). 1086 of these follow.

(Hmmm... it was here until I posted it. Following up.... hang on)

 

[the magician]

Here's what the first of 1087 records comes out as in the text file. Lots of vba needed here. I just wanted to get:
Lich,Barbara,blich@sbcglobal.net

BEGIN:VCARD
VERSION:3.0
N:Lich;Barbara;;;
FN:BarbaraLich
EMAIL;type=INTERNET;type=WORK;type=pref:blich@sbcglobal.net
TEL;type=WORK;type=pref866)225-0446
item1.ADR;type=WORK;type=pref:;;71OceanSt;Teaneck;NJ;96530;
item1.X-ABADR:us
CATEGORIES:ArtParkCommittee-2008,evitelist14*,Volunteers-Maybe
X-ABUID:24A84336-8A59-4854-8500-0C727390D329\:ABPerson
END:VCARD

 

[Norie]

Sure you probably need VBA but I don't think it's going to be too difficult.

That data looks pretty well structured and there are plenty of delimiters in there, including the BEGIN/END which appear to delimit 1 record.

What data do you want to get out of it?

Are there anymore examples, perhaps where people have more than one email, tel no etc?

If that is a possibilty then yes it could get messy.

 

[the magician]

Yes, there will be records with home, biz and cell phones, some with no phones. Two emails, no emails. Some will in fact be just an email address with no other associated data. The consistency factor here is fudge multiplied by Tuesday, divided by zero. Ergo, I'm hopeful to get the Address Book export function to do the work first.

 

[Norie]

Ergo, I'm hopeful to get the Address Book export function to do the work first.

Confused, I thought this was the output from the export function.

Are you offered any options when you export?

 

[the magician]

Apologies. Yes, that is what I get out of the export, and no there are no options. It appears to be an all-or-all function. What I meant was hoping to find a way to "export" out of Address Book selectively (ie. name and email fields only), and I don't know how.

 

[Norie]

Sorry but I really don't see how that's an Excel issue.

Unless somewhere in the world there's a way to import this vcl file.

 

[mikerickson]

Export your AddressBook to the vcf file.
Put this in an Excel file.
When prompted, select that .vcf file.
It will create a new workbook with the information you want.

Sub ImportFromAddressBook()
    Dim newWorkBook As Workbook
    Dim pathStr As String
    Dim resultRRay As Variant
    Dim rNum As Long, outPoint As Long
    pathStr = Application.GetOpenFilename
    If Not (pathStr = "False") Then
        On Error GoTo HaltRoutine
        Set newWorkBook = Workbooks.Open(pathStr)
        With newWorkBook.Sheets(1).Range("A:A")
            With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
                .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))
            End With
            ReDim resultRRay(1 To 2, 1 To .Rows.Count)
            For rNum = 1 To .Rows.Count
                If .Cells(rNum, 1) = "N" Then
                    outPoint = outPoint + 1
                    resultRRay(1, outPoint) = .Cells(rNum, 2).Value
                End If
                If .Cells(rNum, 1) Like "EMAIL*" Then
                    resultRRay(2, outPoint) = .Cells(rNum, 2).Value
                End If
            Next rNum
            ReDim Preserve resultRRay(1 To 2, 1 To outPoint)
            With .Offset(0, 2).Resize(outPoint, 2)
                .Value = Application.Transpose(resultRRay)
                With .Offset(0, 2)
                    .Columns(1).FormulaR1C1 = "=SUBSTITUTE(SUBSTITUTE(RC[-2],"";"","", "",1),"";"","""")"
                    .Columns(2).FormulaR1C1 = "=RC[-2]"
                    .Value = .Value
                End With
                Range(.Parent.Range("a1"), .Cells).EntireColumn.Delete
            End With
        End With
    End If
HaltRoutine:
    On Error GoTo 0
End Sub

One odd thing, when using OPEN from the File menu, the .vcf file is greyed out and unavaliable, but the GetOpenFile name will take the file and the Workbooks.Open(path) works fine.