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

excelimportcsom.png

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.

Steps:

  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

#Connection
#Using SPO SDK, Remember to install this First
#https://www.microsoft.com/en-us/download/details.aspx?id=42038

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)
{
try
{
#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

$listItem.Update()
$ctx.load($list)
$ctx.executeQuery()
Write-Host “Item Added with ID – ” $listItem.Id
}
catch
{
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:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s