SharePoint development Training

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

add items to list using powershell

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.

sharepoint online add list items powershell from csv
sharepoint online add list items powershell from csv

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

sharepoint online powershell add list item
sharepoint online powershell add list item

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

add items to list using powershell from csv
add items to list using powershell from csv

You may like following SharePoint PowerShell tutorials:

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.

Check out Best Alternative to InfoPath -> Try Now

free sharepoint training

SharePoint Online FREE Training

JOIN a FREE SharePoint Video Course (3 Part Video Series)

envelope
envelope