Tech tutorials Bulk User Profile Data Import Into SharePoint Online

By Insight Editor / 12 May 2017 , Updated on 16 May 2019 / Topics: Data and AI Microsoft 365

The SharePoint Online User Profile service provides an out-of-the-box data synchronization with Active Directory (AD). However, it’s a common scenario for non-AD user data to be brought into a user’s profile. Office 365 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 article will cover how to execute this scheduled job using the SharePoint Online commandlets in the SharePoint Online Management Shell.

Creating/storing the 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 look up the user with — in this scenario, it will be WorkEmail.

{ "value" : [ 
{	 
"WorkEmail":"JohnDoe@company.com",
   	"EmployeeID":"ABC123",
"IsFullTime":"1", 
"HireDate":"12/01/2009"
},
{	 
"WorkEmail":"SteveSmith@company.com",
   	"EmployeeID":"DEF456",
"IsFullTime":"0", 
"HireDate":"4/10/2017"
},
                      ]
}

This sample data contains data for three 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 SharePoint Online. If the job encounters errors, it will create a folder containing a text file with the error logs.

PowerShell script

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

Get user credentials.

You’ll need the user credentials for a user who is set up 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 Office 365 tenant.

Since the job is part of the Office 365 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)
$context.Load($o365Tenant)

Set up user lookup information.

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

#Field to lookup on in the User Profile Service
$userIdType=[Microsoft.Online.SharePoint.TenantManagement.ImportProfilePropertiesUserIdType]::Email

#Name of JSON identifier
$userLookupKey="WorkEmail" 

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

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 are not 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 the 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 name/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 guide of the Office 365 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 
$context.ExecuteQuery() 

$workItemIdValue = $workItemId.Value.ToString() 

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