Import Data from Excel to SharePoint List using PnP PowerShell

One of my clients recently asked me to quickly upload data from an Excel file to a SharePoint Online list. I did that using PowerShell. In this tutorial, I will show you how to import data from Excel to a SharePoint list using PnP PowerShell.

Table of Contents

Import Data from Excel to SharePoint List using PnP PowerShell

Let me explain in detail how to use PnP PowerShell to import data from an Excel file into a SharePoint list. I am sure you might have this kind of requirement regularly.

1. Setup the SharePoint Online List

Here, I have created a list on my SharePoint Online site. The list name is Employee Details, and it has the following columns.

Title (Single line of text)
Employee ID (Single line of text)
Joining Date (Date and Time)
Department (Choice: IT, HR, Finance, Marketing)
Salary (Currency)

You can see how the SharePoint Online list looks like in the screenshot below:

import data from excel to sharepoint list using powershell

2. Create The Excel File

Next, we’ll create an Excel file with sample employee data. Our file, named “EmployeeData.xlsx,” will contain a few records.

Here, I have formatted the Excel data as a table. You can download and use the Excel file.

You can see what it looks like:

import excel data into sharepoint list using powershell

3. Complete PnP PowerShell Script

Now, let’s write the PnP PowerShell script to import the data from the Excel file into our SharePoint list. Here’s the complete script:

# Connect to SharePoint Online
Connect-PnPOnline -Url "https://szg52.sharepoint.com/sites/PowerPlatformFAQs" -Interactive

# Load the Excel file
$excelFile = "C:\Bijay\EmployeeData.xlsx"
$worksheet = "Sheet1"
$excelData = Import-Excel -Path $excelFile -WorksheetName $worksheet

# Import data to SharePoint list
$listName = "Employee Details"
foreach ($row in $excelData) {
    Add-PnPListItem -List $listName -Values @{
        Title = $row.Title
        EmployeeID = $row."Employee ID"
        JoiningDate = $row."Joining Date"
        Department = $row.Department
        Salary = $row.Salary
    }
}

Write-Host "Data import completed successfully!"

Here, the Import-Excel cmdlet loads data from the specified Excel file and worksheet into the $excelData variable.

Then, we loop through each row in the $excelData and use the Add-PnPListItem cmdlet to add the data to the SharePoint list. We specify the list name ("Employee Details") and provide the field values using a hashtable. Ensure the column names in the hashtable match the internal names of the columns in your SharePoint Online list.

I executed the above script using VS code, and you can see that it inserted all the items into the SharePoint list.

import excel data into sharepoint list using pnp powershell

You can also verify the records in the SharePoint Online list. I opened the SharePoint Online list, and you can see the items.

import data from excel to sharepoint list using pnp PowerShell

This is how to import data from Excel to a SharePoint list using PnP PowerShell.

If you get the error below while running the above script, you can follow the solution below.

The term ‘Import-Excel’ is not recognized as a name of a cmdlet, function, script file, or executable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

The error comes as the ImportExcel module either not being installed or not being imported into your PowerShell.

To fix the above error, First, you need to install the ImportExcel module from the PowerShell Gallery. Open your PowerShell terminal (you can use the integrated terminal in VS Code) and run the following command:

Install-Module -Name ImportExcel -Scope CurrentUser

After this, when you run the script, it will not throw any error.

In this tutorial, I explained how to import data from an Excel spreadsheet to a SharePoint Online list using PnP PowerShell.

Import Data from Excel to SharePoint List using PnP PowerShell

1. Setup the SharePoint Online List

2. Create The Excel File

3. Complete PnP PowerShell Script

100 POWERSHELL CMDLETS E-BOOK