Monday, October 3, 2016

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!

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
    }
}
Related Posts:



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...