CSOM PowerShell update SharePoint list from Excel

As you probably know I’m not a great fan of MS SharePoint, I consider it to be a design disaster, where most sound design principles have been sacrificed on the altar of stupid simplification. The dreaded 5000 limit, the silent 2000 limit and the bizarre field naming conventions just to name a few. SP is good for (temporary) simple web sites with small amounts of data, integrating with MS Office suit FULL STOP, PERIOD, END.
For some weeks (actually a bit more) I have had this +1000 line Excel sheet on my desk of necessary changes in a +5000 SP list. The excel sheet did not contain row ID. No one at hand could help me how to apply these changes with standard SP scripting or Nintex workflows. And I sure do not know how to do this with SP or Nintex scripting. My choices were C# or Powershell script using the CSOM library, our SP service provider do not allow me to run SOM scripts on their infrastructure plus the very idea of running transactions on the SP server is daft if you ask me. I decided to go for Powershell scripting just for the hell of it. I do not know Powershell .NET or SP well and I have no documentation of it so I have to rely on Google normally my favourite source of developer visdom. Unfortunately Google is a bit complicated with SP, I connect to SP via CSOM and I do not find that many working CSOM examples on Google, moreover many of the CSOM examples are in fact SOM, it seems many do not recognize there is a difference, I can sympathize with that, it should not be different libraries for connecting SP depending on from where you are calling SP. Microsoft have a great pedagogical task to do, promote CSOM and teach the SP community to use CSOM and depricate SOM a.s.a.p. Two SP libraries are one to many. Anyway after almost a day of Googling, picking bits and pieces from posts, extrapolating and trial and error I had a working script, I had to save my Excel sheet as a CSV file. It took some 40 minutes to run which is ridiculous, but it’s SharePoint so you have to live with it I suppose. Anyway the PowerShell script is quite elegant and it did the job. I like PowerShell scripting, if I only can get my head around the .NET object model I can probably do many things fast and efficient in the Windows environment.

$x = @"
This script updates items in a SharePoint list from a csv file
The csv file does not contain the ID of list items, matching is done on 'key' fields.
1. read the csv file into an iterator
2. iterate through the SharePoint list
3. for each list item, iterate through the csv file
4. if cvs row match update the ShartePoint list with values from the csv row

Note! This type of matching is inefficient, but quite ok for SharePoint lists as there few items in lists
and SharePoint per se is easily 'saturated', this matching gives SharePoint pauses to recover.   

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  

$UserId = "USERID"
$Password    = ConvertTo-SecureString 'PASSWORD' -AsPlainText -Force
$siteURL = 'https://thehub.group.atlascopco.com/metadata/group_codes'
$listItemstname = "RG_PGC_Codes" # SP list
$readItems = 5000                # Items to read from SharePoint list

# csv column names
$csvRG = "Rate Group Code"       #key
$csvPGC = "Product Group Code"   #key
$csvGAC = "GAC"                  #Key
$csvUNIT = "Unit"                #key
$csvVALIDTO = "ValidTo"          #value
# csv file
$delimeter = ";"
$filePath = "C:\rgupdate.csv"
$csvData = Import-Csv $filePath -Delimiter $delimeter # Load the CSV file containing the updates
$credentials = New-Object System.Management.Automation.PSCredential $Username, $Password
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
$ctx.credentials = $credentials  
try {  
   $list = $ctx.web.Lists.GetByTitle($listItemstname)  
   $listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery($readItems))  
       foreach($li in $listItems) {
           if ($li["ValidTo"] -eq $null){    # Filter; only null 'ValidTo' are interesting
               foreach ($line in $csvData){
                   if (($li["Rate_x0020_Group_x0020_Code"].LookupValue -eq $line.$csvRG) -and ($li["Product_x0020_Group_x0020_Code0"] -eq $line.$csvPGC) ){
                        Write-Host "Match"
                        $ID = $li["ID"]
                        write-host $line.$csvUNIT,$line.$csvGAC,$line.$csvPGC,$line.$csvRG,$line.$csvVALIDTO, $ID
Write-Host "ID -" $li["ID"] ", RG -" $li["Rate_x0020_Group_x0020_Code"].LookupValue ", PGC -" $li["Product_x0020_Group_x0020_Code0"] ", GAC -" $li["GAC0"] "Unit -" $li["Unit"].LookupValue " ,validTo -" $li["ValidTo"]
                        $li["ValidTo"] = $line.$csvVALIDTO
# 'Break' will stop update an item after first match, 'Return' stop processing after first match (for testing purposes)
                       # Return
                   }                        # endif Match
               }                            # end foreach csv row
   }                                        # end foreach SP item
} catch {  
   write-host "$($_.Exception.Message)" -foregroundcolor red  

No comments:

Post a Comment