SharePoint Online：使用 PowerShell 将列表项导出到 CSV

要求：将 SharePoint Online 列表项导出到 CSV 文件。

如何将 SharePoint Online 列表项导出到 Excel 或 CSV？

您是否需要将数据从 SharePoint Online 列表导出到 CSV？出于多种原因，您可能希望将 SharePoint Online 列表项导出到 Excel 或 CSV。例如，您可能想要创建数据备份。您可能需要将数据迁移到另一个平台或应用程序以进行进一步分析或操作。不管出于什么原因，微软已经让这个过程变得非常简单。本博客文章将向您展示如何使用 PowerShell 和 Web 浏览器界面将 SharePoint Online 列表项导出到 CSV。让我们开始吧！

如何将 SharePoint 列表导出为 CSV？要将 SharePoint Online 列表导出到 CSV 文件，只需导航到列表并单击“导出”菜单中的“导出到 CSV”，SharePoint 就会将列表数据导出到 CSV 文件。就这么简单！

将 SharePoint 列表项导出为 CSV 的 PowerShell 脚本

尽管您可以手动将列表数据导出到 CSV 文件，如上所述，但在某些情况下您可能必须自动执行该过程。我将向您展示如何使用 PowerShell 将 SharePoint Online 列表项导出为 CSV 格式。

以下是通过 PowerShell 将 SharePoint Online 列表导出为 CSV 的方法：

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/poc"
$ListName="Employee"
$ExportFile ="c:\Scripts\ListRpt.csv"
$UserName="[email protected]"
$Password ="Password goes here"

#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"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"]
  
    #Add the object with the 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!"

请注意，此脚本获取列表下的所有列表项，但不会递归获取文件夹和子文件夹的项目（如果您有文件夹和子文件夹）。要递归检索所有列表项，只需将 CAML 查询部分更改为：

#Get all List items from the library Including Items in Sub-Folder
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='FSObjType'/><Value Type='Integer'>0</Value></Eq></Where></Query></View>"
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

SharePoint Online PowerShell 将列表导出到 CSV

虽然上面的脚本将选定的列导出到 Excel 文件，但让我们对其进行更改以将所有列数据导出到 Excel。

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/"
$ListName= "Projects"
$ExportFile ="c:\ListItems.csv"

#Get Credentials to connect
$Cred = Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = $Credentials

#Get the List
$List = $Ctx.web.Lists.GetByTitle($ListName)
 
#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$FieldColl = $List.Fields
$Ctx.Load($ListItems)
$Ctx.Load($FieldColl)
$Ctx.ExecuteQuery()
 
#Array to Hold List Items 
$ListItemCollection = @() 
 
#Fetch each list item value to export to excel
Foreach($Item in $ListItems)
{
    $ExportItem = New-Object PSObject 
    Foreach($Field in $FieldColl)
    {
            if($NULL -ne $Item[$Field.InternalName])
            {
                #Expand the value of Person or Lookup fields
                $FieldType = $Item[$Field.InternalName].GetType().name
                if (($FieldType -eq "FieldLookupValue") -or ($FieldType -eq "FieldUserValue"))
                {
                    $FieldValue = $Item[$Field.InternalName].LookupValue
                }
                else
                {
                    $FieldValue = $Item[$Field.InternalName]   
                }
            }
            $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $FieldValue
    }
    #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!"

和输出：

将较大的 SharePoint Online 列表导出到 CSV 文件

如果您的列表包含超过 5000 个项目怎么办？从彩信、多重查找、URL 等特殊字段获取值怎么样？

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
   
#Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
$ExportFile ="C:\temp\Projects.csv"
$BatchSize = 500

#Get Credentials to connect
$Cred = Get-Credential
 
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Get the List
$List = $Ctx.web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
#Get All List Fields
$FieldColl = $List.Fields
$Ctx.Load($FieldColl)
$Ctx.ExecuteQuery()

#Filter List fields - Skip Read only, hidden fields, content type and attachments
$ListFields = $FieldColl | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne  "ContentType") -and ($_.InternalName -ne  "Attachments") }

#Define Query to get List Items in batch
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = @"
<View Scope='RecursiveAll'>
    <Query>
         <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
    </Query>
<RowLimit Paged="TRUE">$BatchSize</RowLimit>
</View>
"@

#Array to Hold List Items 
$ListItemCollection = @() 
 
