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
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.
No comments:
Please Login and comment to get your questions answered!