TAG

Bulk Exchange Objects creation using CSV Files

Leave a comment

Whether you are creating new Mailbox users , Mail users or any other Exchange objects, it can be time consuming and a headache to administrators who find themselves in a situation where they need to create a bulk of those objects, the more time is consumed in these tasks, the more you are subject to errors and mistakes. so to avoid this today i am going to talk about the use of Microsoft Excel along with Exchange PowerShell to make this task easier.

so what i am talking about here is not how to create an Exchange object using Exchange PowerShell, i need you to focus on how to organize the data you receive  in a proper way before presenting it to Exchange PowerShell. so for an Example here we are going to consider a requirement for creating a number of Mail contacts on your Exchange Server. and all you receive is an Excel sheet with those users in a single column like shown below 🙂 ( Wish i did 😀 )

01

Now all you got a single column that includes the Full names of the required contacts. to move on, we need to review the command that creates  a Mail contact from the PowerShell, we need to know what are the attributes required by this command to create all the provided contacts.

Okay, i will start by opening Exchange PowerShell and i’ll type the command Help New-MailContact to get all the attributes that can be used with this command, Then i will identify the attributes that i need to fill when i create those users, and in this example i will pick the Name, First Name, Last Name, Alias, Organizational unit, and Primary SMTP Address.

02

Time to Work with your Excel sheet 🙂 , start by filling the chosen attributes as an additional columns.

03

The first thing i need to do is to fill the FirstName and LastName columns, this should easily by done by distribute the Name column content using the Text to Column feature from the DATA tab.

04

Move with the text wizard, choose Delimited as we are going to “Space” as the method of identification.

05

Choose the Space to identify how you want the text to be separated.

06

Make sure to set the destination for the result data, in our case B2 is the starting cell. Then click to finish

07

Okay, here we go 🙂 done with those two columns.

08

Now for the alias, we are going to use the format of the first letter of the first name followed by the last name ( which is the normal for most cases )

use the below formula to fill in the first cell =CONCATENATE(LEFT(A2,1), B2) then apply it to the rest of the cells.

09

for the organizational unit column , i am going to open the active directory users and computers console , create a OU that will host those contacts,  and from the objects tab on the properties page copy the object canonical name.

10

Apply the OU canonical name to the OrganizationalUnit column

11

Now for the last column ExternalEmailAddress use the following formula to use the format alias@domain.com

=CONCATENATE(D2,”@lab.com”) then apply the result to the rest of the cells

12

Okay it is time to do some final touches to make sure your script works with no errors, the first thing you need to do is identify the cells that you used formulas to fill and then convert the content from Formula to Value

13

Trailing or Leading spaces could be a headache beside it causes errors when running your script on Exchange PowerShell, make sure to clear them before importing your CSV file, Excel has a builtin Trim Function that would assist you in this, instructions on how to use it can be found here Trim Function Help ..

Okay, now back to Exchange server and PowerShell.. to import our CSV file we are going to use the command Import-CSV with ForEach. as you can see below the ForEach command links every attribute required for the mail contact creation to the relevant value from the CSV file.

Import-Csv “C:\Contacts.csv” | ForEach {New-MailContact -Name $_.Name -Firstname $_.FirstName -LastName $_.LastName -Alias $_.alias -ExternalEmailAddress $_.ExternalEmailAddress -OrganizationalUnit “lab.com/External Contacts”}

Time to run it 🙂

14

Check your Active Directory and Exchange console

15
16

Annnnd we’re done 🙂 .. again, this article is not about how to bulk create Mail contacts only, for any object, lookup the required attributes, format your CSV file and import it .. hope that was informative 🙂 Good day Folks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s