Bill Long's Exchange Blog

Exchange Server stuff, focusing on Public Folders, PFDAVAdmin, ExFolders, and Powershell scripting.

Importing Public Folder Contacts From A CSV File

Importing Public Folder Contacts From A CSV File

  • Comments 10
  • Likes

I just had a customer who needed to migrate contacts from an external database into a public folder. They could export the contacts from the database to a CSV, but they needed a way to get the CSV into the public folder. Last night, I whipped up this quick script.

This morning, I was looking at my RSS feeds, and found that Glen Scales had just solved the same problem in a new blog post. Figures! Here is my version, but I recommend taking a look at his work as well.

That said, here’s what an example CSV might look like for use with my script:

GivenName,Surname,EmailAddress1,EmailAddress2,HomePhone,MobilePhone,Business
Contact,1,contact1@contoso.com,contact1@northwindtraders.com,888-555-1212,888-555-1212,One Microsoft Way%Redmond%WA%US%98052
Contact,2,contact2@contoso.com,contact2@northwindtraders.com,888-555-1212,888-555-1212,One Microsoft Way%Redmond%WA%US%98052

Notice the weird physical address syntax where each part of the address is separated with a % character. This was my way of dealing with the need to parse out the individual address fields (Street, City, State, CountryOrRegion, PostalCode). Everything else is pretty straightforward. There are a lot of properties you can set that are not shown in this example CSV. I’ve listed most of them in the comments at the top of the script. Here it is.

# Import-PFContacts
#
# The purpose of this script is to import contacts into a public folder on
# Exchange 2007 or 2010 from a CSV file. The script reads from a CSV and then
# creates a contact with the appropriate fields using the Exchange Web
# Services Managed API.
#
# Requirements:
#
# This script requires Powershell 2.0. If you get the following error then you do not
# have Powershell 2.0 installed:
#
# The term 'Import-Module' is not recognized as a cmdlet, function, operable program,
# or script file. Verify the term and try again.
#
# The script also requires the EWS managed API, which can be downloaded here:
# http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c3342fb3-fbcc-4127-becf-872c746840e1
#
# Make sure the Import-Module command below matches the DLL location of the API.
#
# Syntax:
# .\Import-PFContacts admin@contoso.com C:\SomeFile.csv "Top Level Folder\Subfolder\Contacts Folder"
#
# The first parameter is the email address that we'll use to run autodiscovery
# and logon. The second parameter is the import file, and the third is the folder
# where we want to create the contacts.
#
# Below you will find details on the columns that are supported in the import
# file. Note that column names are CASE SENSITIVE.
#
# The import file can contain the following email address columns:
#
# EmailAddress1, EmailAddress2, EmailAddress3
#
# The import file can contain the following phone number columns:
#
# AssistantPhone, BusinessFax, BusinessPhone, BusinessPhone2, Callback, CarPhone,
# CompanyMainPhone, HomeFax, HomePhone, HomePhone2, Isdn, MobilePhone, OtherFax,
# OtherTelephone, Pager, PrimaryPhone, RadioPhone, Telex, TtyTddPhone
#
# The import file can contain the following physical address columns. Note that
# the syntax for these is a little odd at the moment. The address has five fields,
# which must be in order and separated by % characters. They are street, city,
# state, country or region, and postal code. Some of the fields can be left
# blank if desired. An example address entry might look like this:
# One Microsoft Way%Redmond%WA%US%98052
# The physical address columns are:
#
# Home, Business, Other
#
# The imort file can contain the following other columns:
#
# AssistantName, Birthday, BusinessHomePage, CompanyName, CompleteName, Department,
# DisplayName, FileAs, Generation, GivenName, Initials, JobTitle, Manager, MiddleName,
# Mileage, NickName, OfficeLocation, Profession, SpouseName

param([string]$autoDiscoverAddress, [string]$importFile, [string]$folderPath)

##########
#
# This path must match the install location of the EWS managed API. Change it if needed.
#
Import-Module -Name "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
#
##########

$emailAddressColumns = new-object System.Collections.Specialized.StringCollection
$foo = $emailAddressColumns.AddRange(@("EmailAddress1", "EmailAddress2", "EmailAddress3"))
$phoneNumberColumns = new-object System.Collections.Specialized.StringCollection
$foo = $phoneNumberColumns.AddRange(@("AssistantPhone", "BusinessFax", "BusinessPhone", "BusinessPhone2", "Callback", "CarPhone"))
$foo = $phoneNumberColumns.AddRange(@("CompanyMainPhone", "HomeFax", "HomePhone", "HomePhone2", "Isdn", "MobilePhone", "OtherFax"))
$foo = $phoneNumberColumns.AddRange(@("OtherTelephone", "Pager", "PrimaryPhone", "RadioPhone", "Telex", "TtyTddPhone"))
$physicalAddressColumns = new-object System.Collections.Specialized.StringCollection
$foo = $physicalAddressColumns.AddRange(@("Business", "Home", "Other"))

