Importing, and Updating Exchange 2010 Contacts from CSV

image_pdfimage_print

I’ve had a very specific requirement to add a list of external contacts into an internal Microsoft Exchange environment. The goal of this is to integrate an external company’s list of contacts and some key information into the Global Address List with an identifier on each record showing the company name as part of the display name.

Before we go further, I want to qualify the process and subsequent script which supports it. This article, and the script is designed towards the intermediate to advanced PowerShell admin. By this I mean that I haven’t exhaustively documented all of the commands and steps. The article is presented in full length so I apologize for the long read.

The requirements for the script are as follows:

  • The contacts must appear in the Global Address List along with existing mail accounts
  • Display names contain the company name as a suffix to differentiate from employees
  • Updates include a number of fields such as address, title, phone number, name
  • Source system uses legal name and informal name. We want to display the informal name (i.e. Chuck vs. Charles)
  • Updates will be done programatically and fairly frequently (let’s assume weekly)
  • Updates cannot impact existing mailflow (Must update rather than delete and re-create)
  • The script was designed to run using the Microsoft Exchange 2010 Command Shell

The file that we receive will be in CSV format and has a header row. This is a saving grace as we can easily manage the content easily using the column header inside a ForEach loop.

THE FILE

The columns that we receive and a description is shown below:

