Lists in SharePoint Online provide an efficient system for managing records in a table structure. The List can be used to store general information such as project tasks, sales records, and more. In this blog, we will explore how to import list items from a CSV file and add the records into a SharePoint Online List using PnP PowerShell.
Before you start, run the following command to install the latest PnP PowerShell module if you have not already installed it.
Install-Module -Name "PnP.PowerShell"
We can use the Add-PnPListItem cmdlet to add an item to a list. The below command adds a new list item to the “Customer Data” list and sets the column fields “Name” and “Company” with the specified values.
#Connect to PnP Online
Connect-PnPOnline "https://contoso.sharepoint.com/sites/sitename"
#Add list item
Add-PnPListItem -List "Customer Data" -Values @{"Name" = "Alex Wilber"; "Company"="Abc Enterprises Inc"}
Import List Items from CSV file into SharePoint Online List
You can use the above command to add a single record to a list. In a large environment, we may need to add a bulk number of records. Consider the CSV file “CustomerData.csv” (Download sample CSV) which holds the customer contact information in each row with the column headers Name, Company, Email, Phone, City, and Address. The below script imports the customer contacts from the CSV file and adds the record (list item) one by one into the given SPO list.
#Provide Site URL and List name
$SiteURL = "https://contoso.sharepoint.com/sites/site_name"
$ListName = "Customer Data"
#Specify the CSV file path
$CSVPath = "C:\Temp\CustomerData.csv"
#Read customer records from the CSV file
$CustomerData = Import-CSV $CSVPath
$i = 0;
$TotalRecords = $CustomerData.Count
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL
#Iterate through each row in the CSV and import it as a list item to the SPO List.
ForEach ($Record in $CustomerData)
{
$i++;
Write-Progress -activity "Adding the record '$($Record.'Name')'" -status "$i out of $TotalRecords completed"
#Add List Item
Add-PnPListItem -List $ListName -Values @{
"Name" = $Record.'Name';
"Company" = $Record.'Company';
"Email" = $Record.'Email';
"Phone" = $Record.'Phone';
"City" = $Record.'City';
"Address" = $Record.'Address';
};
}
Note: Ensure that you have used the internal name (Column name) of the list item field. If you have a different value for the display name and internal name of the column field and you have used the display name in the above script, then we will get the following error.
“Add-PnPListItem : Column ‘Name’ does not exist. It may have been deleted by another user.”