Freigeben über

Importing Public Folder Contacts From A CSV File

  • Artikel

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!"

Comments

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    July 13, 2010
    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?

  • Anonymous
    April 06, 2011
    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

  • Anonymous
    February 27, 2012
    The comment has been removed

  • Anonymous
    May 16, 2012
    Any help to export the existing contacts from public folder?

  • Anonymous
    July 24, 2012
    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.

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

  • Anonymous
    August 28, 2014
    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?

  • Anonymous
    November 03, 2014
    The comment has been removed