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!
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:
Why is line 29 commented out?
#$item.Update()
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!
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?
Use this Script to Update Hyperlink field: How to Update Hyperlink Field Value in SharePoint Online using PowerShell
Hello Is this script has to be run on the Sharepint server or its can be run on the client?
This script is written for SharePoint On-Premises. So, You must run it from the SharePoint server.
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
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
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!
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
Hello,
Do you have a similar script for SharePoint online? getting lots of failures with this one as i assume its on prem only?
Yes. This script is written for On-Prem. For SharePoint Online, use: SharePoint Online: Import CSV File to SharePoint List using PowerShell