Automating Batch File Creation For Large SharePoint Online Data Exports

Jul 30, 2025 by ADMIN 72 views

Hey guys! Ever run into the problem of exporting massive datasets from SharePoint Online and hitting those pesky limits? You're not alone! Many of us who manage SharePoint environments face the challenge of handling large amounts of data, especially when trying to export file information. The default limitations can be a real pain, causing scripts to fail and leaving us scrambling for solutions. This article dives into a practical approach to tackle this issue head-on. We'll explore how to use PowerShell to export file data from SharePoint Online efficiently, automatically creating new batch files once a certain number of rows is reached. This method ensures that you can extract all the necessary information without being hindered by size or row limitations, making your data management tasks smoother and more effective.

Understanding the Challenge: Exporting Large SharePoint Online Datasets

When working with SharePoint Online, exporting data, especially file information, can quickly become a bottleneck due to the sheer volume of data stored in a typical tenant. The standard approach often involves using PowerShell scripts to retrieve file metadata and export it to CSV format. While this method is generally effective, it encounters problems when the dataset grows beyond a certain size. The primary issue is the limitations imposed by both PowerShell's memory management and the CSV format itself. PowerShell scripts can run into memory constraints when handling extremely large datasets, leading to script failures and incomplete exports. Similarly, large CSV files can become unwieldy and difficult to process, often exceeding the capabilities of standard text editors and spreadsheet applications. Furthermore, the time taken to generate a single, massive CSV file can be significant, increasing the risk of interruptions or errors during the export process. Therefore, a more robust solution is needed to handle large-scale data extraction from SharePoint Online, ensuring that all data is captured efficiently and without exceeding system limitations. To address these challenges, we will explore a strategy that involves automatically creating new batch files once a predefined row limit is reached. This approach allows us to break down large exports into manageable chunks, mitigating the risk of script failures and making the resulting data easier to handle.

The Solution: Dynamic Batch File Creation with PowerShell

The key to efficiently handling large SharePoint Online data exports lies in dynamic batch file creation. This method involves setting up your PowerShell script to automatically generate a new CSV file after writing a specific number of rows to the current file. By doing this, you can break your data into smaller, more manageable chunks, avoiding the limitations associated with large single files. Let's break down how this works step by step:

Initial Setup: Start by creating your core PowerShell script to connect to SharePoint Online and retrieve the necessary file data. This typically involves using the Connect-PnPOnline cmdlet to establish a connection and then employing cmdlets like Get-PnPFile or Search-PnPSite to gather the file information you need. The initial part of the script sets the stage for the data extraction process, defining the scope and criteria for the data to be collected.
Setting the Row Limit: Define a variable that specifies the maximum number of rows you want in each CSV file. This limit will depend on your system's capabilities and your preferred file size for manageability. For example, you might set a limit of 10,000 rows per file. This threshold acts as a trigger for creating a new file, ensuring that no single file becomes too large to handle effectively.
Implementing the Counter: Introduce a counter variable to track the number of rows written to the current CSV file. This counter is incremented each time a new row of data is added. The counter's value is continuously monitored to determine when it reaches the predefined row limit, signaling the need for a new file.
Automated File Creation: The heart of the solution lies in the logic that automatically creates new files. When the row counter reaches the defined limit, the script should:
- Close the current CSV file.
- Increment a file counter to create a unique file name (e.g., output_1.csv, output_2.csv).
- Create a new CSV file with the incremented file name.
- Reset the row counter to 1 (to account for the header row in the new file).

This automated process ensures a seamless transition between files, preventing any data loss or interruption in the export process. 5. Writing Data: As the script iterates through your SharePoint Online data, it writes each row to the current CSV file. The critical part here is to increment the row counter after each write operation. This ensures that the counter accurately reflects the number of rows in the file, triggering the creation of a new file when necessary.

By implementing this dynamic batch file creation strategy, you can ensure that your PowerShell scripts efficiently handle large SharePoint Online data exports without hitting size limits. This approach not only prevents script failures but also makes the resulting data more manageable and easier to process.

