So I had a situation the other day where I needed to get a SharePoint list exported as a CSV file on a regular occurance.
Some background, I was working through a process identifying critical information in SharePoint we would like on hand in the event all servers were down (i.e. a brown pants moment). While we have this pretty much sorted for various word and excel documents, there were a number of SharePoint lists that would also be invaluable, and I was loathe to turn these into a static excel spreadsheet instead.
Our current process is a manual one where the list is opened in Excel then saved as a CSV. As this requires human effort, humans get complacent, and doing repetative things over time get done poorly, shortcuts taken, shiny things to distract you, etc.. etc, and isn’t that one of the reasons we use computers?? Where was I going with this again? Ohhh look a Blue Car!
Right back on track… So I performed some research, got distracted, got a coffee, then got back to it. A number of solutions exist which rely on the SharePoint powershell cmdlets that are installed on the SharePoint server, but do not come stand alone for a client. So that discounted them, also in our environment we are are not using PSRemoting. I’m also not a fan of running scheduled scripts on our SharePoint server unless absolutely necessary.
Then I stumbled across the SharePoint Client Side Object Module. Here I could manipulate SharePoint directly from my PC utilizing API calls on HTTPS. It sounded like just the ticket!
I dutily downloaded them: https://www.microsoft.com/en-nz/download/details.aspx?id=35585
Got a little confused reading the basic operations MSDN stuff https://msdn.microsoft.com/en-us/library/office/fp179912.aspx. This was in C# but translates fairly easily to Powershell with a bit of force.
Finally, after a bit of cursing, caffine, and luck I got a result! I wrapped it up in a function, then called it to my hearts content. Essentially it takes any SharePoint list and returns it as a table that I can then export to CSV.
Here is the Powershell:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
function Get-SharepointList($SiteUrl, $ListName) { #Load some SharePoint CSOM Assemblies $CSOM = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") $Runtime = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") #Create the Web ClientContext on specifed Site URL $clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) #Execute the Query (when you run this Method it connects to Sharepoint and runs the commands queued up) $clientContext.ExecuteQuery() #Assign Web to SharePoint Web Site $web = $clientContext.Web #Assign list to Specific List $list = $web.Lists.GetByTitle($ListName) #Get all Field Definitions $fieldDefinitions = $list.Fields #Get all Items in SharePoint List $allItems = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery() $items = $list.GetItems($allItems) #Load objects into the Client Context $clientContext.Load($fieldDefinitions) $clientContext.Load($items) #Execute Query (essentially returns data to $fieldDefinitions and $items) $clientContext.ExecuteQuery() #Get all field Title's (what you see in Sharepoint) and the corresponding internalName #I've only selected Non Standard fields, modify if you want ALL fields (remove WHERE) $fields = $fieldDefinitions | Where-object {$_.FromBaseType -eq $false -or $_.InternalName -eq "Title"} | Select Title, InternalName #loop through all list items foreach ($item in $items) { #Create a temporary PS Object to store all properties in item $obj = New-Object psobject -Property $props foreach ($field in $fields) { #Internal Name required when retrieving item value $value = $item[$field.InternalName] if ($value){ #Trim value and remove HTML tags (makes CSV cleaner, optional of course) $value = $value.ToString().Trim() -replace '<[^>]+>','' } #Add Member using the value and Friendly Field Name $obj | Add-Member -MemberType NoteProperty -Name $field.Title -Value $value } #Return object $obj } } #Example Get-SharepointList -SiteUrl "https://sharepoint.cloudybutfine.com/docs/testSite" -ListName "Beer Hiding Locations" |
Hopefully someone finds that useful, enjoy 😀