Update SharePoint List Items from CSV File using PowerShell

Requirement: Update SharePoint List Items from a CSV file.

A bit of background: We have a list with 1000s 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 Quick edit (or datasheet view) can be used for bulk editing, filtering and editing 100’s of rows would be time-consuming. So, the solution is to have a list of user names and their departments in CSV file format and 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:

Are you looking for a way to update SharePoint list items from a CSV file? PowerShell can just do that! This guide will show you how to use PowerShell to update SharePoint list items from a CSV file.

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 "https://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, let’s 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 "https://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

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

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

  • Why is line 29 commented out?

    #$item.Update()

    Reply
  • 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
  • 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
  • Hello Is this script has to be run on the Sharepint server or its can be run on the client?

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

      Reply
  • 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
  • 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 “https://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
    • 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]=’https://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
  • 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

Your email address will not be published.