kwizcom banner advertisement

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.
Import from CSV to SharePoint List with People Picker Field Values Import from CSV to SharePoint List with People Picker Field Values Reviewed by Salaudeen Rajack on 8:46 PM Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.