Update user profile custom properties in SharePoint online from Excel sheet if any blank.

Question

I need to update 4 custom fields properties of SPO user profile form excel sheet.
Excel sheet column as below

1. email 2. country 3 company 4. department 4 full name 5 display name 6 AADStutus 7 SPUpdateStatus 8 error

I have 45000 data in sheet, all need to update custom field of SPO if any blank.

[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls12

References ###

   Add-Type -Path 'c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'  
   Add-Type -Path 'c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'  
   Add-Type -Path 'c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll'  
   Add-Type -Path 'c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll'  
   Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue  

cls

$rootPath =$PSScriptRoot
if( (Test-Path "C:\Script\Cred\SV_Cred1.xml") -eq $false)
{
$MyCredentials=GET-CREDENTIAL –Credential Svc.cdpadmin@test .com | EXPORT-CLIXML "$rootPath\Cred\SV_Cred1.xml"
}

$credentials = IMPORT-CLIXML "C:\Script\Cred\SV_Cred1.xml"
$userName = $credentials.UserName
$pwd = $credentials.Password

$Global:Ctx = New-Object Microsoft.SharePoint.Client.ClientContext(https://Test-admin.sharepoint.com/)
$Global:Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $pwd)

$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Global:Ctx) -ErrorAction Inquire

Function Get-FileName($initialDirectory){
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null

    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog  
    $OpenFileDialog.initialDirectory = $initialDirectory  
    #$OpenFileDialog.filter = "Excel (*.xlsx)| *.xlsx"  
    $OpenFileDialog.ShowDialog() | Out-Null  
    $OpenFileDialog.filename  

}

function updateProfile([string] $email, [string] $company, [string] $department, [string] $country, [string] $employeeName)
{

    $targetAccount = ("i:0#.f|membership|"+ $email) ;  
   # $targetAccount = $email ;  
    $PeopleManager.SetSingleValueProfileProperty($targetAccount, "Test-EmployeeName", $employeeName) ;  
    $PeopleManager.SetSingleValueProfileProperty($targetAccount, "Test-Company", $company) ;  
    $PeopleManager.SetSingleValueProfileProperty($targetAccount, "Test-Department", $department) ;  
    $PeopleManager.SetSingleValueProfileProperty($targetAccount, "Test-Country", $country) ;  
    $Global:Ctx.ExecuteQuery()  ;   

}

function updateProfilecountry([string] $email, [string] $company)
{

    $targetAccount = ("i:0#.f|membership|"+ $email) ;   
    #$PeopleManager.SetSingleValueProfileProperty($targetAccount, "Test-Country", $country) ;  
    $PeopleManager.SetSingleValueProfileProperty($targetAccount, "Test-Company", $company) ;  
    $Global:Ctx.ExecuteQuery()  ;   

}

Variables to be changed ###

 #   cls     

    $SheetName = "Sheet1"  
    $FileName =  Get-FileName "$PSScriptRoot"    
    $ObjExcel = New-Object -ComObject Excel.Application  
    $WorkBook = $ObjExcel.Workbooks.Open($FileName)  
    $Sheet = $WorkBook.Worksheets.Item($SheetName)  
    $ObjExcel.Visible=$true  
    $RowMax = ($Sheet.UsedRange.Rows).count  
    $ColMax = ($Sheet.UsedRange.Columns).count  


    #$Sheet.Cells.Item(1, $ColMax+1).Value() = "Status" $RowMax  


         if($Sheet.Cells.Item($i, 8).text -eq "Success")  
         {  
             $Sheet.Cells.Item($i, 9).Value() = ""  
             continue  
         }  


        write-host "Row number $i"  

        $email = $Sheet.Cells.Item($i, 1).text  
        #$firstname = $Sheet.Cells.Item($i, 4).text  
        $country = $Sheet.Cells.Item($i, 2).text  
        $company = $Sheet.Cells.Item($i, 3).text  
        $department = $Sheet.Cells.Item($i, 4).text  
        $employeeName = $Sheet.Cells.Item($i, 5).text  



        try{  
             Write-Host $email $company $department $country $employeeName  
            updateProfile $email $company $department $country $employeeName  

            Write-Host "$email - Success" -ForegroundColor Yellow  
            $Sheet.Cells.Item($i, 8).Value() = "Success"  
        }  
        catch  
        {  
            Write-Host "$email - Failed" -ForegroundColor Red  
            $Sheet.Cells.Item($i, 8).Value() = "Failed"  
            $Sheet.Cells.Item($i, 9).Value() = $_.Exception.Message  

        }        
    }  

$ObjExcel.Quit()

Answer 1

Hi @Raju Ram Bagra ,

According to my research and testing, you can try to use the following code to bulk update custom user profile properties for SharePoint Online:

# Get needed information from the end user  
$adminUrl = Read-Host -Prompt 'Enter the admin URL of your tenant'  
$userName = Read-Host -Prompt 'Enter your user name'  
$pwd = Read-Host -Prompt 'Enter your password' -AsSecureString  
$importFileUrl = Read-Host -Prompt 'Enter the URL to the file located in your tenant'  
  
# Get instances to the Office 365 tenant using CSOM  
$uri = New-Object System.Uri -ArgumentList $adminUrl  
$context = New-Object Microsoft.SharePoint.Client.ClientContext($uri)  
  
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $pwd)  
$o365 = New-Object Microsoft.Online.SharePoint.TenantManagement.Office365Tenant($context)  
$context.Load($o365)  
  
# Type of user identifier ["Email", "CloudId", "PrincipalName"] in the user profile service  
$userIdType=[Microsoft.Online.SharePoint.TenantManagement.ImportProfilePropertiesUserIdType]::Email  
  
# Name of user identifier property in the JSON  
$userLookupKey="idName"  
  
# Create property mapping between the source file property name and the Office 365 property name  
# Notice that we have here 2 custom properties in UPA called 'City' and 'OfficeCode'  
$propertyMap = New-Object -type 'System.Collections.Generic.Dictionary[String,String]'  
$propertyMap.Add("City", "City")  
$propertyMap.Add("Office", "OfficeCode")  
  
# Call to queue UPA property import  
$workItemId = $o365.QueueImportProfileProperties($userIdType, $userLookupKey, $propertyMap, $importFileUrl);  
  
# Execute the CSOM command for queuing the import job  
$context.ExecuteQuery();  
  
# Output the unique identifier of the job  
Write-Host "Import job created with the following identifier:" $workItemId.Value  

More information for reference: Bulk update custom user profile properties for SharePoint Online

Hope it can help you. Thanks for your understanding.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

I appreciate you sharing this. For my project Exotic Knife categories, this solution is very helpful.