The two topics that we will review in this article are:
- 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. - 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
- Bulk Import Contacts to Exchange Online (Office 365) Using PowerShell | Part 1#2
- 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:
- 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.
- 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.
- 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.
- 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.
- 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
The Import contacts from a CSV PowerShell menu script will help you to implement “bulk import” of contacts from a CSV file to – Exchange Online. The imported contacts will appear as part of the Exchange Online GAL (Global address List).
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:
You can downlaod the Excell file:
Sample-Contacts.xls
The Import contacts from a CSV PowerShell menu script will help you to implement “bulk import” of contacts from a CSV file to – Exchange Online. The imported contacts will appear as part of the Exchange Online GAL (Global address List).
It is important for us to know your opinion on this article
It’s a way to achieve immortality.
Dalai Lama
