Exporting SharePoint List Data to CSV using PnP PowerShell

Exporting SharePoint list data to a CSV file is useful for reporting, analysis, and backup. With PnP PowerShell, you can automate this process efficiently.

What You’ll Learn:

✔️ How to connect to SharePoint Online
✔️ How to export list data to a CSV file
✔️ How to filter specific columns
✔️ How to export list items based on conditions
✔️ How to schedule automated exports

Prerequisites

Before proceeding, ensure that:
PnP PowerShell is installed
You have SharePoint Online permissions
You know the list name and desired columns

Step 1: Install and Import PnP PowerShell

If you haven’t installed PnP PowerShell, install it using:

Install-Module -Name PnP.PowerShell -Scope CurrentUser -AllowClobber -Force

Then, import the module:

Import-Module PnP.PowerShell

PnP PowerShell is ready!

Step 2: Connect to SharePoint Online

To connect to your SharePoint site, use:

# Connect to SharePoint Online
Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive

🔹 Replace "yourtenant" with your tenant name
🔹 Replace "yoursite" with the actual site name

Connected successfully!

Step 3: Export Full List Data to a CSV File

To export all list items from a SharePoint list:

# Define list name
$listName = "Project Tasks"

# Get all list items
$items = Get-PnPListItem -List $listName

# Export to CSV
$items | Select-Object FieldValues | ForEach-Object { $_.FieldValues } | Export-Csv -Path "C:\SharePointListData.csv" -NoTypeInformation

Write-Host "List data exported to C:\SharePointListData.csv"

🔹 This script retrieves all items and saves them in a CSV file.
List data exported!

Step 4: Export Specific Columns Only

If you need only specific columns (e.g., Title, Status, DueDate):

# Define variables
$listName = "Project Tasks"

# Get list items with selected columns
$items = Get-PnPListItem -List $listName | Select-Object @{Name="Title"; Expression={$_.FieldValues["Title"]}}, 
                                                      @{Name="Status"; Expression={$_.FieldValues["Status"]}}, 
                                                      @{Name="Due Date"; Expression={$_.FieldValues["DueDate"]}}

# Export to CSV
$items | Export-Csv -Path "C:\FilteredListData.csv" -NoTypeInformation

Write-Host "Filtered list data exported to C:\FilteredListData.csv"

Exported specific columns!

Step 5: Export List Items Based on Conditions

To export only tasks with “In Progress” status:

# Define variables
$listName = "Project Tasks"

# Get filtered list items
$items = Get-PnPListItem -List $listName | Where-Object { $_.FieldValues["Status"] -eq "In Progress" } | 
         Select-Object @{Name="Title"; Expression={$_.FieldValues["Title"]}}, 
                       @{Name="Status"; Expression={$_.FieldValues["Status"]}}, 
                       @{Name="Assigned To"; Expression={$_.FieldValues["AssignedTo"]}}

# Export to CSV
$items | Export-Csv -Path "C:\InProgressTasks.csv" -NoTypeInformation

Write-Host "Filtered data exported to C:\InProgressTasks.csv"

Exported only “In Progress” tasks!

Step 6: Automate Exporting on a Schedule

To automate exports, create a PowerShell script and schedule it using Task Scheduler:

1️⃣ Save the script as ExportListData.ps1
2️⃣ Open Task Scheduler and create a new task
3️⃣ In the “Actions” tab, select “Start a program”
4️⃣ Set Program/script to:

powershell.exe -File "C:\Scripts\ExportListData.ps1"

5️⃣ Set the trigger (e.g., daily at 8 AM)

Automated exports scheduled!

Common Errors & Solutions

Error	Cause	Solution
`Access Denied`	Insufficient permissions	Ensure you have Read or Edit rights
`Cannot find list`	Incorrect list name	Verify the list name using `Get-PnPList`
`Invalid CSV Path`	File path doesn’t exist	Ensure the directory is accessible