Bulk Import Contacts to Exchange Online (Office 365) Using PowerShell | Part 1#2

In Exchange based environment, the term “external contacts” or just “contacts,” describe an Exchange recipient object, which their E-mail address is an external E-mail address (non-organization E-mail address).

For example – suppliers, business partners, customers and so on.

By default, “external contacts” appear as part of the GAL (global address list) alongside with the “standard” organization recipients such as – Exchange organization recipients who have a mailbox, distribution groups, etc.

Bulk Import Contacts to Exchange Online (Office 365) Using PowerShell script | Article Series

• Bulk Import Contacts to Exchange Online (Office 365) Using PowerShell | Part 1#2 (this article)
• Bulk Import Contacts to Exchange Online (Office 365) Using PowerShell script | Part 2#2

Transfer mail contacts to Exchange Online

In a scenario in which we migrate from a non-Exchange mail infrastructure, one of the most common requirements is – to transfer all the “mail contact recipients” to the new mail infrastructure meaning, Exchange Online.

The option of “transferring” the mail contacts to Exchange Online can be implemented via one of the following options:

1. Creating each of the mail contacts in Exchange Online manually.

In case that we are talking about a significant amount of mail contacts, the “manual” option, in which we create this contact manually in Exchange Online, is not the desired choice. This task considered as exhausting work can be exposed to human error (spelling mistake, etc.) and so on.

2. Using a third-party import tool.

In case that you use a third party tool for implementing your mail migration from existing mail infrastructure to Exchange Online, most of this “mail migration application” includes models that will help you to import the existing contact list to Exchange Online GAL.

3. Exchange Online – using PowerShell commands.

The Exchange online environment offers us a built-in solution for importing “contacts” object.

The first phase implemented by exporting the information from the current mail infrastructure to a CSV file.

The second phase is performed using a PowerShell command that imports to information about the contact from the CSV file to our Exchange Online server.

The current article and the next article are dedicated for the “Exchange Online method” in which we implemented the bulk contact import task by using PowerShell.

The challenges that we face

1. At the current time, Exchange Online doesn’t include a graphical interface that will enable us to import the required contact information from the CSV file.

The only available option is to use a “set of PowerShell commands”, that will implement the “import contact task.” The PowerShell commands that we use for this work, can be considered as complicated and not so simple for new PowerShell users.”

2. The contact information in the CSV file should store in a particular data structure format. For example, Exchange Online dictates a mandatory requirement in which each contact should have an E-mail address and a “name” (other standard terms are – Display name, Full name).

The solution for the challenges that we face

1. In the current article, we will review:

How to use the required PowerShell command syntax for importing the information from the contact CSV file and discuss the logic and the structure of this PowerShell command

In the next article, we will review how to use a PowerShell script, that I have created that can simplify the task of working with complicated PowerShell commands.

2. In the current article, we will review the required data structure of the CSV file, which we need to prepare for the “import phase.”

In the next article, we will review some tricks for managing and manipulating the data in the contact CSV file.

Implementing Bulk contact import using CSV file

The option of – “bulk Import”, enables us to quickly and efficiently migrate information about hundreds or even thousands of mail contacts, from a previous mail infrastructure to Exchange Online.

The way that we export the information from the “previous mail infrastructure” depends on the appropriate mail infrastructure that we use.

The important thing that we should know regarding the “exported data” is, that the data should be exported using CSV format.

The term CSV, stand for – comma-separated value. The CSV format, define a data structure that is similar to the concept of a table.

In the following screenshot, we can see an example of a CSV file

The information includes “column header” (A) and “data rows” (B).

For example – the following values are the “column header”:

ExternalEmailAddress, Name, FirstName, LastName

The values that appear below the “column header” section are the values that populate the different columns.

For example, the following string – darrenp@fabrikam.com, Darren Parker, Darren, Parker, will be “translated” in the following way:

ExternalEmailAddress	darrenp@fabrikam.com
Name	Darren Parker
FirstName	Darren
LastName	Parker

In the following screenshot, we can see the same CSV file, when we use Excel to edit the file.

It is easy to notice that when using Excel to edit a CSV file, the information is presented in an easier to understand format.

Exchange Online external contact object and mandatory data fields

The “external contact object” in Exchange based environment, can include many properties
such as – name, E-mail address, phone number, office address and much more.

The important thing that I would like to emphasize is that the “external contact object” in Exchange Online based environment, must have three mandatory data fields:

1. *Display name:
2. *Alias:
3. *External email address:

In the following screenshot, we can see these mandatory data fields with the asterisk character, that “tell us” that these is a mandatory data field.

The point is that when we are exporting the contact data from the existing mail infrastructure that we use, we must ensure that the exported data include this “data fields”.

Technically, the exported data does not need to include the “Alias” field but, must include the following data fields:

• External Email Address (in our CSV file, this field appear using the column header
  name – ExternalEmailAddress).
• Display name (in our CSV file, this field appear using the column header name – Name).

In case that you are wondering what about the additional mandatory data field – the “Alias,” the answer is that if the exported data (the CSV file), doesn’t include this field, the import process to Exchange Online, “know” how to automatically generate “Alias” field.

The import process from the CSV file, will take the value that is stored in the CSV field “Name” and use this value for the “Alias” value.

If we want to be more accurate, the import process will take the two separated words from the name field (the private name and the last name), remove the space that exists between these two words, and creates a “new value” – the “Alias“.

