How can I export a SharePoint List to Excel using PnP Powershell. I have spend hours googling it. But no luck. I found this code below but i think it isn't PnP Powershell code.

Export-SPWeb -Identity http://content.contoso.cloud -ItemUrl /lists/testlist -Path "c:\export\export1.cmp"

Does someone knows the exact code?

P

up vote 0 down vote accepted

PnP PowerShell not provide function to export list item to Excel directly. You could use the code below to achieve, using Export-CSV command:

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://xxx.sharepoint.com/sites/xxx/"
$ListName="xxx"
$ExportFile ="c:\Scripts\ListRpt.csv"
$UserName="xxx@xxx.onmicrosoft.com"
$Password ="xxx"

#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"]

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

Reference:

SharePoint Online: Export List Items to CSV using PowerShell

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.