Importing Public Folder Contacts From A CSV File

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:
# https://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!"