How to Split a Large Excel File into Smaller Chunks Using PowerShell

Working with massive Excel files can be cumbersome—slow to open, hard to process, and error-prone in automation. If you’re dealing with a large dataset and need to split it into smaller, manageable files, PowerShell offers a powerful and efficient way to do it—especially with the help of the ImportExcel module.

In this guide, I’ll walk you through a simple script that takes a large Excel file and splits it into multiple smaller Excel files, each containing a defined number of records.

Requirements

• PowerShell

• ImportExcel module
  You can install it via PowerShell with:

          Install-Module -Name ImportExcel

# Import the ImportExcel module

Import-Module ImportExcel

# Path to the large Excel file

$excelFilePath = "[LocalFilePathwithFileExtention]"

# Define the chunk size (e.g., 10,000 records per chunk)

$chunkSize = 10000

# Read the Excel file

$excelData = Import-Excel -Path $excelFilePath

# Calculate how many chunks are needed

$totalRows = $excelData.Count

$chunkCount = [math]::Ceiling($totalRows / $chunkSize)

# Loop to split and save each chunk

for ($i = 0; $i -lt $chunkCount; $i++) {

    $startIndex = $i * $chunkSize

    $endIndex = [math]::Min(($startIndex + $chunkSize), $totalRows)

    # Extract the subset of data

    $chunkData = $excelData[$startIndex..($endIndex - 1)]

    # Define output file path

    $chunkFilePath = "[LocalFilePathwithFileLocation]chunk_$($i + 1).xlsx"

    # Export to a new Excel file

    $chunkData | Export-Excel -Path $chunkFilePath

    Write-Host "Saved chunk $($i + 1) to $chunkFilePath"

}

Write-Host "Splitting completed!"

What This Script Does

1. Imports the Excel file using the Import-Excel cmdlet.

2. Divides the data into smaller chunks (10,000 rows by default).

3. Exports each chunk into a separate .xlsx file.

4. Prints progress messages to the console for visibility.

Why Use This Approach?

• Avoids Excel file size limitations.

• Ideal for batch processing or feeding smaller files into automated workflows.

• Fast and memory-efficient compared to loading Excel in the GUI.

Customization Tips

• Change $chunkSize to adjust how many records go into each file.

• Modify $chunkFilePath to suit your file naming or storage structure.

• Integrate this into a scheduled task or data pipeline for regular use.