Skip to main content

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.

Comments

Post a Comment

Popular posts from this blog

Get App Expiry Dates using Powershell

Step 1: Connect-MsolService Step 2: $applist = Get-MsolServicePrincipal -all  |Where-Object -FilterScript { ($_.DisplayName -notlike "*Microsoft*") -and ($_.DisplayName -notlike "autohost*") -and  ($_.ServicePrincipalNames -notlike "*localhost*") } Step 3: foreach ($appentry in $applist) {     $principalId = $appentry.AppPrincipalId     $principalName = $appentry.DisplayName     Get-MsolServicePrincipalCredential -AppPrincipalId $principalId -ReturnKeyValues $false | ? { $_.Type -eq "Password" } | % { "$principalName;$principalId;" + $_.KeyId.ToString() +";" + $_.StartDate.ToString() + ";" + $_.EndDate.ToString() } | out-file -FilePath d:\appsec.txt -append }
Post a Comment
Read more

Bulk Import Excel Data to SharePoint List Using PowerShell and PnP

  Managing large datasets in SharePoint can be tricky, especially when you're dealing with Excel files and need to avoid list view threshold issues. In this guide, I’ll walk you through a PowerShell script that efficiently imports data from Excel into a SharePoint Online list using PnP PowerShell — with batching support for performance. Prerequisites Make sure you have the following before running the script: SharePoint Online site URL Excel file with data properly formatted PnP PowerShell module installed ( Install-Module PnP.PowerShell ) Appropriate SharePoint permissions What the Script Does Connects to your SharePoint site Loads and reads an Excel file Converts Excel date values Batches records in groups (to avoid the 5000 item threshold) Adds the items to your SharePoint list or library Logs execution time PowerShell Script $siteUrl = "[Site Collection URL]" Connect-PnPOnline -Url $siteUrl -UseWebLogin # Capture the start time $startTime...
Post a Comment
Read more

🚀 Essential SPFx (SharePoint Framework) Commands for Every Developer

Whether you're new to SharePoint Framework (SPFx) or a seasoned pro, having a go-to list of core commands can significantly improve your development workflow. In this post, we'll walk through the most important SPFx CLI commands—from setting up your environment to packaging and deploying your solution. 🛠️ Setting Up Your SPFx Project Start by scaffolding a new project using the Yeoman generator provided by Microsoft. Make sure Node.js and npm are installed before you proceed. yo @microsoft/sharepoint After scaffolding the project, install all dependencies: npm install To update dependencies later: npm update 🔐 Trusting the Development Certificate If you're using the local workbench, you need to trust the developer certificate for HTTPS support: gulp trust-dev-cert 🧪 Running the Local Workbench To build and serve your project locally, use: gulp serve This command starts a local server at: https://localhost:4321/temp/workbench.html You can also test your solution in ShareP...
Post a Comment
Read more