SharePoint 101 – Importing Excel data to SharePoint Online List – CSOM


Business case:
We have a lot of data in Excel files today, and we want to convert this to use SharePoint Online List, for obvious reasons like integration with @Flow and @PowerApps.

With the right steps, the script below using SCOM will help you achieve this.


  1. In SharePoint Online Create a new List with all the columns you need
  2. Save your Excel file as .csv
  3. Modified the script below to match your case
  4. Run it and your data is now available in SharePoint Online List

#Using SPO SDK, Remember to install this First

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”

#Connecting to your site
$siteURL = “Add your site URL”
$userId = “Account”
$pwd = ConvertTo-SecureString “Password” -AsPlainText -Force
$creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userId, $pwd)
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$ctx.credentials = $creds

#Location of your csv file to import
$projectTable = import-csv -Path ‘Path’ -Delimiter ‘,’

$separator = “;”
$option = [System.StringSplitOptions]::RemoveEmptyEntries


foreach ($row in $projectTable)
#Connecting to the list
$lists = $ctx.web.Lists
$list = $lists.GetByTitle(“Project List Example”)
$listItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation

#Event is Add new Item
$listItem = $list.AddItem($listItemInfo)

#Text Field
$listItem[“Title”] = $row.”Title”

#Date Field
$listItem[“StartDate”] = [datetime]::ParseExact($row.”StartDate”, “d/M/yyyy”, $null)
$listItem[“EndDate”] = [datetime]::ParseExact($row.”EndDate”, “d/M/yyyy”, $null)

#Single Choice Field
$listItem[“Department”] = $row.”Department”

#Multi Choice Field
$listItem[“Region”] = $row.”Region”.Split($separator,$option)

#People Picker Field
$NewUser =$ctx.Web.EnsureUser(“i:0#.f|membership|”+ $row.”UserUPN”)
$listItem[“CompletedBy”] = $NewUser

Write-Host “Item Added with ID – ” $listItem.Id
write-host “$($_.Exception.Message)” -foregroundcolor red


Next I’ll try this with PnP-PowerShell, stay tuned for another post



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s