kwizcom banner advertisement

Update SharePoint List Items from CSV File using PowerShell

Requirement: Update SharePoint List Items from CSV file.

Bit background: We've a list with 1000's of rows of organization wide experts. Later we added a new field for "Department". Now the requirement is to update Department field for specific list of users. While the datasheet view can be used for bulk editing, filtering and editing 100's of rows would be time consuming. So the solution is: Have list of user names and their departments in CSV file format, give it to PowerShell!
Update SharePoint List Items from CSV File using PowerShell

PowerShell Script to Read from CSV file and Update SharePoint List Items:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Read the CSV file
$CSVData = Import-CSV -path "C:\UserData.csv" 
 
#Get the Web
$web = Get-SPWeb -identity "http://portal.crescent.com"
 
#Get the Target List
$List = $web.Lists["ExpertProfiles"]
 
#Iterate through each Row in the CSV
foreach ($Row in $CSVData) 
{
    #Get the List Item matching "Name" field in the CSV    
    $Item = $List.Items | Where-Object { $_["Title"] -eq $Row.Name }

    if($item -ne $null)
    {
 #Update List Item - Internal Name!
        $item["Department"] = $row.Department
        $item.Update()
        Write-Host "Updated:"$row.Name -ForegroundColor Green
    }
    else
    {
        write-host "No matching Item Found for:"$row.Name -f Red
    }
}
While the above code serves the purpose, Lets optimize it with CAML-SPQuery

PowerShell to Update SharePoint List from CSV:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Read the CSV file
$CSVData = Import-CSV -path "C:\UserData.csv" 
 
#Get the Web
$web = Get-SPWeb -identity "http://portal.crescent.com"
 
#Get the Target List
$List = $web.Lists["ExpertProfiles"]
 
#Iterate through each Row in the CSV
foreach ($Row in $CSVData) 
{
    #Filter using CAML Query
    $CAMLQuery="<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>$($Row.Name)</Value></Eq></Where>"
    $SPQuery=New-Object Microsoft.SharePoint.SPQuery
    $SPQuery.ViewAttributes = "Scope='Recursive'"  #Get all items including Items in Sub-Folders!
    $SPQuery.Query=$CAMLQuery
    $SPQuery.RowLimit = 1 
     
    #Get the List item based on Filter 
    $Item=$List.GetItems($SPQuery)[0]

    If($Item -ne $null)
    {
        #Update List Item
        $Item["Department"] = $Row.Department
        #$item.Update()
        Write-Host "Updated:"$row.Name -ForegroundColor Green
    }
    else
    {
        write-host "No matching Item Found for:"$row.Name -f Red
    }
}

Update People Picker Field Value from CSV file:
In an another case, I had to update people picker field value:
 #If the matching project found
    If($Item -ne $null)
    {
        #Update Team members Multi-People picker field
  $TeamMembersList = new-object Microsoft.SharePoint.SPFieldUserValueCollection
  $TeamMembers = $row."TeamMembers" -split ';'
  foreach ($TeamMember in $TeamMembers)
  {
   if ($TeamMember -ne $null) 
   {
    #Prepre the user to add
       $User = $Web.EnsureUser($TeamMember)
       $NewUser = new-object Microsoft.SharePoint.SPFieldUserValue($Web, $User.ID, $User.LoginName)
    $TeamMembersList.Add($NewUser)
   }
  }
  #Update Team members field
  $item["Team Members"] = $TeamMembersList
  $item.Update()
        Write-Host "Updated:"$Row.ProjectName -ForegroundColor Green
    }
    else
    {
        write-host "No matching Item Found for:"$row.ProjectName -f Red
    } 

Related Posts:
Update SharePoint List Items from CSV File using PowerShell Update SharePoint List Items from CSV File using PowerShell Reviewed by Salaudeen Rajack on October 03, 2016 Rating: 5

5 comments:

  1. Hello,

    Do you have a similar script for SharePoint online? getting lots of failures with this one as i assume its on prem only?

    ReplyDelete
  2. Hi Salaudeen,

    Thanks again for a wonderful post.

    I have a challenge in front of me. Where I need to update 50 SharePoint Sites which have Links list items using CSV.
    In my CSV I have 3 columns [ItemID], [LinkURL] and [Description].

    Using your script I'm successfully update the URL item. However this also updates the Description for the item which I do not want.

    Will you be able to help me with the script which can only update the URL value and Description value separately.


    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

    #Read the CSV file
    $CSVData = Import-CSV -path "C:\Import.csv"

    #Get the Web
    $web = Get-SPWeb -identity "http://organisationname/Site/NSW"

    #Get the Target List
    $List = $web.Lists["Site Links"]

    #Iterate through each Row in the CSV
    foreach ($Row in $CSVData)
    {

    #Get the List Item matching "Name" field in the CSV
    $Item = $List.Items | Where-Object { $_["ID"] -eq $Row.ItemID }

    if($item -ne $null)
    {
    #Update List Item - Internal Name!
    $item["URL"] = $row.LinkUrl
    $item["Description"] = $row.Description

    $item.Update()
    Write-Host "Updated:"$row.Name -ForegroundColor Green
    }
    else
    {
    write-host "No matching Item Found for:"$row.Name -f Red
    }
    }


    Many thanks in advance.

    Cheers,
    Uttkarsh

    ReplyDelete
    Replies
    1. Sure, Use this Script to Update Link URL: SharePoint Online: Update Hyperlink Field Value using PowerShell , Just remove the line " $item["Description"] = $row.Description" in your script!

      Delete
    2. Hi Salaudeen,

      As mentioned to you in my first reply, the problem I'm facing is when I update the URL field the script updates the Description column with the same value. e.g. in CSV [LinkUrl]='http://google.com' the script also includes the same value within [Description] which I do not wish to be updated. However, I would like [Description]='Google'.
      Hope this provide clarity to my issue.

      Many thanks again.

      Regards,
      Uttkarsh

      Delete

Please Login and comment to get your questions answered!

Powered by Blogger.