Update SharePoint List Items from CSV File using PowerShell

Requirement: Update SharePoint List Items from CSV file.

A bit background: We have 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 the Department field for a 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 a 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 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:

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

12 thoughts on “Update SharePoint List Items from CSV File using PowerShell

  • April 27, 2021 at 1:54 PM

    Why is line 29 commented out?

    #$item.Update()

    Reply
  • April 20, 2021 at 3:36 PM

    I’m also looking for a script to update a SharePoint Online list. The link you’re giving is to import and not update so that’s not really a suitable replacement for this use case. Would you happen to have an update script written (like the one above), but for SharePoint Online? Preferably using pnp.PowerShell? Much thanks!

    Reply
  • November 6, 2020 at 6:03 PM

    Thanks mate, it works fine. I am currently working on updating the description attribute of the type “Hyperlink or picture” column. can you please advise on how to achieve it. the script should read a document name and change the description of the URL.

    please advise?

    Reply
  • December 13, 2019 at 7:22 PM

    Hello Is this script has to be run on the Sharepint server or its can be run on the client?

    Reply
    • December 27, 2019 at 8:21 PM

      This script is written for SharePoint On-Premises. So, You must run it from the SharePoint server.

      Reply
  • March 26, 2019 at 10:50 PM

    Hi Saludeen,

    A quick question, I have a requirement to update fields in a list for multiple sites within a site collection. Can you please help me with the info how achieve?

    Thanks,
    Uttkarsh

    Reply
  • March 4, 2019 at 5:52 AM

    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

    Reply
    • March 4, 2019 at 10:48 PM

      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

      Reply
  • December 3, 2018 at 9:32 AM

    Hello,

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

    Reply

Leave a Reply