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:
Here is how you can export SharePoint online list to CSV via PowerShell

#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="[email protected]"
$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!"

Please note, this script gets all list items under a list, but doesn't recursively get items of folders and sub-folders of the list (If you have folders and sub-folders). To retrieve all list items recursively, just change the CAML query part as:

#Get all List items from the library Including Items in Sub-Folder
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='FSObjType'/><Value Type='Integer'>0</Value></Eq></Where></Query></View>"
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

SharePoint Online PowerShell to Export List to CSV:
While the above script exports selected columns to Excel file, let's 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:

Export SharePoint Online List Items to CSV using PowerShell
Here is the PnP PowerShell to export SharePoint online list items to CSV

#Config Parameter
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName = "Access Requests"
$CSVPath = "C:\Temp\ListData.csv"
$ListDataCollection= @()

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
$Counter = 0 
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000

#Get all items from list
$ListItems | ForEach-Object {
        $ListItem  = Get-PnPProperty -ClientObject $_ -Property FieldValuesAsText
        $ListRow = New-Object PSObject 
        $Counter++
        Get-PnPField -List $ListName | ForEach-Object {
            $ListRow | Add-Member -MemberType NoteProperty -name $_.InternalName -Value $ListItem[$_.InternalName]
            }
        Write-Progress -PercentComplete ($Counter / $($ListItems.Count)  * 100) -Activity "Exporting List Items..." -Status  "Exporting Item $Counter of $($ListItems.Count)"
        $ListDataCollection += $ListRow
}
#Export the result Array to CSV file
$ListDataCollection | Export-CSV $CSVPath -NoTypeInformation

Related Posts:

• Export SharePoint List Items to CSV using PowerShell 
• Export SharePoint List Items to XML using PowerShell