Skip to main content

Updating Contact Information in Office 365 from CSV via PowerShell

Some time ago, we did an export of our Domino contacts into the Office 365 address book. It was mostly successful and we got the users and their email addresses but missed a lot of detail on the phone numbers, company names and fax numbers. 

At the time it didn't matter but recently we reached a point where we needed this information to be present. 

The process was much fiddlier than it should have been, so here's how we did it.

Exporting out of Domino

This was easy, literally a five minute job for about 5000+ contacts. Domino has menu options to export as CSV, so I won't go into detail here.

The end result is that you should have a CSV file that looks something like this;

ExternalEmailAddress,FirstName,LastName,Name,Title,Company,Phone,MobilePhone,Fax,StreetAddress,City,StateorProvince,PostalCode,CountryOrRegion
atano@clonewars.com,Ashoka,Tano,Ashoka Tano,,Cartoon Network,08 8988 9889,,,,,,,
ynotfar@dagpbah.com,Yoda,Not Far,Yoda Not Far,Jedi Master,Food of this Kind Ltd,,,,GPO Box 1234,Dagobah,,1556,Dagobah System
pkoon@jeditemple.com,Plo,Koon,Plo Koon,Jedi Master,Plo's Mask Emporium,03 5468 4889,0417 650 456,03 5406 8790,"Jedi Temple, Suite 66",Coruscant,COR,1234,Central Systems
spalpatine@dualidentities.com,Sheev,Palpatine,Sheev Palpatine,Chancellor,Always Two Limited,02 1264 5640,0442 548 987,02 8987 9802,"Red Suite, Level 4000",Coruscant,,,Central Systems

Ideally, you'll be able to paste that test data into notepad, save as CSV and have a working template but just in case you can't, it's essentially 14 fields;



  • ExternalEmailAddress
  • FirstName
  • LastName
  • Name
  • Title
  • Company
  • Phone
  • MobilePhone
  • Fax
  • StreetAddress
  • City
  • StateorProvince
  • PostalCode
  • CountryOrRegion



PowerShell

From here, you start PowerShell (in Administrator Mode) and connect to Office 365

Set-ExecutionPolicy RemoteSigned

$UserCredential = Get-Credential

You'll be prompted to sign in with an Office 365 ID that has global admin rights.

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $Session

In this next step, we're presuming that your CSV file is saved as C:\temp\AllPeople.csv.  If you saved it elsewhere or under a different name, you'll need to update that line.

$Contacts = Import-CSV C:\temp\AllPeople.csv

The next section says which fields to import. We discovered that there were a lot of problems with this statement. 

  • If you include fields which aren't in your CSV, then no fields get imported. 
  • If ONE field breaks the rules (eg: a company with a length of over 64 characters, then that will eventually halt the processing of the entire input file). In our case, I used Excel to return Left(CompanyName, 62) where Len(CompanyName) > 62.  It fixed a big problem. 


$contacts | ForEach {Set-Contact $_.Name -StreetAddress $_.StreetAddress -City $_.City -StateorProvince $_.StateorProvince -PostalCode $_.PostalCode -Phone $_.Phone -MobilePhone $_.MobilePhone -Company $_.Company -Title $_.Title -Fax $_.Fax}

You should still expect quite a few errors when running this command as names which don't match perfectly from one system to another, particularly those with accent characters, will most likely fail.

As usual, you'll want to finish up with;

Remove-PSSession $Session
To clear any variables out 

and 
Exit
to close the Powershell window. 

You should be able to see the results in Office 365 immediately. 

Comments

Popular posts from this blog

How to Change Your Notification Options for New Lotus Notes Mail in version 8.x

Don't worry, I'm not patronizing you (my readers), I just decided to re-document this for one of our internal users and thought you might want to be able to use it in your own user documentation. WHAT IS THIS DOCUMENT ABOUT? Some people who don't get a lot of mail, like to be notified when such an event occurs. Notification can be; via a sound via a pop-up box via the system tray (where the computer clock is) The pop up box looks like this; Other people, who like myself, get too much mail would rather not be notified. The aim of this document is to tell you how (and where) to turn these options on and off. CHANGING YOUR SETTINGS To change your settings from the Notes 8.x client; On the Menu, click File , then Preferences... On the left hand side , click on the little plus sign to the left of Mail to expand the options. Click on the option marked Sending and Receiving . In the middle section, under receiving, you can control your notifications. If you untick the box mark

How to Create an Auto-Response Mail Message in Lotus Notes 8.5.3+

Why would you do this? Suppose that you have an externally accessible generic email address for your company; support@mycompany.com or info@mycompany.com. You might expose this to the web and allow people to send messages to you. Setting up an auto-response email will tell the senders that their message reached its destination and that it will be dealt with accordingly.  It's also good practice to include links to FAQs or other useful information. Why 8.5.3 The techniques we'll be using here work in older versions of Notes but some of the options seem to have moved around in 8.5.3.  I figured it was a good time to show you where they've moved to. The Procedure Start Domino Designer and open the Mail file to be modified.  A really quick way to do this is to right-click on the application tab and choose "Open in Designer". In the Left hand panel of designer, expand Code and then double-click Agents.  A new window should appear. Click the action

How to Do a Mail Merge to Email using Lotus Notes

Why do one? In today's "green" world, it makes much better sense to send out emails than letters but you still want to personalize them. Sadly, by itself Lotus Notes doesn't support mail merge to email. Of course, we know that outlook does (but then it lets anyone and anything send emails for you - even when you don't want them to). So, how to do it in Notes? OpenNTF The first port of call is OpenNTF ( http://www.openntf.org/ ). This place is full of great things but most of them are really badly documented. Still, these guys give things away for free and they develop in their spare time, so we should be grateful for what we get. There's a great little project there called MailMerge Excel to Notes . Go there, click on releases and download the ZIP file. Getting to the Code The installation is tricky though I've noted that since I asked the author about the install, it's been updated (so maybe these steps are less necessary). Unzip the files to somewher