$exchService = new-object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
$exchService.UseDefaultCredentials = $true
$exchService.AutodiscoverUrl($autoDiscoverAddress)
$pfsRoot = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($exchService, [Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::PublicFoldersRoot)

$tinyView = new-object Microsoft.Exchange.WebServices.Data.FolderView(2)
$displayNameProperty = [Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName
$folderPathSplits = $folderPath.Split(@('\'))
$folder = $pfsRoot
for ($x = 0; $x -lt $folderPathSplits.Length;$x++)
{
    $filter = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo($displayNameProperty, $folderPathSplits[$x])
    $results = $folder.FindFolders($filter, $tinyView)
    if ($results.TotalCount -gt 1)
    {
         ("Ambiguous name: " + $folderPathSplits[$x])
         return
    }
    elseif ($results.TotalCount -lt 1)
    {
         ("Folder not found: " + $folderPathSplits[$x])
         return
    }
    $folder = $results.Folders[0]
}

$importReader = new-object System.IO.StreamReader($importFile)
$headers = $importReader.ReadLine().Split(@(','))
$line = 1

while ($null -ne ($buffer = $importReader.ReadLine()))
{
    $line++
    if ($buffer.Length -gt 0)
    {
        $newContact = new-object Microsoft.Exchange.WebServices.Data.Contact($exchService)
        $columns = $buffer.Split(@(','))
        for ($x = 0; $x -lt $headers.Length; $x++)
        {
            if ($columns[$x].Length -lt 1)
            {
                continue
            }
            if ($emailAddressColumns.Contains($headers[$x]))
            {
                $emailAddressType = $headers[$x]
                $emailAddressType = [Microsoft.Exchange.WebServices.Data.EmailAddressKey]::$emailAddressType
                $newContact.EmailAddresses[$emailAddressType] = $columns[$x]
            }
            elseif ($phoneNumberColumns.Contains($headers[$x]))
            {
                $phoneType = $headers[$x]
                $phoneType = [Microsoft.Exchange.WebServices.Data.PhoneNumberKey]::$phoneType
                $newContact.PhoneNumbers[$phoneType] = $columns[$x]
            }
            elseif ($physicalAddressColumns.Contains($headers[$x]))
            {
                $addressFields = $columns[$x].Split(@('%'))
                $addressType = $headers[$x]
                $addressType = [Microsoft.Exchange.WebServices.Data.PhysicalAddressKey]::$addressType
                $address = new-object Microsoft.Exchange.WebServices.Data.PhysicalAddressEntry
                $address.Street = $addressFields[0]
                $address.City = $addressFields[1]
                $address.State = $addressFields[2]
                $address.CountryOrRegion = $addressFields[3]
                $address.PostalCode = $addressFields[4]
                $newContact.PhysicalAddresses[$addressType] = $address
            }
            elseif ($headers[$x] -eq "Birthday")
            {
                $newContact.Birthday = [System.DateTime]::Parse($columns[$x])
            }
            else
            {
                $attribute = $headers[$x]
                $newContact.$attribute = $columns[$x]
            }
        }
        $newContact.Save($folder.Id)
    }
}

"Done!"

Comments
  • hey, you did a really greate job there!!!

    I'm not at powershell guru (not at all) and it was not easy to read and understand (somewho) what your script is doing. It's excately what I'm looking for, but some fields are missing and/or are not filled in correctely:  f.exp. (File as:), (Display as:)

    Can you explain how to add this and other fields?

  • Hey Jack, sorry for the delayed response. I have been swamped.

    Looking at the EWS managed API reference (msdn.microsoft.com/.../microsoft.exchange.webservices.data.contact_members(v=EXCHG.80).aspx), DisplayAs is not exposed as a property on the Contact class.

    FileAs should work, but I haven't tested it. In any case, you'll only be able to add other fields that are exposed via the API as shown in the reference doc.

  • Hi, I know this is an older post but very new at this and have no idea how to run this.  I know how to get to powershell on Exchnage 2010 and run invidual commands but do I kick off the whole thin and pass parameters?  Thanks, Joe

  • Hi Joe,

    First, you'll need to copy the whole script and save it to a file called "Import-PFContacts.ps1". Then, from the Exchange Management Shell, change to the folder where the script is and run the script. Look at the top of the script for syntax examples. I put them in the comments.

  • Thank you for this script. I found one variable that is probably not (anymore) in use: $line

    It of course anyway works very good.

  • Any help to export the existing contacts from public folder?

  • Hi,

    thanks Bill for this script.

    I had to find a solution for exporting SQL data (MS CRM 2011) and import them to Exchange 2007 Public Folder Contacts.

    I wrote a powershell script for exporting SQL data to a csv file.

    Before the import starts all items in the public folder were deleted.

    And changed your script a little bit for the import.

    The field mapping wasn't nice because CRM don't use the same names like Exchange.

  • Late to the party here, but this script is awesome. Works like a charm, thank you!

  • What about comma in a value? I want to save the DisplayName and FileAs by "Surname, GivenName". How can i set it without being recognized as separator?

  • Hi everybody, Bill thank you for your usefull script !! I have a question about this script, did you test this script under EWS 2.0 and exchange 2010 SP2 ?

    I change the import module line and exchange version line but i have a message "autodiscover url cannot be reached", I configured my EWS with url but I'm looking for a tutorial to check all the pre requisite with EWS + autodiscover

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment