kwizcom banner advertisement

SharePoint Online: Export List Items to CSV using PowerShell

Requirement:  Export SharePoint online list items to CSV file from client side.
SharePoint Online Export List Items to CSV using PowerShell

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!"

SharePoint online PowerShell to Export List to CSV:
While the above script exports selected columns to Excel file, lets alter it a bit to export all columns data to Excel.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/"
$ListName= "Projects"
$ExportFile ="c:\ListItems.csv"

#Get Credentials to connect
$Cred = Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = $Credentials

#Get the List
$List = $Ctx.web.Lists.GetByTitle($ListName)
 
#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$FieldColl = $List.Fields
$Ctx.Load($ListItems)
$Ctx.Load($FieldColl)
$Ctx.ExecuteQuery()
 
#Array to Hold List Items 
$ListItemCollection = @() 
 
#Fetch each list item value to export to excel
 Foreach($Item in $ListItems)
 {
    $ExportItem = New-Object PSObject 
    Foreach($Field in $FieldColl)
    {
            if($NULL -ne $Item[$Field.InternalName])
            {
                #Expand the value of Person or Lookup fields
                $FieldType = $Item[$Field.InternalName].GetType().name
                if (($FieldType -eq "FieldLookupValue") -or ($FieldType -eq "FieldUserValue"))
                {
                    $FieldValue = $Item[$Field.InternalName].LookupValue
                }
                else
                {
                    $FieldValue = $Item[$Field.InternalName]   
                }
            }
            $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $FieldValue
    }
    #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!"
and the output:
sharepoint online powershell export list to csv

Related Posts:
SharePoint Online: Export List Items to CSV using PowerShell SharePoint Online: Export List Items to CSV using PowerShell Reviewed by Salaudeen Rajack on March 09, 2016 Rating: 5

6 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
  3. Hello,

    How to get the multi value lookup column value for all the documents in library using powershell in sharepoint online and export to CSV.

    Thanks in Advance.

    ReplyDelete
    Replies
    1. Please help to modify the above script to include multi lookup value and choice column value in CSV file.

      Delete

Please Login and comment to get your questions answered!

Powered by Blogger.