PNP PowerShell - Read Excel File And Add Bulk Data Into SharePoint List

In this article, we will see how we can read excel file using PowerShell and add that data to SharePoint List

Input excel file will be stored on local drive.

Excel File

There will be two parameters

1. StartingIndex - It will be starting row number. (It cannot be 1, since first row is column title)
2. Range - How many rows you want to add.

PNP Powershell Code

param(# Starting Index and range[Parameter(Mandatory)]
    [int] $StartingIndex,
    [Parameter(Mandatory)]
    [int] $Range)
$filePath = "C:\ashish\temp\test.xlsx"
# Create an Object Excel.Application using Com interface
$excelObj = New - Object - ComObject Excel.Application
# Disable the 'visible'
property so the document won 't open in excel  
$excelObj.Visible = $false
#open WorkBook
$workBook = $excelObj.Workbooks.Open($filePath)
$SiteURL = "Your Site Url"
$ListName = "TestList"
$UserName = "UserName"
$Password = "Password"
$SecurePassword = ConvertTo - SecureString - String $Password - AsPlainText - Force
$Cred = New - Object - TypeName System.Management.Automation.PSCredential - argumentlist $UserName, $SecurePassword
#connect to sharepoint online site using powershell
Connect - PnPOnline - Url $SiteURL - Credentials $Cred
#Select worksheet using Index
$workSheet = $workBook.sheets.Item(1)
#Select the range of rows should read
for ($i = $StartingIndex; $i - le $Range; $i++) {
    try {
        $Title = $workSheet.Columns.Item(2).Rows.Item($i).Text
        $Name = $workSheet.Columns.Item(3).Rows.Item($i).Text
        $Age = $workSheet.Columns.Item(4).Rows.Item($i).Text
        #Add List Item - Internal Names of the columns: Value
        Add - PnPListItem - List $ListName - Values @ {
            "Title" = $Title;
            "Name" = $Name;
            "Age" = $Age
        }
    } catch {
        Write - Host "Error occured" - BackgroundColor DarkRed
    }
}

Save this code in a file with .ps1 extension and run it

Starting Index - 2 ( skip the first row since it is column title)

Range - 5 ( add till the fifth row)

Final output