Import Contacts into Exchange 2007 using CSV
* This article has been superceded by the one posted HERE
One of my clients came up with a pretty standard request the other day, one I have been meaning to get around to myself but just hadn't had the time. He wanted a script to assist him in importing bulk contacts into his Exchange Server 2007 Organization.
Now this is pretty simple, well much simpler than it was in 2003, when you leverage the functionality of PowerShell Cmd-Lets, Scripting & the native CSV file support.
The procedure is as follows:
1. Create a new File in notepad
2. Paste the following text into it
Last,First,Email,Company,Department,Title,Location,Tel,Fax,Mobile,POBox,Zip,StreetAdd,City,Country
Gates,bill,gates.bill@microsoft.com,Microsoft Corporation,Executive,Chairman,Redmond,+1 (0)830 308 3665,+1 (0)830 336 6727,+1 (0)191 555 1780,8469,A9141,"1 MS Close",Redmond,United States of America
3. Save the file as Contacts.csv
4. Open the file in Xcel & Populate it with all the required data
5. Open Notepad & Paste the following into it:
# Create User Accounts & Mailboxes According to Information in contacts.csv (CSV Formatted)
One of my clients came up with a pretty standard request the other day, one I have been meaning to get around to myself but just hadn't had the time. He wanted a script to assist him in importing bulk contacts into his Exchange Server 2007 Organization.
Now this is pretty simple, well much simpler than it was in 2003, when you leverage the functionality of PowerShell Cmd-Lets, Scripting & the native CSV file support.
The procedure is as follows:
1. Create a new File in notepad
2. Paste the following text into it
Last,First,Email,Company,Department,Title,Location,Tel,Fax,Mobile,POBox,Zip,StreetAdd,City,Country
Gates,bill,gates.bill@microsoft.com,Microsoft Corporation,Executive,Chairman,Redmond,+1 (0)830 308 3665,+1 (0)830 336 6727,+1 (0)191 555 1780,8469,A9141,"1 MS Close",Redmond,United States of America
3. Save the file as Contacts.csv
4. Open the file in Xcel & Populate it with all the required data
5. Open Notepad & Paste the following into it:
# Create User Accounts & Mailboxes According to Information in contacts.csv (CSV Formatted)
# NOTE: Be sure that the Contacts.csv file resides in the same directory as the script when running.
# $_. (defined by $entry) calls the column in the CSV (e.g. $_.First calls the data from csv column titled First)
# $_.First is First Name
# $_.Last is Last Name
# $DN is Display Name
# $uLogin is User Login Name
# $OU is the Organizational Unit for User Objects (String Value enclosed in '')
# $Domain is the UPN Domain Name (either internal Domain Name or Public Domain Name if added as additional UPN Domain in Domains & Trusts, string value enclosed in '')
# $Server is the Database Serer Name (String value enclosed in '')
# $SG is the Storage Group Name for hosting Mailboxes
# $MDB is the Mailbox Database Name for hosting Mailboxes
# $_.First is First Name
# $_.Last is Last Name
# $DN is Display Name
# $uLogin is User Login Name
# $OU is the Organizational Unit for User Objects (String Value enclosed in '')
# $Domain is the UPN Domain Name (either internal Domain Name or Public Domain Name if added as additional UPN Domain in Domains & Trusts, string value enclosed in '')
# $Server is the Database Serer Name (String value enclosed in '')
# $SG is the Storage Group Name for hosting Mailboxes
# $MDB is the Mailbox Database Name for hosting Mailboxes
# Define Common Variables
$OU = 'Contacts'
# Call CSV File & Create Mailboxes
Write-host "Creating Contacts..."
import-csv 'contacts.csv' |
foreach{
$entry = $_
$DN = $_.First+" " +$_.Last;
$Alias = $_.First+"_" +$_.Last
New-MailContact $DN -DisplayName $DN -FirstName $_.First -LastName $_.Last -organizationalunit $OU -Alias $Alias -ExternalEmailAddress $_.email
Set-Contact $DN -Company $_.Company -Title $_.Title -Department $_.Department -Fax $_.Fax -MobilePhone $_.Mobile -Office $_.Location -Phone $_.Tel -PostalCode $_.Zip -PostOfficeBox $_.POBox -City $_.City -StreetAddress $_.StreetAdd -CountryorRegion $_.Country
Write-Host 'OU =' $OU
Write-host 'First Name =' $_.First
Write-Host 'Last Name =' $_.Last
write-host 'Display Name =' $DN
Write-Host 'Alias =' $Alias
Write-Host 'Email Address =' $_.Email
Write-Host 'Company =' $_.Company
Write-Host 'Office =' $_.Location
Write-Host 'Title =' $_.Title
Write-Host 'Department =' $_.Department
Write-Host 'Telephone =' $_.Tel
Write-Host 'Fax =' $_.Fax
Write-Host 'Mobile No. =' $_.Mobile
}
6. Save the file as CreateContacts.ps1
7. Create an OU in AD Called Contacts
8. Copy both Contacts.csv & CreateContacts.ps1 onto a computer with Exchange Management Shell Installed
9. From EMS, navigate to the folder where you placed the files above & run the PS1 script (type .\CreateContacts)
Hint: When entering numbers, i.e. telephone, extension etc in excel, be sure to start the input with ' to ensure no leading 0 is dropped, e.g. '04 301 9112
foreach{
$entry = $_
$DN = $_.First+" " +$_.Last;
$Alias = $_.First+"_" +$_.Last
New-MailContact $DN -DisplayName $DN -FirstName $_.First -LastName $_.Last -organizationalunit $OU -Alias $Alias -ExternalEmailAddress $_.email
Set-Contact $DN -Company $_.Company -Title $_.Title -Department $_.Department -Fax $_.Fax -MobilePhone $_.Mobile -Office $_.Location -Phone $_.Tel -PostalCode $_.Zip -PostOfficeBox $_.POBox -City $_.City -StreetAddress $_.StreetAdd -CountryorRegion $_.Country
Write-Host 'OU =' $OU
Write-host 'First Name =' $_.First
Write-Host 'Last Name =' $_.Last
write-host 'Display Name =' $DN
Write-Host 'Alias =' $Alias
Write-Host 'Email Address =' $_.Email
Write-Host 'Company =' $_.Company
Write-Host 'Office =' $_.Location
Write-Host 'Title =' $_.Title
Write-Host 'Department =' $_.Department
Write-Host 'Telephone =' $_.Tel
Write-Host 'Fax =' $_.Fax
Write-Host 'Mobile No. =' $_.Mobile
}
6. Save the file as CreateContacts.ps1
7. Create an OU in AD Called Contacts
8. Copy both Contacts.csv & CreateContacts.ps1 onto a computer with Exchange Management Shell Installed
9. From EMS, navigate to the folder where you placed the files above & run the PS1 script (type .\CreateContacts)
Hint: When entering numbers, i.e. telephone, extension etc in excel, be sure to start the input with ' to ensure no leading 0 is dropped, e.g. '04 301 9112
He wanted a script to assist him in importing bulk contacts into his Exchange Server 2007 Organization.
good
hurray i am teaching excahnge server this time
Thanks for posting this, and your entire code. Very helpful. You have some tweaks in there that would have eluded me...
It's a pleasure to help out. I am sure the code could be further optimized as I dont script / code too much so not the best at it, but hey, so long as it works that's great for me.
Thanks for the detailed instructions on how to do this! A lot of small businesses use bulk contacts/mailing lists, and this comes in handy. I am concerned about computer hacking though with this type of file - do you have thoughts on that?
Hi,
The Files contain basic plain text and standard VB / Powershell commands which are available anywhere on the web so would be readily accessible to a hacker in any case.
They also require local access to the network which they are run against and should be run from a computer that is a domain member, has the exchange management tools installed and using an account with appropriate priviledges to create objects in AD and email enable those objects (in this case Contacts.)
All in all, it may be possible to use it as a hacking tool, but in my own opinion, unlikely.
that's great information. I really like it. Thanks for this useful information