如何修改PowerShell脚本实现CSV按指定行数分批次生成

以下是修改后的PowerShell脚本，实现按指定行数拆分CSV输出，自动生成带序号的文件名（如SharePointAllTenantFilesReport072825-1.csv、SharePointAllTenantFilesReport072825-2.csv等）：

#Config Variables
$TenantAdminURL = "https://test-admin.sharepoint.com"
$CSVFilePath = "C:\Users\location\Documents\SP_Reports\SharePointAllTenantFilesReport072825.csv"
$BatchSize = 10000 # 每个CSV文件的最大行数，可根据需求调整

# Define the App ID
$ENTRAID_APP_ID = "PowerShell Entra App ID"

# 拆分CSV路径为目录、文件名和扩展名，用于生成带序号的文件名
$FileDir = Split-Path -Path $CSVFilePath -Parent
$FileNameWithoutExt = [System.IO.Path]::GetFileNameWithoutExtension($CSVFilePath)
$FileExt = [System.IO.Path]::GetExtension($CSVFilePath)

# 初始化分文件跟踪变量
$TotalRows = 0
$FileIndex = 1
$CurrentCSVPath = Join-Path -Path $FileDir -ChildPath "$FileNameWithoutExt-$FileIndex$FileExt"

#Connect to Admin Center using PnP Online
Connect-PnPOnline -Url $TenantAdminURL -Interactive -ClientID $ENTRAID_APP_ID

# 删除已存在的同系列报告文件
Get-ChildItem -Path $FileDir -Filter "$FileNameWithoutExt-*$FileExt" | Remove-Item -Force

#Get All Site collections - Exclude: Seach Center, Redirect site, Mysite Host, App Catalog, Content Type Hub, eDiscovery and Bot Sites
$SiteCollections = Get-PnPTenantSite | Where { $_.URL -like '*/sites*' -and $_.Template -NotIn ("SRCHCEN#0", "REDIRECTSITE#0", "SPSMSITEHOST#0", "APPCATALOG#0", "POINTPUBLISHINGHUB#0", "EDISC#0", "STS#-1")}

#Get All Large Lists from the Web - Exclude Hidden and certain lists
$ExcludedLists = @("Form Templates", "Preservation Hold Library","Site Assets", "Pages", "Site Pages", "Images",
                        "Site Collection Documents", "Site Collection Images","Style Library")
$SiteCounter = 1   
#Loop through each site collection
ForEach($Site in $SiteCollections)
{    
    #Display a Progress bar
    Write-Progress -id 1 -Activity "Processing Site Collections" -Status "Processing Site: $($Site.URL)' ($SiteCounter of $($SiteCollections.Count))" -PercentComplete (($SiteCounter / $SiteCollections.Count) * 100)
    #Connect to the site
    Connect-PnPOnline -Url $Site.URL -Interactive -ClientID $ENTRAID_APP_ID
    #Get all document libraries
    $DocumentLibraries = Get-PnPList | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $False -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt 0}
    $ListCounter = 1
    #Iterate through document libraries
    ForEach ($List in $DocumentLibraries)
    {
        $global:counter = 0
        $FileData = @()
        Write-Progress -id 2 -ParentId 1 -Activity "Processing Document Libraries" -Status "Processing Document Library: $($List.Title)' ($ListCounter of $($DocumentLibraries.Count))" -PercentComplete (($ListCounter / $DocumentLibraries.Count) * 100)
        #Get All Files of the library with size > 0MB
        $Files = Get-PnPListItem -List $List -Fields Author,FileLeafRef,FileRef,SMTotalFileStreamSize -PageSize 500 -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -Id 3 -parentId 2 -PercentComplete ($global:Counter / ($List.ItemCount) * 100) -Activity "Getting List Items of '$($List.Title)'" -Status "Processing Items $global:Counter to $($List.ItemCount)";} | Where {($_.FileSystemObjectType -eq "File") -and ($_.FieldValues.SMTotalFileStreamSize/1MB -gt 0)} 
        #Collect data from each files
        ForEach ($File in $Files)
        {
            $FileData += [PSCustomObject][ordered]@{
                Library      = $List.Title
                FileName     = $File.FieldValues.FileLeafRef
                URL          = $File.FieldValues.FileRef
                Created      = $File.FieldValues.Created_x0020_Date
                CreatedBy    = $File.FieldValues.Author.Email
                Modified     = $File.FieldValues.Last_x0020_Modified
                ModifiedBy   = $File.FieldValues.Editor.Email
                Size         = [math]::Round(($File.FieldValues.SMTotalFileStreamSize/1MB),2)
            }
        }
        # 处理分文件导出逻辑
        if ($FileData.Count -gt 0) {
            $SortedFileData = $FileData | Sort-object Size -Descending
            $RowCount = $SortedFileData.Count
            $TotalRows += $RowCount

            # 当累计行数超过批量限制时，拆分数据到新文件
            while ($TotalRows -gt $BatchSize) {
                # 计算当前文件可写入的剩余行数
                $RowsToWrite = $BatchSize - ($TotalRows - $RowCount)
                # 写入当前文件剩余额度的数据
                if (-not (Test-Path $CurrentCSVPath)) {
                    $SortedFileData[0..($RowsToWrite-1)] | Export-Csv -Path $CurrentCSVPath -NoTypeInformation -Force
                } else {
                    $SortedFileData[0..($RowsToWrite-1)] | Export-Csv -Path $CurrentCSVPath -NoTypeInformation -Append -Force
                }
                # 保留剩余未写入的数据
                $SortedFileData = $SortedFileData[$RowsToWrite..($RowCount-1)]
                $RowCount = $SortedFileData.Count
                $TotalRows = $RowCount

                # 切换到新的序号文件
                $FileIndex++
                $CurrentCSVPath = Join-Path -Path $FileDir -ChildPath "$FileNameWithoutExt-$FileIndex$FileExt"
            }

            # 写入剩余数据到当前文件
            if (-not (Test-Path $CurrentCSVPath)) {
                $SortedFileData | Export-Csv -Path $CurrentCSVPath -NoTypeInformation -Force
            } else {
                $SortedFileData | Export-Csv -Path $CurrentCSVPath -NoTypeInformation -Append -Force
            }
        }

        $ListCounter++
    }
    $SiteCounter++
}

# 断开PnP连接
Disconnect-PnPOnline

关键改动说明 #

• 批量行数配置：新增$BatchSize变量，可根据需求设置每个CSV文件的最大行数（示例为10000行）
• 动态文件名生成：拆分原CSV路径为目录、文件名和扩展名，自动生成带序号的文件名（如xxx-1.csv、xxx-2.csv）
• 分文件逻辑：跟踪累计行数，当达到批量限制时自动切换到新文件，拆分当前批次数据分别写入
• 表头处理：每个新文件首次写入时自动生成表头，后续追加数据时跳过表头，避免重复
• 清理旧文件：脚本启动时自动删除同系列的旧报告文件，避免数据混杂

内容的提问来源于stack exchange，提问作者Kenneth Kerr