Steps to add items from CSV file to SharePoint Online List using PowerShell in CSOM

This SharePoint Online tutorial explains, how to add items from CSV file to SharePoint Online list using PowerShell CSOM.

These days most of the users want to add data in SharePoint List and Libraries in SharePoint 2010, SharePoint 2013 and in Office 365. In this article, we learn how to add items in SharePoint list in Office 365 site. Today we will learn how to add items in SharePoint list in Office 365 site using PowerShell with the input from a csv file.

To improve the efficiency and saving time during migration it is always good to go with PowerShell scripting for the initial setup.

Add items from csv file to SharePoint Online List using PowerShell in CSOM

Below is the full PowerShell script to insert items to SharePoint Online list from CSV file.

If you are new to PowerShell, you can read an article on Working with PowerShell in SharePoint Online/2016/2013.

Below script, you can execute using Windows PowerShell ISE.

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#next pass the credentials and connect to web
$siteURL = "https://hubto.sharepoint.com/sites/learnhub"
$Listname="sitecollections"
$userName = "User.onmicrosoft.com"
$PlainPassword = "Password"
$ImportFile ="D: \Powershell\LearnPowershell\Test.csv"
$password = $PlainPassword | ConvertTo-SecureString -AsPlainText -Force
# set SharePoint Online credentials
$SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)
#Creating client context object
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$clientContext.credentials = $SPOCredentials
$web = $clientContext.Web
$clientContext.load($web)
#Get the List
$List = $clientContext.Web.Lists.GetByTitle($Listname)
$clientContext.Load($List)
$clientContext.executeQuery()
$csv = Import-CSV $ImportFile
foreach($row in $csv)
{
#Creat single list Items
$ListItenCreationInformation =New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$NewListItem = $List.AddItem($ListItenCreationInformation)
$NewListItem["Sourcesite"] = $row. Sourcesite
$NewListItem["TargetSite"] = $row. TargetSite
$NewListItem["WaveId"] = $row.WaveId
$NewListItem["Status"] = $row.Status
$NewListItem.Update()
$ClientContext.ExecuteQuery()
}
Write-Host "Items Added to List Sucessfully"

Let Us Walk Through the Code:

1- Importing all the Data in CSV file.
D: \Powershell\LearnPowershell\Test.csv”

2- In Test.csv file I have capture the input data as below:

3- The below line of code is set SharePoint Online credentials.
$SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)

4- The below line for Creating client context object
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)

$clientContext.credentials = $SPOCredentials

5- Refer below screenshot of code used.

You can see the SharePoint list contains no item before executing the PowerShell script.

After running about PowerShell script, you can see the items from CSV file has been added to SharePoint Online list.

You may like following SharePoint PowerShell tutorials:

• Extract term store in SharePoint 2013/2016 using PowerShell
• Download Blobs from Azure Container using PowerShell
• Enable Save Site as Template option in Publishing Site in SharePoint Online using PowerShell
• Install Microsoft SharePoint Server 2016 Using PowerShell
• Add item to SharePoint 2016 list using PowerShell
• How to cancel all workflows from one List in SharePoint using PowerShell?
• How to remove an App from SharePoint 2016 using PowerShell Script?
• SharePoint 2016 Fast Site Collection Creation using PowerShell
• Export list names into CSV using PowerShell in SharePoint 2013
• Bulk Export SharePoint Files using PowerShell

Hope this SharePoint tutorial helps to add items from CSV file to SharePoint Online list using PowerShell CSOM. This way, you can upload bulk items to SharePoint Online list from CSV file.