Sync SharePoint List Data from a CSV File using PowerShell
Requirement: We've a SharePoint list with number of 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, Datasheet view can help, but the problem is: The Team field value to be updated on items matching "AccountName" field of the SharePoint list and CSV file.
Solution:
Lets write a PowerShell script to select the matching row from the CSV file to SharePoint list and then update it.
PowerShell Script to sync SharePoint List data from CSV File:
Solution:
Lets write a PowerShell script to select the matching row from the CSV file to 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 "http://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 "") ) { #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 "")) { #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.
No comments:
Please Login and comment to get your questions answered!