Wednesday, March 9, 2016

SharePoint Online: Export List Items to CSV using PowerShell

Requirement: 
Export SharePoint online list items to CSV file from client side.

PowerShell script to Export SharePoint List Items to CSV from Client Side (CSOM):

#Load SharePoint CSOM Assemblies
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"
 
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/poc/"
$ListName="Employee"
$ExportFile ="c:\Scripts\ListRpt.csv"
$UserName="Salaudeen@crescent.com"
$Password ="Password goes here"

#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))

#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials
 
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)

#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$context.Load($ListItems)
$context.ExecuteQuery()

#Array to Hold List Items 
$ListItemCollection = @() 

#Fetch each list item value to export to excel
 $ListItems |  foreach {
    $ExportItem = New-Object PSObject 
    $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"]
  
    #Add the object with above properties to the Array
    $ListItemCollection += $ExportItem
 }
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $ExportFile -NoTypeInformation 

Write-host "List data Exported to CSV file successfully!"

Related Posts:



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:

3 comments :

  1. Looks good thankyou

    ReplyDelete
  2. I am getting the below error: with sdk version 15 and 16.
    Exception calling "ExecuteQuery" with "0" argument(s): "Identity Client Runtime
    Library (IDCRL) could not look up the realm information for a federated
    sign-in."

    On checking the variable $List it says

    The collection has not been initialized. It has not been
    requested or the request has not been executed. It may need to be explicitly
    requested.
    please advice me how to resolve this.
    thank you.

    ReplyDelete
    Replies
    1. Double check the credentials you've supplied. Try reinstalling SharePoint Online SDK https://www.microsoft.com/en-us/download/details.aspx?id=42038 or SPO Management Shell from here: https://www.microsoft.com/en-us/download/details.aspx?id=35588

      Delete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...