Batch file creation after x numbers of rows of data reached

I have a PowerShell Script that give me an output of every file in our SharePoint Online tenant. The code works, the file get output in CSV format but the script is failing because it hits the max number of rows that a csv file can support and the script can no longer write to the file. How would I modify the code below to create a new file after x number of rows are written?

In the code, I thought using a "-Batch" parameter would have worked, but it didn't.

$FileData | Export-Csv -Path $CSVFilePath -NoTypeInformation -Batch

After running completely I would expect to see the below...

Files: SharePointAllTenantFilesReport072825-1.csv SharePointAllTenantFilesReport072825-2.csv SharePointAllTenantFilesReport072825-3.csv SharePointAllTenantFilesReport072825-4.csv SharePointAllTenantFilesReport072825-5.csv etc.

#Config Variables
$TenantAdminURL = "https://test-admin.sharepoint.com"
$CSVFilePath = "C:\Users\location\Documents\SP_Reports\SharePointAllTenantFilesReport072825.csv"
 
# Define the App ID
$ENTRAID_APP_ID = "PowerShell Entra App ID"
 
#Connect to Admin Center using PnP Online
Connect-PnPOnline -Url $TenantAdminURL -Interactive -ClientID $ENTRAID_APP_ID
 
#Delete the Output Report, if exists
if (Test-Path $CSVFilePath) { Remove-Item $CSVFilePath }
 
#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)
            }
        }
        #Export Files data to CSV File
        $FileData | Sort-object Size -Descending
        $FileData | Export-Csv -Path $CSVFilePath -NoTypeInformation -Append
        $ListCounter++
        #Write-Progress -Activity "Completed Processing List $($List.Title)" -Completed -id 2
    }
    $SiteCounter++
}

Solution

Try writing one line at a time instead of appending which is more efficient

$file = 'c:\\temp\test.csv'
$writer = [System.IO.StreamWriter]::New($file)
$Header = [string]::Join(',',@('Library','FileName','URL','Created','CreateBy','Modified','ModifiedBy','Size'))
$writer.WriteLine($Header)

foreach($i in 0..9) {
   $FileData = [PSCustomObject]@{
      Library      = 'Library'
      FileName     = 'FileName'
      URL          = 'URL'
      Created      = 'Created'
      CreatedBy    = 'CreateBy'
      Modified     = 'Modified'
      ModifiedBy   = 'ModifiedBy'
      Size         = 'Size'
   }
   $line = [string]::Join(',',@($FileData.Library,$FileData.FileName,$FileData.URL,$FileData.Created,$FileData.CreatedBy,$FileData.Modified,$FileData.ModifiedBy,$FileData.Size))
   $writer.WriteLine($line)
}
$writer.Flush()
$writer.Close()