Export SharePoint Online List Items To CSV Using PnP PowerShell

Vijai Anand Ramalingam

4 Comments

Join the conversation! Your thoughts help the community grow.

Manoj tambe5y ago
How to export multiple list in single excel using PnP Powershell?
Swapna Nethi6y ago
My list has both Lookup fields and other fields. how do i change the script to make it work for lookup values as well? currently it is displayed as below : Microsoft.SharePoint.Client.FieldUserValue
- John Hohengarten5y ago
  I was able to get Microsoft.SharePoint.Client.FieldUserValue to return using the below function (written by Justin W Grote, provided via the PowerShell Discord):# provided by @justinwgrote, PowerShell Discord function ExpandSharepointLookupValue ($Value) { if ($Value -is [Microsoft.SharePoint.Client.FieldLookupValue]) { $Value[0].LookupValue } else { $Value } } I then adjusted the Add-Member call in the loop to this: $obj | Add-Member -MemberType NoteProperty -Name $_.Key -Value (ExpandSharepointLookupValue($_.Value))
zaidi s6y ago
I am very new to windows power shell and have a requirement to export sharepoint list to a csv. I am using the script as is, but what its doing is, the command Connect-PnPOnline -Url $url -UseWebLogin is launching my share point site in a window and just stays there, its not getting to call the function ExportList. if I close the window it just errors out with message error: No connection, please connect first with Connect-PnPOnline. please help

Introduction

In this blog, you will learn how to export SharePoint Online list items to a CSV using PnP PowerShell.

Prerequisites

Ensure SharePoint PnP PowerShell Online cmdlets are installed. Click here for more details on how to install.

Steps Involved

Open Notepad.

Copy the below code and save the file as ExportList.ps1.

###### Declare and Initialize Variables ######
$url="https://c986.sharepoint.com/sites/vijai"
$listName="Test List"
$currentTime= $(get-date).ToString("yyyyMMddHHmmss")
$logFilePath=".\log-"+$currentTime+".docx"
# Fields that has to be retrieved
$Global:selectProperties=@("Title","Comments","Status");
## Start the Transcript
Start-Transcript -Path $logFilePath
## Export List to CSV ##
function ExportList
{
try
{
# Get all list items using PnP cmdlet
$listItems=(Get-PnPListItem -List $listName -Fields $Global:selectProperties).FieldValues
$outputFilePath=".\results-"+$currentTime+".csv"
$hashTable=@()
# Loop through the list items
foreach($listItem in $listItems)
{
$obj=New-Object PSObject
$listItem.GetEnumerator() | Where-Object { $_.Key -in $Global:selectProperties }| ForEach-Object{ $obj | Add-Member Noteproperty $_.Key $_.Value}
$hashTable+=$obj;
$obj=$null;
}
$hashtable | export-csv $outputFilePath -NoTypeInformation
}
catch [Exception]
{
$ErrorMessage = $_.Exception.Message
Write-Host "Error: $ErrorMessage" -ForegroundColor Red
}
}
## Connect to SharePoint Online site
Connect-PnPOnline -Url $url -UseWebLogin
## Call the Function
ExportList
## Disconnect the context
Disconnect-PnPOnline
## Stop Transcript
Stop-Transcript

Open Windows PowerShell and navigate to the location where the file is placed.

Run the following command.

CSV file is generated with all the required details.

Reference

https://docs.microsoft.com/en-us/powershell/module/sharepoint-pnp/get-pnplistitem?view=sharepoint-ps

Thus, in this blog, you saw how to export SharePoint Online list items to a CSV using PnP PowerShell.