Sync SharePoint List Data from a CSV File using PowerShell
Requirement: We have a SharePoint list with several fields to capture organization-wide people Expertise. This list has a particular field called “Team” and is to be updated from a CSV file of 100+ rows. Yes, the Datasheet view can help, but the problem is: The Team field value to be updated on items matching the “AccountName” field of the SharePoint list and CSV file.
Solution:
Let’s write a PowerShell script to select the matching row from the CSV file to the SharePoint list and then update it.
PowerShell Script to sync SharePoint List data from CSV File:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Read the CSV file
$CSVData = Import-CSV -path "C:\Expertdata.csv"
#Get the Web
$web = Get-SPWeb -identity "https://portal.crescent.com"
#Get the Target List
$list = $web.Lists["ExpertProfiles"]
$TeamsLookupList = $web.Lists["Teams"]
#Iterate through each Row in the CSV
foreach ($row in $CSVData)
{
#Get the matching Row from Experts List in SharePoint
$item = $list.Items | Where-Object {$_["AccountName"] -eq $row.AccountName}
if($item -ne $null)
{
Write-Host "Found Matching User in the Experts List:"$row.AccountName
#If the CSV data has valid Team data
if( ( $row.Team -ne $null) -and ( $row.Team -ne [string]::Empty) )
{
#Get Team Field Value (Lookup column) from the Expert Item
$ExpertTeam = New-Object Microsoft.SharePoint.SPFieldLookupValue($Item["Team"])
#If the current Team Value is empty or Null
if ( ($ExpertTeam.LookupValue -eq $null) -or ( $ExpertTeam.LookupValue -eq [string]::Empty))
{
#Get the Team from Parent Lookup List
$LookupItem = $TeamsLookupList.Items | where {$_["Team"] -eq $row.Team} | Select-Object -First 1
$item["Team"] = $LookupItem.ID
$item.Update()
Write-Host "Updated Team in:"$row.AccountName -ForegroundColor Green
}
}
}
}
I’ve added additional validations, such as: Checking if the current Team field value is empty as per the business requirements.
