One of the most highly-demanded, but yet sparsely-documented features in Office 365 that I seem to be addressing in ever-increasing frequency is the need for better (read: bulk) management of the Global Address List.
Let me be the first to tell you that there has not been a single GAL I’ve moved from an on-premise Exchange environment to 365 that wasn’t chock full of garbage. Having worked for a company that dealt primarily in the warehousing and transformation of EMR data, I can tell you that I am a huge proponent of clean and portable data. These address lists in question are 100% subject to the GIGO (Garbage In, Garbage Out) theory, and a healthy chunk of my time is spent on data cleanup when I’m asked to assist with migrating data out of personal contact lists, on-prem GALs, and public folders. A good, clean set of data is VITAL to the success of maintaining a healthy Global Address List, and that’s where we’re going to start our discussion.
This process completely revolves around CSV files - personal contacts and on-prem GALs have to be exported to CSV, and the source file that we’ll use to import back into 365 will also be CSV. That being said, you will want to make sure you have a good spreadsheet program on hand in order to do this process. For the purposes of this blog post I will be using Excel, but the open-source options work just as well.
IMPORTANT: One thing to note is that the Powershell cmdlet we use to create the contact “skeleton” has 4 mandatory parameters that are passed to it; one of them being an email address. The reason I bring this up is that you cannot use the bulk upload utilities in Powershell to create a contact that doesn’t have an email address. Period. If your current GAL or personal Contacts contain entries without email addresses, you will need to either add a dummy email address (NOT recommended), or exclude them from the GAL.
Bulk import into your GAL on 365 is actually quite simple once the data has been scrubbed, and essentially consists of making two separate passes against the source CSV file in Powershell. The first pass creates a contact object with only 4 fields: Email Address, Display Name, First Name, and Last Name. The second pass then serves to “fill in the blanks” in this contact object by populating the remaining fields of your choosing, such as Address, City, State, Zip, Phone Number, etc. I will outline the steps below in more detail.
Step 1 - Data Scrubbing
Once you have your existing contact list(s) in CSV format, it’s time to import that data into the new CSV file I am providing below. In my opinion, this is the most important step in the entire process, and will also eat up most of your time. Take the time to make certain that the data you put into this file is accurate and clean.
Download the sample CSV file here
As I stated before, if any of the contacts in your source lists don’t have email addresses, do not put those contacts into this new CSV. You will not be able to import them into the GAL in 365.
Step 2 - First pass, creating the initial contact containers
PREREQUISITES:
- You will need Powershell and WinRM v2.0 installed, configured, and working correctly to perform this and the remaining steps. I would recommend having Powershell and WinRM v3.0, however. You can find the steps on how to do this in a previous blog post.
- You will need to connect Powershell to Office 365 using global admin credentials. If you don’t know how to do this, I’ve outlined the steps in a previous tech tip.
Once you’ve got your new CSV file populated with clean and accurate data, we now need to run a Powershell cmdlet against it. This will serve to create our contact “skeletons” that we will subsequently fill in with more details in the next step. For the sake of simplicity, I would suggest that you save that CSV file to the root of your C:\ drive and name it something short and simple - “365contacts.csv” for example.
Fire up Powershell, and connect to your 365 account with a Global Admin user.
Run the following command (assuming you saved the CSV as described above):
Import-Csv C:\365contacts.csv|%{New-MailContact -Name $_.Name -DisplayName $_.Name -ExternalEmailAddress $_.ExternalEmailAddress -FirstName $_.FirstName -LastName $_.LastName}
Depending on how many contacts you’re creating, this could take up to a few minutes.
Step 3 - Second pass, filling in the blanks
Now that your GAL has been populated, it’s time to fill in the remaining gaps. As I mentioned before, this is the step that will complete the fields like street address, phone number, city, state, zip, etc. Something to keep in mind: even though the command below states to import the majority of the column headings, don’t worry if one of those cells is blank for some of your contacts. If it’s blank, Powershell won’t import it.
Important: The “manager” field is the only one of the column headings that can be a major thorn in your side. It’s the only field that if it’s not populated, it will cause a problem. It is for this reason that unless you know the manager for EACH person in your GAL, you omit it from the Powershell command altogether. (You can also omit any parameters that you know are blank in the CSV - so if none of your contacts have an entry in the Pager field, you can remove -Pager $_.Pager from the command before you paste it in.)
Here’s the two commands that you need to run (separately) for Pass 2:
$365contacts = Import-CSV C:\365contacts.csv
$365contacts | 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 -Manager $_.Manager}
That’s it! Once Powershell has finished, it will come back to a command prompt. You can now view any of your imported contacts by going into your Exchange Control Panel in the Microsoft Online Portal, and clicking Contacts. (You can also add one-off contacts to your GAL from here, as well as do one-off edits and deletions.)