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:

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.