Bulk Import Contacts to Exchange Online (Office 365) Using PowerShell script | Part 2#2 5/5 (3)

The current article is the second article in our two article series, in which we review the process of Bulk Import contacts to Exchange Online (Office 365) using PowerShell.
The two topics that we will review in this article are:

  1. Using a “Bulk Import contacts” PowerShell script.
    Using the PowerShell script will simplify and streamline the process of importing data from the CSV file and also, includes other related tasks.
  2. How to manipulate the information in the CSV file
    The underlying assumption is that the information that exported from the current mail infrastructure is not entirely compatible with the required data structure, which used for importing data to Exchange Online.
    In this section, we will review some examples of – how to use Excel formulas to manipulate existing data.

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

Using the import contacts PowerShell script

To simplify the procedure of Bulk Import contacts to Exchange Online, I have prepared a PowerShell script that includes the following options:

  1. Login in using your Office 365 Administrator credentials – this option will help us to create a remote PowerShell session that will connect us to Exchange Online.
  2. Import External contacts from a CSV file – this option will help us to run the two phases of the PowerShell command – phase 1 – import the information from a CSV file and create the required external contacts in Exchange Online and phase two – import the properties for each contact.
  3. Delete External contacts from a CSV file – in case that we want to delete external contact who was imported to Exchange Online from the CSV file, we can use this option.
  4. Import Distribution Group members from a CSV File – this option that will help us to populate the existing distribution group with the external contacts who was imported from the CSV file.
  5. Export information about external contacts – this option will help us to export data about existing Exchange Online external contacts to three types of file formats: TXT, CSV, and HTML

 

For your convenience, I have “Wrapped” all the PowerShell commands that were reviewed in a PowerShell Script named:
Import-Contacts.ps1
You are welcome to download the script and use it.

download-button-02.png

 

Running the import-contacts.ps1 PowerShell script

After you have downloaded the PowerShell and extract the ZIP file, save the PowerShell script in a specific folder.

In our scenario, we have saved the import-contacts.ps1 to a folder named script in C: drive.

In case that this is the first time that you run PowerShell on your desktop, you will need to run the PowerShell console as administrator and run the PowerShell command:

Set-ExecutionPolicy Unrestricted -force

We will run the import-contacts.ps1 from the PowerShell console, by using the following steps:

1. “Navigate” the PowerShell script location PowerShell script

To be able to execute the PowerShell script, we need to navigate to the path in which the PowerShell script located.
In our scenario, the PowerShell script is located in the c:\script folder.

Type the following command: cd c:\script and ENTER

2. Provides the PowerShell script name

To execute a PowerShell script, we need to start the command with the following characters – “.\” and then, type the name of the PowerShell script.

For example: .\ import-contacts.ps1

Another useful option that we can use is the PowerShell autocomplete feature.
Instead of writing the “full name” of the PowerShell script, we can type the first letters of the PowerShell script name, and let PowerShell complete the rest of the script name.

For example, to call a PowerShell script, we need to write the following characters – .\ and then, type the first letter\s of the PowerShell script such as imp.
To start the l autocomplete feature, we hit the TAB key.

After “hitting” the TAB Key, The PowerShell console will automatically complete the rest of the PowerShell script name by himself.

Bulk Import Contacts to Exchange Online Using PowerShell script -01

The first task that we need to complete is – connecting to Exchange Online using remote PowerShell.

To start the remote connection process, select menu “0” and hit the ENTER key

Bulk Import Contacts to Exchange Online Using PowerShell script -02

Pop out credential’s windows appear.
We will need to provide the Office 365 global administrator credentials.

Bulk Import Contacts to Exchange Online Using PowerShell script -03

Menu 1 – Import External contacts from CSV file

Using menu “1“, we will import the information from the CSV file, create external contact’s objects in Exchange Online and import the contacts properties.

In this phase, we assume that we already have exported the required contact information to a CSV file.

Type the number “1” and then hit the ENTER key.

A prompt appears, asking us to provide the path to the CSV file.

In our scenario, the CSV file name is import-contacts.ps1, and the file is located in the Temp folder on drive C:

To start the import process hit the ENTER Key.

Bulk Import Contacts to Exchange Online Using PowerShell script -04
The information was successfully imported

Bulk Import Contacts to Exchange Online Using PowerShell script -05

In the following screenshot, we can see that the external contact from the CSV file created in Exchange Online.

Bulk Import Contacts to Exchange Online Using PowerShell script -06

Menu 3 – Import Distribution Group members from a CSV File

Using this option, we will add all the external recipients that appear in the CSV file to an existing distribution group.

This option enables us to complete a common requirement that is related to the process of managing external contacts.

In our scenario, we will create a new distribution group.

The distribution group name is – External Contacts

Bulk Import Contacts to Exchange Online Using PowerShell script -07

Type the number “3” and then hit the ENTER key.

Bulk Import Contacts to Exchange Online Using PowerShell script -08

We will need to provide two parameters:

1. The Path to the CSV file

