Import from CSV to SharePoint List with People Picker Field Values

Requirement is: To add bulk of records from a CSV file to SharePoint list with People Picker field of “Allow multiple selections” set to “Yes”.

Unfortunately, SharePoint datasheet view doesn’t allow us to copy-paste People picker values. So, I got to write the script!

Here is my data to be imported to SharePoint list from CSV file:

Import from CSV to SharePoint List with People Picker Field Values

PowerShell Script to import CSV data to SharePoint list with people picker field

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

#Functions for Get-SPSite & Get-Web in MOSS 2007
function global:Get-SPSite($url)
 {
    return new-Object Microsoft.SharePoint.SPSite($url)
 }


Function global:Get-SPWeb($url)
{
  $site= New-Object Microsoft.SharePoint.SPSite($url)
        if($site -ne $null)
            {
               $web=$site.OpenWeb();
       
            }
    return $web
}

$URL="https://sharepoint.crescent.com/sites/helpdesk/"
 
#Read the CSV file - Map the Columns to Named Header 
$CSVData = Import-CSV -path "D:\csaapprovalmatrix.csv" -Header("Region", "Country", "Company", "Entity", "Preparer", "Reviewers")

#Get the Web
$web = Get-SPWeb $URL
 
#Get the Target List
$list = $web.Lists["CSA Approval Matrix"]
 
#Iterate through each Row in the CSV
foreach ($row in $CSVData)
 {
       $item = $list.Items.Add()
        
       $item["CSA Region"] = $row.Region
       $item["CSA Country"] = $row.Country
       $item["CSA Company"] = $row.Company
       $item["CSA Entity"] = $row.Entity

    #Set "Preparer" People Picker Field value
 try
 {
    $ErrorActionPreference = "Stop"
    
       $Preparer=[Microsoft.Sharepoint.Utilities.SpUtility]::GetLoginNameFromEmail($Web.Site, $row.Preparer)
       [Microsoft.SharePoint.SPUser]$PreparerUser = $Web.EnsureUser($Preparer)       
    }
    catch
    {
     write-host "Could Not resolve Preparer: $($Row.Preparer)" 
        write-host $_.Exception.Message
        continue
    }
    finally
    {
     $ErrorActionPreference = "Continue"
      #continue; #Skip to Next Row from the CSV
    }
  
  $item["CSA Preparer"] =  $PreparerUser  
  
  #Set $Reviewers People picker field with Multiple values allowed
  $ReviewersList = new-object Microsoft.SharePoint.SPFieldUserValueCollection
  $Reviewers = $row.Reviewers -split ';'

  foreach ($Reviewer in $Reviewers)
    {
  if ($Reviewer -ne $null) 
   {
       #Get the Login Name "Domain\User" from Email
     $ReviewerAccount =[Microsoft.Sharepoint.Utilities.SpUtility]::GetLoginNameFromEmail($web.Site, $Reviewer)
                try
                {
                     $ErrorActionPreference = "Stop"
                      
               [Microsoft.SharePoint.SPUser]$ReviewerUser = $Web.EnsureUser($ReviewerAccount)   
               $ReviewerValue = new-object Microsoft.SharePoint.SPFieldUserValue($Web, $ReviewerUser.ID, $ReviewerUser.LoginName)
               $ReviewersList.Add($ReviewerValue)
                }
                catch
                {
                     write-host "Could Not resolve Reviewer: $($Reviewer) "
                     write-host $_.Exception.Message
                     continue
                }
                finally
                {
                     $ErrorActionPreference = "Continue"
                }
   }
 }
    write-host $ReviewersList
 #Skip to Next item if $ReviewersList is null
 if($ReviewersList -eq $null)
 {
  continue
 }
 
    $item["CSA Reviewers"] = $ReviewersList

    $item.update()
}

Another challenge was: CSV has email ids instead user names! So, I used SpUtility’s GetLoginNameFromEmail function to get the Login Name from given Email Id.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *