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.
Reply to this
good
Reply to this
hurray i am teaching excahnge server this time
Reply to this