Unleashing SharePoint List Data Extraction with PowerShell

Extracting SharePoint list data to CSV via PowerShell is genuinely simple for a small list — the part that trips people up is the connection method that’s since been removed, and the list-view threshold that silently breaks the simple version of this script once a list crosses 5,000 items. This post covers the current, working version of both.

In this post: Connecting, the current way · Extracting list data · Large lists: PageSize and the 5,000-item threshold · Selecting only the fields you actually need · Person, Lookup, and multi-choice fields in CSV · Automating on a schedule · Related reading

Connecting, the current way

Worth a direct correction, since a script built around it will now fail outright rather than just warn: `-UseWebLogin` has been removed from `Connect-PnPOnline` entirely, not just deprecated. It relied on cookie-hijacking to authenticate, couldn’t make Graph calls behind the scenes, and was removed for being genuinely insecure by current standards — not swapped out for stylistic reasons. The current, supported way to connect interactively is `-Interactive`:

Connect-PnPOnline -Url "https://yourdomain.sharepoint.com/sites/yoursite" -Interactive

For anything unattended — a scheduled extraction rather than a one-off run — certificate-based app-only authentication is the realistic path rather than an interactive sign-in prompt that has nobody there to click through it.

Extracting list data

For a small list, this is genuinely the whole thing:

$ListName = "Your List Name"
$Items = Get-PnPListItem -List $ListName
$Items | Select-Object -ExpandProperty FieldValues | Select-Object Title, Field2, Field3 |
    Export-Csv -Path "C:\Path\To\Exported\File.csv" -NoTypeInformation

Worth flagging directly: piping `$Items` straight into `Select-Object Field1, Field2` without `-ExpandProperty FieldValues` first is a common mistake — `Get-PnPListItem` returns list item objects whose actual column data lives nested inside a `FieldValues` property, not as top-level properties on the object itself. Selecting a field name directly off the raw item object returns blank columns in the CSV, not an error, which makes it an easy thing to miss until someone opens the export and finds it empty.

Get-PnPListItem without -PageSize hits the same 5,000-item list view threshold as any other unindexed query — past that point the simple version of this script fails outright, not just slowly.

Large lists: PageSize and the 5,000-item threshold

The simple version above works fine until the list crosses 5,000 items, at which point it hits the same list view threshold that governs any other unindexed SharePoint query — a hard, unchangeable limit in SharePoint Online. The fix is `-PageSize`, which batches the request into smaller chunks the threshold doesn’t apply to the same way:

$Items = Get-PnPListItem -List $ListName -PageSize 500

A genuinely useful nuance worth knowing before it causes confusion: `-PageSize` and `-Query` (a CAML filter) don’t combine — using PageSize means retrieving everything in batches first, then filtering the results in PowerShell afterward, not filtering at the query level the way a CAML query would. For a list large enough to need PageSize in the first place, that’s usually the right tradeoff anyway, but it’s a real constraint worth planning around rather than discovering when a filtered query silently ignores the filter.

Selecting only the fields you actually need

`Get-PnPListItem` supports a `-Fields` parameter that limits which columns actually get retrieved, rather than pulling every field on every item and discarding most of it in the `Select-Object` step afterward:

$Items = Get-PnPListItem -List $ListName -PageSize 500 -Fields "Title", "Field2", "Field3"

On a small list this makes no noticeable difference. On a large one being extracted regularly, requesting only the columns actually needed is a real, meaningful reduction in how much data crosses the wire per run — worth adopting as the default habit rather than something to optimize only after a script is already slow.

Person, Lookup, and multi-choice fields in CSV

Text and number columns export cleanly with the pattern above; Person, Lookup, and multi-select Choice columns don’t, because CSV has no native way to represent a nested object or an array in a single cell. A Person field’s `FieldValues` entry is an object with its own `Email`, `LookupValue`, and `LookupId` properties, not a plain string — selecting it directly into a CSV column produces a useless type-name string like `Microsoft.SharePoint.Client.FieldUserValue` instead of the person’s name. The fix is pulling the specific sub-property explicitly rather than the field as a whole:

$Items | Select-Object -ExpandProperty FieldValues | Select-Object `
    Title,
    @{Label="AssignedTo"; Expression={$_.AssignedTo.Email}},
    @{Label="Categories"; Expression={$_.Categories -join "; "}} |
    Export-Csv -Path $ExportPath -NoTypeInformation

A multi-select Choice or multi-value Lookup field returns an array in `FieldValues` — joining it into a single delimited string (semicolon works well since commas already mean something in CSV) is the practical way to keep it in one column rather than the export silently truncating to just the first value, which is what happens with a plain `Select-Object` on an array property.

Automating on a schedule

Putting the full pattern together for something that runs unattended — Task Scheduler, an Azure Automation runbook, or similar:

$ListName = "Your List Name"
$ExportPath = "C:\Path\To\Exported\File.csv"

Connect-PnPOnline -Url "https://yourdomain.sharepoint.com/sites/yoursite" -Thumbprint $CertThumbprint -Tenant "yourtenant.onmicrosoft.com" -ClientId $ClientId

$Items = Get-PnPListItem -List $ListName -PageSize 500 -Fields "Title", "Field2", "Field3"
$Items | Select-Object -ExpandProperty FieldValues | Select-Object Title, Field2, Field3 |
    Export-Csv -Path $ExportPath -NoTypeInformation

Disconnect-PnPOnline

The certificate-based connection replaces the interactive sign-in from the connecting section above — unattended automation can’t stop and wait for someone to click through a login prompt, so this is the piece that actually makes a scheduled run possible rather than just theoretically automatable.

Navigating the Maze: Common Issues with SharePoint — the list view threshold and other symptoms it causes elsewhere, not just in extraction scripts.

The core extraction pattern is genuinely simple — the two things worth getting right up front are the current auth method and planning for the 5,000-item threshold before a list grows into it, rather than after a working script suddenly starts failing. The `FieldValues` nesting and the Person/Lookup/multi-choice handling above are the two most common reasons a first attempt at this produces a CSV that runs without errors but is quietly wrong — blank columns or type-name strings instead of actual data — which is a worse outcome than an error, since nothing flags it until someone actually opens the file.