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

Scenario :
These day most of the user 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 add items in SharePoint list in Office 365 site using PowerShell with the input from csv file.

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

Code Block :

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”

You can also read few PowerShell tutorials:
– Export User Permissions to HTML using PowerShell in SharePoint Online

– Windows PowerShell ISE Tutorial

– Upload bulk documents to SharePoint 2016 document library using PowerShell

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 screen shot of code used.