Sunday, March 23, 2008

Import Contacts from CSV Into Exchange 2007

A vendor submitted a list of their employees whose contact information needed to be made available through the Exchange 2007 Global Address List (GAL). I found a very elegant solution for this from the MSExchange.org website. Although the solution is a great starting point, I needed to modify it. The way it is written makes this a very easy thing to do.

The Excel sheet sent to me this time (next time it will probably be different) had additional columns and attributes that were not included in the posted script. The original script assumes that their is not "display name" or full name. So it concatenates the first and last fields to create this to later be used at import. I find it easier to concatenate the strings (if needed) using Excel's concatenate() function. Speaking of the Excel sheet, there needs to be column headers in the sheet. These column headers will be used during the import process. The fields available bring up the point of how Exchange 2007 and Active Directory store and access the properties of a contact object. The mail-enabled contact must be created through Exchange. Then, the Active Directory-specific attributes can be changed.

All of these objects are created using the Windows Powershell (more info about the Powershell can be found here: http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx) with the Exchange Snap-in. The following are the commands used:
  • Import-csv
  • ForEach-object
  • New-mailcontact
  • Set-contact
  • Set-mailcontact
The format of the new-mailcontact command points to a domain controller, and then the attributes/properties of the object are manipulated using a hyphen (-) followed by the name of the property, while the name of the column is designated with a variable.

new-mailcontact -domaincontroller dc.domain.com -Name $_."Name Column" -Firstname $_."First Name" -Lastname $_."Last Name"


The set-contact command works the same way, it just offers different attributes to manipulate.

set-contact -domaincontroller dc.domain.com -City $_."Business City" -Company $_."Company"


The entire script is below:

Add-Content c:\debug.txt "::Starting Import: ";

$err = "";

Import-Csv Import-CSV-Contacts.csv | ForEach-Object{

New-MailContact -DomainController dc.domain.com -Name $_."displayName" -Firstname $_."FirstName" -Lastname $_."LastName" -ExternalEmailAddress $_."E-mail Address" -OrganizationalUnit "harrynorman.com/Offices/Contacts/Dickenson Gilroy Law Firm" -ErrorAction SilentlyContinue -ErrorVariable +err | Set-Contact -DomainController dc.domain.com -City $_."Business City" -Company $_."Company" -Office $_."Office" -DisplayName $_."displayName" -Fax $_."Business Fax" -Name $_."displayName" -Phone $_."Business Phone" -PostalCode $_."Business Postal Code" -StateOrProvince $_."Business State" -StreetAddress $_."Business Street" -Title $_."Title" -ErrorAction SilentlyContinue -ErrorVariable +err;

Set-MailContact -Identity $_."displayName" -DomainController dc.domain.com -ErrorAction SilentlyContinue -ErrorVariable +err;
}
Add-Content c:\debug.txt $err;


Finally, I created a new custom address list in Exchange to aggregate these addresses. Much like the "query-based distribution groups" in Active Directory, a query of the company can be used to create the custom address list. As new contacts are added/removed the address list should be updated accordingly.

Side Note: A very nice feature in Exchange 2007 Outlook Web Access is the way the system automatically senses an address field for a contact. It provides a "Map this address..." link that then takes the user to Microsoft's map engine displaying the map for the user in a new window.

2 comments:

ExchAdmin said...

Code does not work...does seeem to loop through the info in the .csv files.

ExchAdmin said...

Code does not work...does seeem to loop through the info in the .csv files.