Exporting SharePoint List Data for Migration using PnP PowerShell

Migrating SharePoint lists between sites or tenants requires exporting list data efficiently. PnP PowerShell provides a streamlined way to export SharePoint list data, including metadata, attachments, and user fields, into a CSV or JSON format for easy migration.

This guide will cover:
Exporting SharePoint list data to CSV
Handling attachments and complex fields
Saving list data in JSON format for structured migration

---

Step 1: Install and Connect to SharePoint Online

Ensure PnP PowerShell is installed:

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

Connect to the SharePoint site:

$SiteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
Connect-PnPOnline -Url $SiteUrl -Interactive

Connected successfully!

---

Step 2: Export List Data to CSV

Exporting list items to CSV ensures easy migration and compatibility with most tools.

Save this script as C:\Migration\ExportListData.ps1

# Define Variables
$SiteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
$ListName = "ProjectTasks"
$ExportPath = "C:\Migration\ProjectTasks.csv"

# Connect to SharePoint
Connect-PnPOnline -Url $SiteUrl -UseWebLogin

# Retrieve List Items
$Items = Get-PnPListItem -List $ListName -PageSize 500 | Select-Object FieldValues

# Convert Data to Exportable Format
$ExportData = @()
foreach ($Item in $Items) {
    $ExportData += [PSCustomObject]@{
        ID = $Item["FieldValues"]["ID"]
        Title = $Item["FieldValues"]["Title"]
        Status = $Item["FieldValues"]["Status"]
        Created = $Item["FieldValues"]["Created"]
        Modified = $Item["FieldValues"]["Modified"]
    }
}

# Export to CSV
$ExportData | Export-Csv -Path $ExportPath -NoTypeInformation

Write-Host "List data exported successfully to $ExportPath"

CSV file generated at C:\Migration\ProjectTasks.csv!

---

Step 3: Export List Data with Attachments

If list items include attachments, use this script to download them.

Save this script as C:\Migration\ExportListWithAttachments.ps1

# Define Variables
$SiteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
$ListName = "ProjectTasks"
$ExportPath = "C:\Migration\ProjectTasks.csv"
$AttachmentFolder = "C:\Migration\Attachments"

# Ensure Attachment Directory Exists
If (!(Test-Path -Path $AttachmentFolder)) {
    New-Item -ItemType Directory -Path $AttachmentFolder
}

# Connect to SharePoint
Connect-PnPOnline -Url $SiteUrl -UseWebLogin

# Retrieve List Items
$Items = Get-PnPListItem -List $ListName -PageSize 500 | Select-Object FieldValues, FieldValuesAsText

$ExportData = @()
foreach ($Item in $Items) {
    $Attachments = $Item["FieldValues"]["Attachments"]
    $AttachmentUrls = @()
    
    if ($Attachments -eq $true) {
        $ItemId = $Item["FieldValues"]["ID"]
        $AttachmentsList = Get-PnPProperty -ClientObject $Item -Property "AttachmentFiles"
        
        foreach ($Attachment in $AttachmentsList) {
            $FileUrl = $Attachment.ServerRelativeUrl
            $FileName = $FileUrl.Split("/")[-1]
            $LocalFilePath = "$AttachmentFolder\$FileName"
            
            Get-PnPFile -Url $FileUrl -Path $LocalFilePath -AsFile -Force
            $AttachmentUrls += $FileName
        }
    }

    $ExportData += [PSCustomObject]@{
        ID = $Item["FieldValues"]["ID"]
        Title = $Item["FieldValues"]["Title"]
        Status = $Item["FieldValues"]["Status"]
        Created = $Item["FieldValues"]["Created"]
        Modified = $Item["FieldValues"]["Modified"]
        Attachments = ($AttachmentUrls -join "; ")
    }
}

# Export to CSV
$ExportData | Export-Csv -Path $ExportPath -NoTypeInformation

Write-Host "List data and attachments exported successfully!"

Attachments downloaded and data exported!

---

Step 4: Export List Data to JSON

JSON is useful when migrating structured data to other systems.

Save this script as C:\Migration\ExportListDataJSON.ps1

# Define Variables
$SiteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
$ListName = "ProjectTasks"
$ExportPath = "C:\Migration\ProjectTasks.json"

# Connect to SharePoint
Connect-PnPOnline -Url $SiteUrl -UseWebLogin

# Retrieve List Items
$Items = Get-PnPListItem -List $ListName -PageSize 500 | Select-Object FieldValues

# Convert Data to JSON
$ExportData = @()
foreach ($Item in $Items) {
    $ExportData += @{
        ID = $Item["FieldValues"]["ID"]
        Title = $Item["FieldValues"]["Title"]
        Status = $Item["FieldValues"]["Status"]
        Created = $Item["FieldValues"]["Created"]
        Modified = $Item["FieldValues"]["Modified"]
    }
}

# Save JSON File
$ExportData | ConvertTo-Json -Depth 2 | Out-File -Encoding utf8 $ExportPath

Write-Host "List data exported successfully to $ExportPath"

JSON file generated for structured migration!

---

Step 5: Verify Exported Data

After running the scripts, verify exported data:
CSV → Open in Excel to check the structured format.
JSON → Open with a text editor to confirm structured data.
Attachments → Ensure all files exist in C:\Migration\Attachments.

Everything is ready for migration!