In the following screenshot, we can see an example of the “mapping logic” that implemented between the data stored in the CSV file, and the Exchange Online “external contact object.”

Notice that:

• The field names (row header) that appear in the CSV file, are different from the Exchange Online external contact fields names.
• The CSV file doesn’t include the “Alias” field. The import process will generate this field automatically if we provide him value in the “Name” field.

Import external contact information to Exchange Online using PowerShell the process logic

The process which we import the external contact information from the CSV file into Exchange Online, consists of two different phases:

Phase 1 – in this phase, we use the import process that is implemented by using PowerShell, for creating the required external contact “object” in Exchange Online.

In this phase, the import process relates only to the mandatory fields that were mentioned, and to the additional column – the column named FirstName and LastName.

In other words, the PowerShell import process “ignore” the rest of the information in the CSV file.

Phase 2 – this is the phase, in which we “fill in” the missing data that was “ignored” in the first step.

The PowerShell import process, adds the “additional data” such as phone number, office address and so on, to the existing Exchange Online external contacts.

In the following screenshot, we can see an example of a CSV file that includes the information about the contacts that we need to import.

In the first phase, we use the “mandatory data fields” + the column named – FirstName and LastName.
In the second import phase, the rest of the data field (colored in orange) will be merged into the existing Exchange contacts.

Implementing the task of – Import external contact information to Exchange Online using PowerShell

The task list that we need to complete, for implementing the task of “Import external contact information to Exchange Online using PowerShell”, include the following tasks:

1. Export the contacts information from your existing mail infrastructure to CSV file.
2. Verify that the CSV file includes the required data structure that we use for importing the information to Exchange Online. For example, the name of the columns header and the mandatory fields.
3. Connect to Exchange Online PowerShell
4. Run the “Phase 1” PowerShell commands, that will import the information from the CSV file and create the external contact in Exchange Online.
5. Run the “Phase 2” PowerShell commands, that will import the “additional information” from the CSV file such as phone number, and populate the information to the existing external contact respectively

1. Connect to Exchange Online PowerShell

To be able to run the PowerShell commands specified in the current article, you will need to Connect to Exchange Online PowerShell.

Start Windows PowerShell as administrator and run the cmdlet Connect-ExchangeOnline.

Connect-ExchangeOnline

2. Create the external contacts object in Exchange Online

The PowerShell command syntax that we use for importing the data from the existing CSV file is:

Import-Csv <Path> | ForEach {New-MailContact -Name $_.Name -DisplayName $_.Name -ExternalEmailAddress $_.ExternalEmailAddress -FirstName $_.FirstName -LastName $_.LastName}

The PowerShell script, includes two separate parts:

The first part is responsible for importing the information from the CSV file and save this information in a temporary storage on the desktop RAM (Import-Csv ).

The second part of the PowerShell script is responsible for:

• Fetch the information that was retrieved in the previous step
• Start to create a new external contacts object in Exchange Online based on this information (New-MailContact)

The PowerShell import process is implementing a “loop” (ForEach), by reading each “row” of the CSV file, creating the required contact object in Exchange Online and repeating the process over and over again until he reaches the “last row” in the CSV file.

An additional concept that we should understand is the “mapping process.”
In our scenario, we map information from the CSV file to the “external contact” in Exchange Online

• The “Red” parameters are the name of the data fields, that used by Exchange Online.
• The “Blue” parameters are the column headers names that appear in the CSV file. Technically speaking, the name of the column’s headers in the CSV file, are just an arbitrary name whom we choose. There are no specific naming conventions that we should use, besides the recommendation to use the column header name without spaces.

In the following screenshot, we can see an example of the PowerShell command, which we run in the PowerShell console (after connecting to Exchange Online PowerShell).

The command reads the information from the CSV file (in our scenario, the file is saved in c:\temp folder and the file name is ExternalContacts_01.csv).

In the following screenshot, we can see the contact successfully created in Exchange Online.

3. Add information on the properties of the external contacts

In this phase, we “populate” the external contact object that was created in the first phase, with property information such as – Office phone, Home phone, City and so on.

In this phase, we use the same concept of “import information” from CSV files but now, we use the PowerShell command – Set-Contact

The contact is already created and in this phase, we need to update (Set-Contact) the existing Exchange Online contact.

The PowerShell “know” that he need to relate to each of the contact that exists in the CSV file because we instruct him to run in a loop (ForEach) and find all of the contacts that appears “under” the column Name (Set-Contact $_.Name).

The PowerShell syntax of the command that we use is:

$Contacts = Import-CSV <path>
$contacts | ForEach {Set-Contact $_.Name -StreetAddress $_.StreetAddress -City $_.City -StateorProvince $_.StateorProvince -PostalCode $_.PostalCode -Phone $_.Phone -MobilePhone $_.MobilePhone -Pager $_.Pager -HomePhone $_.HomePhone -Company $_.Company -Title $_.Title -OtherTelephone $_.OtherTelephone -Department $_.Department -Fax $_.Fax -Initials $_.Initials -Notes $_.Notes -Office $_.Office }

In the following screenshot, we can see an example of the PowerShell command
that we run in “phase 2”:

In the following screenshot, we can see that the second phase also completed successfully. The contact information added to the contact that’s created in the first phase.

You can download sample Excel file named: ExternalContacts_01.csv.

In the next article, we will look into Bulk Import Contacts to Exchange Online (Office 365) Using PowerShell script | Part 2#2.