Practical PowerShell Script Example

To illustrate the concept, let's walk through a practical PowerShell script example that implements dynamic batch file creation for exporting SharePoint Online data. This script connects to your SharePoint Online tenant, retrieves file information, and automatically generates new CSV files once a specified row limit is reached. This example is designed to be a starting point that you can customize to fit your specific needs.

#region Original Script
#Requires -Modules PnP.PowerShell

# Configuration variables
$SiteURL = "https://yourtenant.sharepoint.com/sites/yoursite"
$OutputFolder = "C:\Temp"
$MaxRowsPerFile = 10000 # Set the maximum number of rows per file

# Authentication (using device login)
Connect-PnPOnline -Url $SiteURL -Interactive

# Initialize variables
$FileCounter = 1
$rowCounter = 0
$OutputFile = Join-Path $OutputFolder ("Files_Batch_$FileCounter.csv")

# Create the output folder if it doesn't exist
If (!(Test-Path -Path $OutputFolder)) {
    Write-Host "Creating output folder: $($OutputFolder)"
    New-Item -ItemType Directory -Force -Path $OutputFolder
}

# Function to write data to CSV, handling batch file creation
Function Write-DataToCsv {
    Param (
        [Parameter(Mandatory)]
        [PSObject]$Data
    )

    # Check if a new file needs to be created
    If ($rowCounter -eq 0) {
        # Create a new file and write the header
        $Data | Get-Member -MemberType NoteProperty | ForEach-Object {
            "$($_.Name)" >> $OutputFile
            "," >> $OutputFile
        }
        "`r`n" >> $OutputFile
        $rowCounter++ # Increment the row counter to account for the header
    }

    # Check if the maximum number of rows has been reached
    If ($rowCounter -gt $MaxRowsPerFile) {
        # Increment file counter
        $FileCounter++
        # Reset row counter
        $rowCounter = 1 # Reset the counter to 1 (to account for the header in the new file)
        # Generate a new output file name
        $OutputFile = Join-Path $OutputFolder ("Files_Batch_$FileCounter.csv")
        Write-Host "Creating new file: $($OutputFile)"

        # Write Header Row to the new CSV
        $Data | Get-Member -MemberType NoteProperty | ForEach-Object {
            "$($_.Name)" >> $OutputFile
            "," >> $OutputFile
        }
        "`r`n" >> $OutputFile
    }

    # Convert the object to CSV format and write to file, replacing line breaks
    $Data | ForEach-Object {
        $line = $_ | ConvertTo-Csv -NoTypeInformation
        $line = $line.Replace("`r`n", " - ") # Replace line breaks with " - "
        $line = $line.Replace("\"", "")        # Remove extra quotes
        $line = $line.TrimStart(",")           # Remove leading comma

        $line >> $OutputFile # Append to file
        $rowCounter++          # Increment the row counter
    }
}

# Get all files from the SharePoint site
Write-Host "Getting Files from $($SiteURL)"
$Files = Get-PnPFile -All -ErrorAction SilentlyContinue

Write-Host "Total Files found: $($Files.Count)"

# Loop through each file and extract properties
Write-Host "Exporting File Properties to CSV..."
Foreach ($File in $Files) {
    $FileProps = New-Object PSObject -Property @{
        Name     = $File.Name
        URL      = $File.ServerRelativeUrl
        Modified = $File.TimeLastModified
        Size     = $File.Length
    }
    Write-DataToCsv -Data $FileProps
}

Write-Host "File properties have been exported to $($OutputFolder)"

#endregion

This script provides a robust framework for exporting large amounts of data from SharePoint Online, automatically splitting the output into multiple CSV files based on a defined row limit. The use of the Write-DataToCsv function ensures that the file creation and writing processes are handled efficiently, preventing common issues associated with large data exports. Let's break down the key parts of the script:

Configuration: The script starts by defining several configuration variables. $SiteURL specifies the URL of your SharePoint Online site. Make sure to replace `