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