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  


In memory of Hans Rosling

Bill Gates writes it much better than I can do https://www.gatesnotes.com/About-Bill-Gates/Remembering-Hans-Rosling

"Hans Rosling turned global health and poverty data into graphically engaging, focused, insightful, compelling, even exciting stories" (Mike Urbonas)
Hans took Business Intelligence to a global scale. He invented a new type of graphics I had not seen before the GapMinder graphics, with moving bubbles he showed us the world as we didn't know it. Hans was not old only 68, but as as Keith Richards once said "The good guys go first".


Auf Wiedersehen

2012-03-24 I wrote my first post in this blog. I started to write due to inspiration of my colleague Petr Hutar, in one of the first meetings with him he said, “I have lot’s of other interests beside computers, I just forgot what they are”. At that time Petr put words on who I felt I was, so I stole with pride and made the quote mine.
Now Petr is leaving the company to pursue a somewhat different IT career going from administrative IT systems and IT architecture to robotics and factory automation. As Petr is an extremely creative person with a technical degree in robotics, I’m sure we will see great things from Petr in future.
You may call Petr an IT-nerd, but more than that he is a well respected eloquent gentleman that can see and present the big picture, not only the miniscule details of program algorithms. My best wishes Petr, I hope we have a beer together in the not too distant future.


Happy New Year

Last task this work year. Upload currency rates for year end closing.
View from the office 08:20 in the morning. If you look closely to the right you can see reflections of me drinking coffee.

The dollar was sinking which worried the financial guys. I suspect it is the recent quibble between US and Russia that makes the market a bit apprehensive.
All the same - Happy New Year to all of you.


SharePoint 5000 limit trick

Another trick to circumvent the dreaded 5000 items limit in Sharepoint.

I found this trick how to create a very complex view filter (combining two or even more boolean expressions in the view filter):
If you use two or more columns in the filter expression, the determining index or indexes should use an AND operator. For example, if you want to return Dogs from a large list of animals. You have an unindexed column called Species where you have Dog as a value. If you just query for Species = Dog, your query will be throttled. However, if you have an indexed column called Class, your query becomes Class = Mammals AND Species = Dog. You could also search for cats and dogs with the query Class = Mammals AND (Species = DOG OR Species = Cats). The second query selects all Mammals, and then filters to Dogs and Cats.”

This is an example of a view filter definition:

I have not figured out where I type or 'click in' the parentheses, but once I done that, I have great hope this Microsoft trick may work.
If you know how you create this 'dog or cat' filter in sharePoint please drop me a mail.

After rereading the post my eyes fell on the impossible filter query "Class = Mammals AND (Species = DOG OR Species = Cats)", the latter paranterized part filtering on species where DOG is upper case singular, while the feline Cats is plural lowercase with a leading uppercase 'C'. And missing quotes suround Mammals, DOG & Cats.
Do anyone beleive the author have tested this or even have had a SharePoint list to test the filter on?


Cash free

Andreas sent me this link https://youtu.be/NrmMk1Myrxc really cool. It reminded me I am cash free, it is probably about two year or so since I used cash in Sweden. I use a credit card and Swish for money transfer and the Inet bank for bills. Very convenient. I’m looking forward to the day when we can do away with the cashier counter, this post is unfortunately in swedish only but says a lot about at least my frustration of standing in line waiting to pay.     
Last time I used cash was probably in Antwerp buying bollekes and frietjes. Best beer, best french fries in the world.


Blue & Lonesome

A new Stones album doesn’t come often these days and this new one is a cover album, I expected a lot, these guys have been around for a while and they did some stunning covers some 50 years ago.  After listen to the songs once I think the album is a bit "pale", the only really good track was Howlin’ Wolf’s “Just like I treat you”.  “Just  your fool” and “I can’t quit you baby” are alright, the rest of the songs I don’t remember.
In school I once used the word “quit” in an english essay, my english teacher told me there is no such word but I had heard “I can’t quit you baby” so I knew. However my teacher was not impressed by my reference to a rhythm and blues song. I was as usually given a bad mark in english, "quit" probably didn't matter much. I didn’t write better english then and I wasn’t good teacher’s pet material either, so I expected a bad english mark.