Introduction
In this article, we will go through the steps required to import the Excel file data to SharePoint online list using PnP PowerShell. There are a lot of ways to import the data, but for this article, we will stick to 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 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’.
SharePoint week – 8-11 May 2023.
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 6
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 5
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
Declare the following variables with 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 7
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 8
Read the excel data using the import-excel command and store it in a variable.
$data = Import-Excel -Path “C:\Temp\TestRecords.xlsx”
Step 9
Read each excel row data, and for each row record, update the SharePoint list using the Add-PnPListItem command.
Step 10
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.'GMF IO 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 SharePoint online list using PnP PowerShell Module.
References
- https://www.powershellgallery.com/packages/ImportExcel/7.8.4
- https://github.com/dfinke/ImportExcel
- PNP Powershell How To Get Started
- Working With PnP Powershell
About the Author
M365 developer
Reference
Ayinapurapu, V., 2023, Import Excel Data To SharePoint Online Using PowerShell, c-sharpcorner.com, Available at: https://www.c-sharpcorner.com/article/import-excel-data-to-sharepoint-online-using-powershell/ [Accessed on 4 May 2023]