Monday, January 5, 2015

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.

Sync SharePoint List Data from a CSV File using PowerShell
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.



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...