Use PowerShell to create an item in a SharePoint List

In this post we’ll look at use PowerShell to create an item in a SharePoint List pulling the information from a CSV file

One scenario for this could be for example having an on-premise system with no external cloud access that needs to be imported into SharePoint onPrem or SharePoint Online.

In this Scenario the other system (SQL for example) could dump the requested data into an CSV export which could then be read and imported into SharePoint via PowerShell.

To do this create a scheduled task that runs on a server that  opens up the script below, updating the file location and name and the relevant column fields as necessary:

[PowerShell]
##### This is the path to the CSV data file
$file = Import-CSV C:\exported_sql.csv

# Add references to SharePoint client assemblies and authenticate to Office 365 site – required for CSOM
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.WorkflowServices.dll”

# Specify tenant admin and site URL
$SiteUrl = “https://TENANCY.sharepoint.com/mySite”
$ListName = “MY LIST NAME”
$UserName = “Admin e-mail address”
$Password = Read-Host -Prompt “Please enter your password” -AsSecureString
#$SecurePassword = ConvertTo-SecureString $Password -AsPlainText -Force

# Bind to site collection
$ClientContext = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $Password)
$ClientContext.Credentials = $credentials
$ClientContext.ExecuteQuery()

# Get List
$List = $ClientContext.Web.Lists.GetByTitle($ListName)
$ClientContext.Load($List)
$ClientContext.ExecuteQuery()

#iterate through all items in list
$file | ForEach-Object {
# Create new List Item Object with data rom CSV Row
# Each column heading gets pulled from the $_. object
#$_.Name
#$_.PersonID
#$_.PostCode
# Create Single List Item
$ListItemCreationInformation = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$NewListItem = $List.AddItem($ListItemCreationInformation)
$NewListItem[“Title”] = $_.Name
$NewListItem[“Referral_x0020_ID”] = $_.PersonID
$NewListItem[“Postcode”] = $_.PostCode
$NewListItem[“Referral”] = ‘Full Consent Update’
$NewListItem.Update()
$ClientContext.ExecuteQuery()
}
[/PowerShell]