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

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

[sdm_download id=”43473″ fancy=”1″ new_window=”1″ button_text=”Download” color=”blue” ]

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:

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.

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

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

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.

The information was successfully imported

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

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

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

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.

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

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.

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

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

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

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.

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:

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.

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:

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.

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:

[sdm_download id=”43473″ fancy=”1″ new_window=”1″ button_text=”Download” color=”blue” ]