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 = Get-Date

# Define lists and field mappings

$LibraryName = "[LibraryName]"

$excelFilePath = "[excelFilePathwithExtention]" 

# Import data from the Excel file using Import-Excel

$excelData = Import-Excel -Path $excelFilePath

# Define the batch size (to avoid SharePoint's list view threshold of 5000 items)

$batchSize = 200

# Initialize a list to hold batches of items

$batch = @()

# Loop through each row in the Excel data

foreach ($row in $excelData) {

    # Calculate the OBSERVATION_DATE

    $baseDate = [datetime]"1900-01-01"

    $convertedDate = $baseDate.AddDays($row.OBSERVATION_DATE - 2)

    # Map Excel data to SharePoint fields

    $Values = @{

        "Title"                     = $row.Title

        "SHIFT"                     = $row.SHIFT

        "OBSERVATION_DATE"         = $convertedDate

        "BEHAVIOR"                 = $row.BEHAVIOR

        "ROLE"                      = $row.ROLE

        "Behaviour_unsafeCondition" = if ($row.AT_RISK_BY_UNSAFE_COND -eq "N") { "No" } 

                                      elseif ($row.AT_RISK_BY_UNSAFE_COND -eq "Y") { "Yes" } 

                                      else { "" }

        "LOCATION_ID"               = $row.LOCATION_ID

        "AREA_ID"                   = $row.AREA_ID

        "SUB_AREA_ID"               = $row.SUB_AREA_ID

        "LINE_ID"                   = if ($null -eq $row.LINE_ID -or $row.LINE_ID -eq "") { $null } else { $row.LINE_ID }

        "CATEGORY_ID"               = $row.CATEGORY_ID

        "SUB_CATEGORY_ID"           = $row.SUB_CATEGORY_ID

    }

    $batch += $Values

    # Process the batch if it reaches the threshold

    if ($batch.Count -ge $batchSize) {

        foreach ($item in $batch) {

            Add-PnPListItem -List $LibraryName -Values $item -Verbose

        }

        Write-Host "Added $($batch.Count) items to $LibraryName"

        $batch.Clear()

    }

}

# Add remaining items if any

if ($batch.Count -gt 0) {

    foreach ($item in $batch) {

        Add-PnPListItem -List $LibraryName -Values $item -Verbose

    }

    Write-Host "Added the last batch of $($batch.Count) items to $LibraryName"

}

# Calculate execution time

$endTime = Get-Date

$executionTime = $endTime - $startTime

Write-Host "Script executed in:" $executionTime

Key Benefits

• Efficient batching: Avoids the 5000-item limit by processing in chunks.

• Dynamic date conversion: Transforms Excel numeric dates into valid DateTime objects.

• Safe and robust: Skips null values and handles conditional logic cleanly.