#Get List Items in Batch
Do 
{
    $ListItems = $List.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()      

     #Fetch each list item value to export to excel
     Foreach($Item in $ListItems)
     {
        $ExportItem = New-Object PSObject 
        Foreach($Field in $ListFields)
        {
            If($NULL -ne $Item[$Field.InternalName])
            {
                #Handle Special Fields
                $FieldType  = $Field.TypeAsString 
                If($FieldType -eq "User" -or $FieldType -eq "UserMulti" -or $FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti")
                {
                    $FieldValue = $Item[$Field.InternalName].LookupValue -join "; "
                }
                ElseIf($FieldType -eq "URL") #Hyperlink
                {
                    $URL = $Item[$Field.InternalName].URL
                    $Description  = $Item[$Field.InternalName].Description
                    $FieldValue = "$URL, $Description"
                }
                ElseIf($FieldType -eq "TaxonomyFieldType" -or $FieldType -eq "TaxonomyFieldTypeMulti") #MMS
                {
                    $FieldValue = $Item[$Field.InternalName].Label -join "; "
                }
                Else
                {
                    #Get Source Field Value
                    $FieldValue = $Item[$Field.InternalName]
                }
            }
            $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $FieldValue
        }
        #Add the object with above properties to the Array
        $ListItemCollection += $ExportItem
     }
    $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
}
While($Query.ListItemCollectionPosition -ne $null)

#Export the result Array to CSV file
$ListItemCollection | Export-CSV $ExportFile -NoTypeInformation

Write-host "List data Exported to CSV file successfully!"

使用 PnP PowerShell 将 SharePoint Online 列表项导出为 CSV

要将 SharePoint Online 列表导出到 CSV，请使用此 PowerShell 脚本。该脚本从给定列表中导出选定的字段值。您需要做的第一件事是连接到您的 SharePoint Online 网站 URL。然后，从目标列表中获取要导出的所有列表项。最后，运行 Export-CSV cmdlet 并指定要将 SharePoint 列表数据导出到 CSV 文件的路径。 Export-Csv cmdlet 还具有其他有用的参数，例如 Append、Encoding 等。

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/projects"
$ListName = "Projects"
$SelectedFields = @("ProjectName","Project_x0020_Manager", "StartDate") 
$CSVPath = "C:\Temp\ListData.csv"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Get List items from the list
$ListItems = Get-PnPListItem -List $ListName -Fields $SelectedFields -PageSize 500

#Iterate through each item and extract data
$ListDataColl = @()
$ListItems | ForEach-Object {
    $ListData = New-Object PSObject
    #Get the Field Values of the item as text
    $ListItem  = Get-PnPProperty -ClientObject $_ -Property FieldValuesAsText 
    ForEach($Field in $SelectedFields)
    {
        $ListData | Add-Member Noteproperty $Field $ListItem[$Field]
    }
    $ListDataColl += $ListData  
}
#Export data to CSV
$ListDataColl
$ListDataColl | Export-CSV $CSVPath -NoTypeInformation

这会将指定列表中的所有列表项导出到计算机“C:\Temp”文件夹中名为“ListData.csv”的 CSV 文件中。然后，您可以在 Excel 或其他电子表格程序中打开该文件来查看数据。在运行此脚本之前，请确保已安装 PnP PowerShell 模块。

从 SharePoint Online 列表中导出所有可用字段值怎么样？好吧，这是将 SharePoint 在线列表项导出到 CSV 的 PnP PowerShell：

#Config Parameter
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName = "Access Requests"
$CSVPath = "C:\Temp\ListData.csv"
$ListDataCollection= @()

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
$Counter = 0 
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000

#Get all items from list
$ListItems | ForEach-Object {
        $ListItem  = Get-PnPProperty -ClientObject $_ -Property FieldValuesAsText
        $ListRow = New-Object PSObject 
        $Counter++
        Get-PnPField -List $ListName | ForEach-Object {
            $ListRow | Add-Member -MemberType NoteProperty -name $_.InternalName -Value $ListItem[$_.InternalName]
            }
        Write-Progress -PercentComplete ($Counter / $($ListItems.Count)  * 100) -Activity "Exporting List Items..." -Status  "Exporting Item $Counter of $($ListItems.Count)"
        $ListDataCollection += $ListRow
}
#Export the result Array to CSV file
$ListDataCollection | Export-CSV $CSVPath -NoTypeInformation -Encoding UTF8

此脚本获取所有列表项并将它们导出到 CSV 文件。如果您想对列表项应用过滤器，假设您希望获取过去 30 天内创建的所有列表项，您可以使用：

$ListItems = Get-PnPListItem -List $ListName -PageSize 2000 | ? { $_["Created"] -gt (Get-Date).AddDays(-30) }

相关帖子：

• SharePoint Online：使用 PowerShell 进行网站用户和组报告
• 使用 PowerShell 将列表项导出到 SharePoint Server 中的 CSV
• SharePoint Online：使用 PowerShell 从 CSV 批量删除文件