A prompt appears, asking us to provide the path to the CSV file.

In our scenario, the CSV file name is ExternalContacts_01.csv, and the file is located in the Temp folder on drive C:

To continue to the next step, hit the ENTER Key.

2. The distribution group E-mail address

Provide the E-mail address of the distribution group
To start the import process, hit the ENTER Key.

Bulk Import Contacts to Exchange Online Using PowerShell script -09

In the following screenshot, we can see that the external recipient that appears in the CSV file successfully added as a member of the External Contacts distribution group

Bulk Import Contacts to Exchange Online Using PowerShell script -10

Menu 4 – Export information about external contacts

Using the export option, we will be able to export the information about the existing Exchange Online external contacts.

The PowerShell command will export the information about the external contacts in three file formats: TEXT, CSV, and HTML.

The PowerShell command will create a new folder named – INFO in C: drive

Type the number “4” and then hit the ENTER key.

Bulk Import Contacts to Exchange Online Using PowerShell script -11

The information was exported and its “waiting” for us in the INFO folder.

Bulk Import Contacts to Exchange Online Using PowerShell script -12

In the following screenshot, we can see that the PowerShell command exports the information using three types of file formats.

Bulk Import Contacts to Exchange Online Using PowerShell script -13

This is an example of the information that was exported to HTML file format:

Bulk Import Contacts to Exchange Online Using PowerShell script -14

How to manipulate the information in the CSV file

In this section, we review how to “manipulate” the information that existed in the CSV file that we are going to import using PowerShell to Exchange Online.

The underlying assumption is that in many scenarios, the information that we export is not “perfect” and doesn’t include all the required information about a particular contact.

For example, a scene in which the “private name” of the contact is missing (doesn’t exist) or a scenario in which the information about the contact doesn’t include the contact’s company name.

Using a tool such as Excel can help us to “play” with the existing information by suing Excel various Excel Formulas.

In the following section, we will review three examples of the way that we can use Excel formula for manipulating existing data.

Example 1 – Extract name from E-mail address

Scenario description

When looking in our CSV file, we have noticed that the “First name” is missing for some of our contacts.

We would like to “fill-in” the information by “stealing” the individual name from the contact E-mail address.

Manipulating the data in the CSV external content file -01

To be able to complete this task, we will use Excel function named – FIND.
In our scenario, we instruct the FIND function to go to the cell that includes the contact E-mail address and “take” the left part of the E-mail address (the alias of the recipient).

The syntax of the function that we use is:

=LEFT(E2,FIND(“@”,E2)-1)

Manipulating the data in the CSV external content file -02

Example 2 – Extract company name from E-mail address

Scenario description

When looking in our CSV file, we have noticed that the “company name” is missing for some of our contacts.

We would like to “fill-in” the information, by “stealing” the company name that appears as part of the E-mail address domain name.

Manipulating the data in the CSV external content file -03

To be able to complete this task, we will use Excel function named – FIND.
In our scenario, we instruct the FIND function to go to the cell that includes the contact E-mail address, and “take” the left part of the E-mail address (the alias of the recipient).

The syntax of the function that we use is:

=LEFT(REPLACE(E4,1,FIND(“@”,E4),””),FIND(“.”,REPLACE(E4,1,FIND(“@”,E4),””))-1)

Manipulating the data in the CSV external content file -04

Example 3 – Combine values from different cells + add space

Scenario description

When looking in our CSV file, we have noticed that the “Name” (also described as – Display name or Full name) is missing for some of our contacts.

We would like to “fill-in” the information, by “stealing” the company name which appears as part of the E-mail address domain name.

Manipulating the data in the CSV external content file -05

To be able to complete this task, we will use Excel function named – CONCATENATE.
In our scenario, we instruct the CONCATENATE function to go to the Excel cell that includes the First name, and to the Excel cell that includes the Last name and “combine” these two separated values into one value.

The syntax of the function that we use is:

=CONCATENATE(E8,” “,F8)

Manipulating the data in the CSV external content file -06

 

You can downlaod teh Excell file:
Sample-Contacts.xls

download-button-02.png

Now it’s Your Turn!
It is important for us to know your opinion on this article

Summary
Article Name
Bulk Import Contacts to Exchange Online (Office 365) Using PowerShell script | Part 2#2
Description
To simplify the procedure of Bulk Import contacts to Exchange Online, I have prepared a PowerShell script that includes the following options: Import External contacts from a CSV file, Delete External contacts from a CSV file, Import Distribution Group members from a CSV File, Export information about external contacts
Author
Publisher Name
o365info.com
Publisher Logo

Please rate this

Print Friendly

Related Post

Eyal Doron on EmailEyal Doron on FacebookEyal Doron on GoogleEyal Doron on LinkedinEyal Doron on PinterestEyal Doron on RssEyal Doron on TwitterEyal Doron on WordpressEyal Doron on Youtube
Eyal Doron
Share your knowledge.
It’s a way to achieve immortality.
Dalai Lama

Leave a Reply

Your email address will not be published. Required fields are marked *