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.