Smarter ideas worth writing about.

Bulk User Profile Data Import into SharePoint Online

The SharePoint Online (SPO) User Profile service provides an out of the box data synchronization with Active Directory (AD), however it is a common scenario for non-AD user data to be brought into a user’s profile. O365 has a job capable of reading a text file residing in a SharePoint document library that contains a JSON string with user data to be imported. This blog post will cover how to execute this scheduled job using the SharePoint Online commandlets in the SharePoint Online Management Shell.

Creation/Storing of JSON file

For this job to run successfully the file containing the JSON object needs to be in a specific format. The format will consist of a single top level name/value pair, with the name being the string “value” and the value being an array of JSON objects. Each of these JSON objects in the array will be name/value pairs of a single piece of data about the user. One of these name/value pairs will be used as the value to lookup the user with, in this scenario it will be WorkEmail.

{ "value" : [ 

This sample data contains data for 3 user profile properties that will be updated (EmployeeID, IsFulltime, and HireDate). There are a few things to note regarding the format of the JSON string in the text file.

  • The text of every name and value in the user data objects must be wrapped in quotes, regardless of the logical data type. This job processes all values as strings and will do a conversion to the data type of the user profile property while processing.
  • Boolean values must be passed in as a 0 or 1, as seen with IsFullTime.

Once the file is created it will need to be uploaded to a SharePoint document library in SPO. If the job encounters errors, it will create a folder containing a text file with the error logs.

PowerShell Script

Below are the steps different steps for the execution of the script.

Get User Credentials

Going to need the user credentials for a user that is setup as a SharePoint Administrator in the tenant.

#Get user credentials
$userName = Read-Host -Prompt 'Enter username'
$password = Read-Host -Prompt 'Enter password' -AsSecureString 

Get an instance of the O365 tenant

Since the job is part of the O365 tenant the first step is to get an instance of the tenant.

#Get tenant instance
$adminCenterUrl = Read-Host -Prompt 'Enter admin center url'
$uri = New-Object System.Uri -ArgumentList $adminCenterUrl
$context = New-Object Microsoft.SharePoint.Client.ClientContext($uri)

$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)
$o365Tenant = New-Object Microsoft.Online.SharePoint.TenantManagement.Office365Tenant($context)

Setup User Lookup information

For the job to know what account to update the field the lookup needs be performed on and the name/value pair in the user data object to use needs be specified. The User Profile fields that can be looked up on are Email, CloudId, and PrincipalName.

#Field to lookup on in the User Profile Service

#Name of JSON identifier

$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)
$o365Tenant = New-Object Microsoft.Online.SharePoint.TenantManagement.Office365Tenant($context)

Create Mapping from JSON to User Profiles

The next step is to create a mapping of the name/value pairs in the JSON objects to their respective User Profile properties. Every name/value pair in the JSON objects must have a mapping to a property, there cannot be any unused name/value pairs. When adding the mappings, the first parameter is the JSON name and the second parameter is the User Profile property name. The User Profile properties being mapped to cannot be editable by the user.

#Create property mapping between JSON data and user profile properties
$propertyMap = New-Object -type 'System.Collections.Generic.Dictionary[String,String]'
$propertyMap.Add("EmployeeID", "CSGEmployeeID")
$propertyMap.Add("IsFullTime", "CSGIsActive")
$propertyMap.Add("HireDate", "CSGHireDate") 

Initiating Import Scheduled Job

Now that the mapping is complete the import job can be initiated. The function to execute this job (QueueImportProfileProperties) has four parameters.

  • idType
    • The ImportProfilePropertiesUserIdType enum value for what field to perform lookup on
  • sourceDataIdProperty
    • The name/value pair from the user data object to use for the lookup
  • propertyMap
    • The mapping of the namve/value pairs from the user data object to a user profile property
  • sourceUri
    • The Uri to the text file to import

The function will return the guid of the O365 job.

#Kick off import job 
$fileUrl = Read-Host -Prompt 'Enter file url' 
$workItemId = $o365Tenant.QueueImportProfileProperties($userIdType, $userLookupKey, $propertyMap, $fileUrl) 

#Execute the CSOM command for queuing the import job 

$workItemIdValue = $workItemId.Value.ToString() 

Write-Host "Import job created with following identifier:" $workItemIdValue


ImportProfilePropertiesUserIdType Enumeration  

QueueImportProfileProperties Function 


About The Author

SharePoint Developer
Frank has been a SharePoint developer since SharePoint 2010 was first released.  He has worked on a variety of SharePoint implementations, including intranets, extranets and public sites.