EMail (SMTP formatted address)
InformalName (If FirstName is different than the name used commonly)
FirstName (Legal first name)
Lastname (Last name)
BranchCode (Each branch is assigned a code in our example)
BranchLegalName (Branch description)
AddressLine1 (Address information)
AddressLine2 (Address information if needed)
AddressCity (City name)
AddressState (State or province)
AddressZip (ZIP or Postal Code)
AddressCountry (Country in ISO code format)
JobTitle (Job title)
PhoneCountryCode (If exists for non-North American numbers)
PhoneAreaCode (Area code)
PhoneNumber (Phone number in ###-#### format)
PhoneExtensionOrSpeedDial (Extension or speed dial number if it exists)

That’s a lot of information and because it comes from an external system, the fields don’t match up one-to-one with the native Microsoft Exchange fields which are associated with a mail contact.

THE DESIGN

Now we get to the good stuff! Here is the logic of how I built the script that is used:

  1. Read the file
  2. Loop through the contacts in the file
  3. Check to see if the contact exists
  4. If it doesn’t exists, create it
  5. If the contact exists, update it
  6. Loop ends
  7. Read the OU where we store the contacts
  8. Read the file
  9. Loop through the contacts in AD
  10. Check to see if the contact exists in the file
  11. If it doesn’t exist, delete it
  12. If it exists, do nothing
  13. Loop ends

Thanks to the magic of PowerShell this is a fairly simple task. We will use the following CmdLets to accomplish our task:

  • Import-CSV
  • ForEach
  • If, Else, ElseIf
  • Get-Contact (Exchange 2010)
  • Set-MailContact (Exchange 2010)
  • Set-Contact (Exchange 2010)
  • New-MailContact (Exchange 2010)
  • Get-Content
  • Write-Host

I’ve used the Write-Host CmdLet to output to screen so that we can troubleshoot and monitor the process during the initial tests. Another important feature we use for testing is the -WhatIf parameter. I’ll give the necessary disclaimer here which is that you must run this in a test environment and using the WhatIf parameter first! It’s not that I don’t know that the script works, but regardless of my confidence, it is an absolute must that you test any process in an alternate environment before you go live.

THE SCRIPT

Hold on to your hats because this is a big one. We will step through the script together in sections to show what’s happening along the way.

# Set the file location
$rawfile = “X:SCRIPTScontacts.csv”

# Ignore Error Messages and continue on. Comment this out with a # symbol if you need verbose errors
trap [Microsoft.Exchange.Configuration.Tasks.ManagementObjectNotFoundException] { continue; }

$contactfile = Import-CSV $rawfile

In this section we’ve identified the location of the import file (assume X:SCRIPTS for the path and a filename of contacts.csv), as well as setting the alerts to continue on error. Note that this is not alwasy 100% effective and may require some tweaking which I’ll update as I make more progress with the error handling. Next we see that the file is imported into the $contactfile as an array.

Next up we will loop through the records and assign working variables to based on the contents of each record. For the name fields there have been some issues where people have names containing spaces (e.g. FirstName=Mary Jane) which will cause the import to fail. For these cases we will use a -replace option when assigning the value to the variable and replace the spaces with hyphens.

# Read contacts and make the magic happen
ForEach ($contact in $contactfile) {

# Read all attributes provided by the file
$sourceEMail=$contact.EMail
$sourceInformalName=$contact.InformalName -replace ” “,”-”
$sourceFirstName=$contact.FirstName -replace ” “,”-”
$sourceLastName=$contact.LastName -replace ” “,”-”
$sourceManagerID=$contact.ManagerID
$sourceBranchCode=$contact.BranchCode
$sourceBranchLegalName=$contact.BranchLegalName
$sourceAddressLine1=$contact.AddressLine1
$sourceAddressLine2=$contact.AddressLine2
$sourceAddressLine3=$contact.AddressLine3
$sourceAddressCity=$contact.AddressCity
$sourceAddressState=$contact.AddressState
$sourceAddressZip=$contact.AddressZip
$sourceAddressCountry=$contact.AddressCountry
$sourceJobTitle=$contact.JobTitle
$sourcePhoneCountryCode=$contact.PhoneCountryCode
$sourcePhoneAreaCode=$contact.PhoneAreaCode
$sourcePhoneNumber=$contact.PhoneNumber
$sourcePhoneExtensionOrSpeedDial=$contact.PhoneExtensionOrSpeedDial

Now we will take the variables which have been assigned and begin to manipulate the data into fields where we require concatenation of the data. This is also where we do the logical checks for the InformalName field by checking the length of the field. If the field is greater than a zero length then it will be used for the First Name attribute on the contact.

We also craft the Display Name by concatenating the calculated First Name, the Last Name and the trailing suffix of COMPANY (you can change that to be whatever identifier you want). Address and phone number are fairly simple, but again we check for field values of zero length to decide if we need to include them in the concatenated results.

Lastly in this section we create the Alias which must be unique. We prefix with COMPANY again to ensure they are different than our existing user records and make them easily searchable.

 # Create the concatenated fields and custom fields

# Informal Name – This checks to see if they have an informal name (Jim versus James) and if so, use the informal name
if ($sourceInformalName.Length -lt 1) {
$targetFirstName = $sourceFirstName
}
elseif ($sourceInformalName.Length -gt 1) {
$targetFirstName = $sourceInformalName
}

# Assign the Display Name using the correct First Name, Last Name and a suffix of COMPANY. We trim this field because of leading spaces that show up regularly
$sourceDisplayName = “$targetFirstName $sourceLastName COMPANY”
$targetDisplayName = $sourceDisplayName.Trim()

# Assign the Distinguished Name attribute using the correct First Name, Last Name and OU structure
$targetDistinguishedName = “CN=$targetFirstName $sourceLastName,OU=ExternalContacts,DC=yourdomain,DC=com”

# Assemble the phone number

# Check for a country code, otherwise value is null
if ($sourcePhoneCountryCode -lt 1) {
$targetCountryCode = $null
}
elseif ($sourcePhoneCountryCode -gt 1) {
$targetCountryCode = “$sourceCountryCode-”
}

# Check for an extension, otherwise value is null
if ($sourcePhoneExtensionOrSpeedDial -lt 1) {
$targetExtension = $null
}
elseif ($sourcePhoneExtensionOrSpeedDial -gt 1) {
$targetExtension = ” ext. $sourcePhoneExtensionOrSpeedDial”
}

$targetPhoneNumber = “$targetCountryCode$sourcePhoneAreaCode-$sourcePhoneNumber$targetExtension”
# Assemble the Address
$targetStreetAddress = “$sourceAddressLine1 $sourceAddressLine2 $sourceAddressLine3”

# Assign the name attribute for new contacts
$targetCommonName = “$sourceFirstName $sourceLastName”

# Assign the Alias using COMPANY as a prefix so that we can identify them easily
$targetAlias = “COMPANY$targetFirstName$sourceLastName”

So what we have got now is a working set of data to begin to apply to our Exchange environment. The next step is to search Active Directory/Exchange for the contact to see if they are existing.

###################################################
# Search for the contact to see if it is existing #
###################################################

if ( Get-Contact -Identity $sourceEmail )
{
# Output to screen so we can track the process. Comment the following line when it is running as a batch process
Write-Host $sourceEmail Exists so $targetDisplayName will be MODIFIED -foregroundcolor green

Set-MailContact -Identity $sourceEmail -Alias $targetAlias -ForceUpgrade
Set-Contact -Identity $sourceEmail `
-City $sourceAddressCity `
-Company $sourceBranchLegalName `
-CountryOrRegion $sourceAddressCountry `
-Department $sourceBranchCode `
-DisplayName $targetDisplayName `
-SimpleDisplayName $targetDisplayName `
-Name “$targetCommonName” `
-FirstName $targetFirstName `
-LastName $sourceLastName `
-Phone $targetPhoneNumber `
-PostalCode $sourceAddressZip `
-StateOrProvince $sourceAddressState `
-StreetAddress $targetStreetAddress `
-Title $sourceJobTitle `
-WebPage “RJFAccountFlag” `
-WindowsEmailAddress $sourceEmail -WhatIf
}

Notice that we have done 2 important things in the Set-Contact CmdLet phrasing. For readability we use the ` character which allows you to span multiple lines in a single command. Be careful that you note that it is the reverse single quote (found on the tilde ~ button) and not the traditional single quote ‘ found by the Enter Key. Secondly we have tagged the command with the -WhatIf parameter to monitor the potential result.

###################################################
# If it is not existing, create a new contact     #
###################################################
else
{
# Output to screen so we can track the process. Comment the following line when it is running as a batch process
Write-Host $sourceEmail Does Not Exist so $targetDisplayName will be CREATED -foregroundcolor yellow

# First we create the contact with the required properties
New-MailContact -Name “$targetCommonName” `
-OrganizationalUnit “OU=ExternalContacts,DC=yourdomain,DC=com” `
-ExternalEmailAddress $sourceEmail `
-Alias $targetAlias `
-DisplayName $targetDisplayName `
-FirstName $targetFirstName `
-LastName $sourceLastName `
-PrimarySmtpAddress $sourceEmail -WhatIf

# Now we set the additional properties that aren’t accessible by the New-MailContact cmdlet
Set-Contact  -Identity $sourceEmail `
-City $sourceAddressCity `
-Company $sourceBranchLegalName `
-CountryOrRegion $sourceAddressCountry `
-Department $sourceBranchCode `
-DisplayName $targetDisplayName `
-SimpleDisplayName $targetDisplayName `
-FirstName $targetFirstName `
-LastName $sourceLastName `
-Phone $targetPhoneNumber `
-PostalCode $sourceAddressZip `
-StateOrProvince $sourceAddressState `
-StreetAddress $targetStreetAddress `
-Title $sourceJobTitle `
-WebPage “RJFAccountFlag” `
-WindowsEmailAddress $sourceEmail -WhatIf
}

# Clean up after your pet – this does some memory cleanup
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}

For a new contact we had to perform two steps. The first step is to create the contact with the required attributes. There are limited attributes that can be affected with the New-MailContact CmdLet unfortunately, so we follow that command with a Set-Contact to update the remaining attributes that we need.

The section I’ve named as “Clean up after your pet” is a little memory cleansing process. Let’s just say that if you skip this step you will find yourself about 4GB deep in memory usage and crawling your way through the contact script.

The last portion of our script is the deletion process. Let me reiterate the importance of testing on this. If there is an error with the reading of the file, or you use a sample file with less contacts it will relentlessly delete them while you watch in horror. The -WhatIf has been put into the command here also to help with the assurance testing.

##################################################################
# Now we reverse the process and remove contacts no longer valid #
# If contact is not in the file, delete it                       #
##################################################################

$CurrentContacts = Get-MailContact -OrganizationalUnit ‘OU=ExternalContacts,DC=yourdomain,DC=com’ -ResultSize Unlimited | Select-Object PrimarySMTPAddress,name
ForEach ($contact in $currentContacts) {
$sourceEmail = $Contact.PrimarySMTPAddress
if ( Get-Content $rawFile | Select-String -pattern $sourceEmail )
{
# Output to screen so we can track the process. Comment the following line when it is running as a batch process
Write-Host This contact $sourceEmail Exists in the file -foregroundcolor green
}
else
{
# Output to screen so we can track the process. Comment the following line when it is running as a batch process
Write-Host “DELETE THIS CONTACT $sourceEmail” -backgroundcolor yellow
Remove-MailContact -Identity “$sourceEmail” -Confirm:$Y -WhatIf
}
}

$CurrentContacts = $null
# Clean up after your pet – this does some memory cleanup
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Congratulations! We’ve made it to the end. This script shows you the power and flexibility of PowerShell. For your own situation you can work from this sample and hopefully it will save you some development time.

This is quite a process to go through, so I encourage you to read through it and feel free to add comments with any questions.

Here are the files associated with this process:

Contacts.csv (sample contact file)

ManageExternalContacts.ps1.txt (rename to .ps1 after download)

 

DiscoPosse

People, Process, and Technology. Powered by Community!

You might also like

22 Comments

  • Mike Vain
    February 21, 2012 at 3:17 pm

    Great article! I have been searching for a script that would reliably perform exactly this.

    Would you mind answering a couple of questions? I would really appreciate it.

    I migrated from Exchange 2003 to 2010 and there were a bunch of pre-existing external contacts (our corporate in Sweden) residing in an AD container called “Company Contacts”. They are mail-enabled contacts, but I get errors if I try to update them with new information (a lot of them still exist, but their Email addresses have all changed), so I am just going to delete them all. I wasn’t clear from your script if the imported contacts will reside in a specific OU or where they will be located”.

    The contact file I’m getting from Corporate includes the following parameters:
    Alias,Name,DisplayName,EmailAddress,City,PhoneNumber,Department,Office,

    We don’t need company because they are all the same, I was looking to edit your command set to use these, would you care to comment on this? Any advice would be greatly appreciated!

    • DiscoPosse
      Eric
      February 21, 2012 at 4:11 pm

      Hi Mike,

      Thanks for the feedback! Can you give me more details regarding the errors on the legacy contacts. I know that I had a similar situation after the migration because the contacts previously had spaces in the Alias which was acceptable in the Exchange 2003.

      The solution I used for this was to add the -ForceUpgrade to the Set-MailContact CmdLet:

      Set-MailContact -Identity $sourceEmail -Alias $targetAlias -ForceUpgrade

      What you most likely have is that one of the attributes violates the requirements for an Exchange 2010 contact format. If you open one of the contact records in the Exchange Management Console and flip between the tabs you will get a warning on one of the tabs that you cannot leave until you change the content of a field. Once you find the source field(s) that are an issue.

      If you want to do a delete and replace, then you can use the script. Just remove the fields that are not present from the variable definition and the set and update field assignments too. The destination OU is set in the New-MailContact with the -OrganizationUnit parameter (excerpt shown below):

      -OrganizationalUnit “OU=ExternalContacts,DC=yourdomain,DC=com”

      Make sure that you also set the distinguishedName attribute with the correct string:

      $targetDistinguishedName = “CN=$targetFirstName $sourceLastName,OU=ExternalContacts,DC=yourdomain,DC=com”

      Hope that makes sense. Let me know if you have any other questions. Thanks!

  • Bill
    May 10, 2012 at 6:54 am

    Thanks for posting this script. I used to just delete my contacts and do a fresh import every time but that hasn’t worked so well in Exchange 2010 and was causing issues for users if I did the process during the day (we maintain about 5500 contacts in the global address book) since half the directory could be missing as a user tried to send a message during an import.

    I had some issues using the -identity with the commands but just replaced it and used the Alias to do the look ups and comparisons. Not sure if this a unique difference between 2010 and 2007 but it works great using the logic in your script.

    Thanks again for sharing!

    • DiscoPosse
      Eric
      May 11, 2012 at 8:51 am

      I’m glad that it worked for you! I know the pain of the rip and replace method and this has been a massive help for me in reducing the time to update and the consistency.

  • Bob
    July 10, 2012 at 2:53 pm

    Hello,

    I have a similar requirement as you mentioned in this article. I am looking for a tool or a script to do a buld update to all contacts in Exchagne 2007. Basically, we will be getting either a flat file or a csv file from our payroll company which has the most up to date employee record including Title, Phone numbers, Start Date, date of birth etc.. and we would like to do a mass update to our global address list in Exchange which by the way have multiple databases.

    Can i use a script similar to your sample or is there a 3rd party tool availalble to do something like this?

    Regards.

    • DiscoPosse
      Eric
      July 14, 2012 at 6:15 am

      Hi There,

      For what you need to do I would recommend working with the script and adjusting the fields as you need. I haven’t found any tools that work well with import files. It’s not that there aren’t applications that can create import and update processes, but they are typically very rigid with what fields they can manage, or they are only meant to really act as a shim between AD/Exchange and another well known system. There is also the cost, which can be pretty high with any type of “identity management” and administration system.

      That’s my thought. I hope that this script is a good start to be able to help you with what you need to do 🙂

      Thanks,

      Eric

  • McMike
    February 1, 2013 at 1:07 pm

    Does this script work only in 2010? Is there any edit that would make it work in 2007?

    • DiscoPosse
      Eric
      February 2, 2013 at 5:22 am

      I’m pretty sure this would work with 2007…but I haven’t got an environment to check unfortunately. You can run with the -WhatIf options and see what the behaviour is, or also run it with a single contact to test out the process. Let me know if it does work. Sorry that I haven’t got more concrete information.

  • John Rolstead
    February 11, 2013 at 12:50 pm

    Thank you for clearing up a Get-Mailcontact problem I was having! The Trim Method resolved an object not found error even thought the same command from consle returned the object! I must have been pulling in spaces with email address.

    Regards,
    John Rolstead

    • DiscoPosse
      Eric
      February 11, 2013 at 4:39 pm

      Thanks for the comment John. I’m glad the script was able to help!

      Eric

  • Markus
    July 11, 2013 at 1:26 am

    Eric,

    Can your solution coexist with auto-complete lists of individual Outlook clients? We are trying to implement the functionality of your script but are experiencing a nasty problem:

    Whenever a contact created by the CSV import tool already existed in the auto-complete list of an Outlook user, this user gets an error when trying to send a mail to this contact:

    IMCEAEX-_O=_OU=EXCHANGE+20ADMINISTRATIVE+20GROUP+20+28FYDIBOHF23SPDLT+29_CN=RECIPIENTS_CN=Tong+20Elsaabc@.com
    #550 5.1.1 RESOLVER.ADR.ExRecipNotFound; not found ##

    What happens is that the external username and our own domain are combined to an address which of course does not exist.

    Thanks & regards
    Markus

    • DiscoPosse
      Eric
      July 17, 2013 at 3:37 am

      Hi Markus,

      I’ve had this happen before also. The Outlook cache can be a real trick. The NK2 file stores the previous instance, and sometimes it conflicts when a new instance of the contact is created.

      One of the ways that I’ve worked around this is to force the Outlook client to do lookups against the GAL. Here is that article: http://support.microsoft.com/kb/831124

      Hopefully that could help. Let me know if the problem persists.

      Thanks,

      Eric

  • Markus
    July 24, 2013 at 6:23 am

    Hi Eric,

    Thanks so much for your reply. Fix it 50345 brings up an error saying it is not intended for my OS or application version. We are running Win XP SP3 and Outlook 2010, both in German. So I used regedit to add this key:

    [HKEY_CURRENT_USERSoftwareMicrosoftOffice14.0OutlookCached Mode] “ANR Include Online GAL”=dword:00000001

    Unfortunately the problem persists.

    Markus

  • Carlos
    June 4, 2015 at 8:25 am

    Thanks a lot for the script! After a few modifications this script works as expected. I was able to create over 250 contacts in our AD and Exchange.

  • Carlos
    June 4, 2015 at 8:26 am

    Thanks for the Script! After a few modifications I was able to create over 250 contacs on our AD and Exchange. Thanks a lot!.

    • DiscoPosse
      Eric
      June 13, 2015 at 5:46 pm

      Great hear Carlos! Thank you for feedback 🙂

  • Aviv
    December 27, 2015 at 5:12 am

    Excuse me sir,
    Thank you very much for your script but the download isnt working, when i right click and try to save them, i get files filled with HTML data.
    Can you fix this please?

    • DiscoPosse
      DiscoPosse
      February 6, 2016 at 7:02 pm

      Hi Aviv,

      This should be all fixed now. Sorry for the issues in the first attempt. I had recently ported my site to a new server and some of the content didn’t come over correctly.

  • Jose
    October 10, 2016 at 4:52 pm

    The sample file Contacts.csv is not available to download.

    • DiscoPosse
      DiscoPosse
      October 20, 2016 at 9:05 pm

      Hi Jose! Thank you for letting me know. I’ve updated the file URL with the working link now.

  • Shawn
    October 18, 2016 at 10:39 am

    Hello and thanks for posting–this is precisely what I have been looking for. I was able to download the PS script but the link to the sample CSV file seems to be broken. Can I get a copy of the sample CSV file? Thanks.

    • DiscoPosse
      DiscoPosse
      October 20, 2016 at 9:04 pm

      Thank you for spotting this, Shawn! I’ve fixed the link now with a new location that works.

LEAVE A COMMENT

Proudly Sponsored By

GC On-Demand

Subscribe to the Blog

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Upcoming events:

Archives