Import Excel Data To SharePoint Online Using PowerShell

Introduction

In this article, we will go through the steps required to import the Excel file data to the SharePoint online list using PnP PowerShell. There are a lot of ways to import the data, but for this article, we will stick to the PowerShell way. Here, the credit goes to person Danile Finke, who developed the Import Excel PowerShell module and made it available in PowerShell Gallery. Kudos to him.

Steps

Step 1. Open the PowerShell with Admin Privileges. Check if the PowerShell module 'ImportExcel' is installed. You can check the installed modules from PowerShell by running Get-Module in the PowerShell ISE or PowerShell Command window.

Get-Module

As you observed, in my case, the ImportExcel module is installed.

Step 2. Check the availability of the module in the PowerShell Gallery. You can do so by entering the below command.

Find-Module

The Find-Module queries the PowerShell Gallery and gets the information from it.

Step 3. Install the module "ImportExcel" by entering the below command in the PowerShell window.

Install-Module -Name ImportExcel -Scope AllUsers

Note. You may be prompted if you trust the repository. Select 'Yes'.

Step 4. After installing, you may have to import-module.

If you are not able to find the module, try the import-module command.

Import-Module -Name ImportExcel

Step 5. Now check the Excel Columns by importing the Excel data.

$data = Import-Excel -Path "C:\Temp\TestRecords.xlsx"

Here, the columns marked in the screenshot above are present in excel. Create a PS Custom Object accordingly. You can refer to the script section on how to do this.

Step 6. Check if the PnP PowerShell module is installed. If not, check the article in the references on how to install the PnP PowerShell module. If installed, import the module.

Import-Module -Name PnP.PowerShell

Step 7. Declare the following variables using the Site URL and the List Name where the Excel data needs to be imported.

$SiteUrl = "https://contoso.sharepoint.com/sites/ContosoDev"

$ListName = "MyTestList"

Step 8. Initiate a connection to SharePoint online using the parameter -interactive.

Connect-PnPOnline -url $SiteUrl -interactive

Note - Please note that the account running the script should have Site Collection Administrator rights.

Step 9. Read the excel data using the import-excel command and store it in a variable.

$data = Import-Excel -Path "C:\Temp\TestRecords.xlsx"

Step 10. Read each excel row data, and for each row record, update the SharePoint list using the Add-PnPListItem command.

Step 11. Validate the data in the SharePoint online list.

Complete Script

#Read the Excel Data and change path accordingly
$data = Import-Excel -Path "C:\Temp\MyTestRecords.xlsx"
#Declare Site URL and List names
$SiteUrl = "https://Contoso.sharepoint.com/sites/ContosoDev"
# Update with the internal name of the SharePoint list.
$ListName = "MyTestRecords"
#Initiate a connection to SharePoint site
Connect-PnPOnline -Url $SiteUrl -Interactive
try {
    ForEach($record in $data) {
        Write -Host "Adding record $($record.'Record Series')"
        #Add List Items - Map with Internal Names of the Fields!.Below you define a custom object @ {}for each record and update the SharePoint list.        
        Add-PnPListItem -List $ListName -Values @{
            "Title" = $($record.'Record Series');
            "RecordCategory" = $($record.'Category');
            "RecordDescription" = $($record.'Description');
            "RecordSeriesName" = $($record.'Record Series Name');
            "GMFIORetentionPeriod" = $($record.'Retention Period');
            "CountrySpecificDeviation" = $($record.'Country Specific Deviation');
            "ExampleRecord" = $($record.'Example Records');
            "RecordComments" = $($record.'Record Comments');
            "StartApproval" = $false;
            "ApprovalStatus" = "Approved";
        };
    }
} catch {
    Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
}

Conclusion

Thus, in this article, we have seen how to import the Excel data to the SharePoint online list using the PnP PowerShell Module.

References