SharePoint Online: Import Excel File Data Into Existing List using PowerShell

Requirement: Import Data from Excel file into existing SharePoint Online List

SharePoint Online: Import Excel to Existing List using PowerShell
While my another post describes on importing data from a CSV file to SharePoint Online list, this script targeting importing the .XSLX file into existing SharePoint Online list.
 Please make sure the data is in right format! E.g. Date column should be in "dd/mm/yyyy" format.

PowerShell to Import Excel Data into Existing SharePoint Online List
Lets import task list from excel  to SharePoint Online using PowerShell
#Load SharePoint CSOM Assemblies
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"

#Config Variables
$SiteURL="https://crescent.sharepoint.com/sites/marketing"
$ListName="Project Tasks"
$FilePath ="C:\Users\salaudeen\Desktop\Tasks.xlsx"

#Function to get data from Excelsheet
Function Import-Excel
{
    param ([string]$FileName)

    #Create an Object Excel.Application using Com interface
    $ExcelObject = New-Object -ComObject Excel.Application
    $ExcelObject.Visible = $false

    #Open WorkBook
    $WorkBook = $ExcelObject.Workbooks.Open($FilePath)

    #Load the First work sheet - You can use Sheet name as: $workBook.Sheets.Item("Sheet Title")
    $WorkSheet = $WorkBook.Sheets.Item(1)

    #Select the range of data used
    $Columns = $WorkSheet.UsedRange.Columns.Count
    $Rows = $WorkSheet.UsedRange.Rows.Count
    #Get Column Headers from Excelsheet as Fields in SharePoint List
    $Fields = @()
    For($Column=1; $Column -le $Columns; $Column++) 
    {
        #Get the Value from Excel Sheet
        $FieldName = $WorkSheet.Columns.Item($Column).Rows.Item(1).Text
        $Fields += $FieldName
    }

    #Get All rows from the Excelsheet
    $DataCollection =@()
    For($Row=2; $Row -le $Rows;$Row++)
    {
        $Data = New-Object PSObject
        #Iterate through columns of the row
        For($Column=1; $Column -le $Columns; $Column++)
        {
            #Get the Value from Excel Sheet
            $Value = $WorkSheet.Columns.Item($Column).Rows.Item($Row).Text

            #Frame Data
            $Data | Add-Member -MemberType noteproperty -Name $Fields[$Column-1] -Value $Value
        }
        $DataCollection +=$Data
    }

    #Close Objects
    $WorkBook.Close()
    $ExcelObject.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObject) | Out-null

    Return $DataCollection
}

Try {
    Write-host -f Yellow "Reading Data from Excel File..." -NoNewline
    #Get Data from Excel File
    $ExcelData = Import-Excel $FilePath
    Write-host -f Yellow "$($ExcelData.count) Rows Found!" 

    #Setup Credentials to connect
    $Cred = Get-Credential
 
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

    #Get the List
    $List=$Ctx.Web.Lists.GetByTitle($ListName)

    $i=1
    Foreach ($Row in $ExcelData)
    {
        Write-host -f Yellow "Adding Row $i of $($ExcelData.count)..." -NoNewline
        #Add to SharePoint Online List
        $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
        $ListItem = $List.AddItem($ListItemInfo)
        
        #Set Task Name - Title
        $ListItem["Title"] = $Row.'Task Name'
        
        #Set Start Date & Due Date Fields
        $ListItem["StartDate"] = [DateTime]::parseexact($Row.'Start Date', "dd/mm/yyyy", $null)
        $ListItem["DueDate"] =  [DateTime]::parseexact($Row.'Due Date', "dd/mm/yyyy", $null)

        #Completed Percentage
        $ListItem["PercentComplete"] = $Row.'% Complete'

        #Priority and Task status drop down values
        $ListItem["Priority"] = $Row.Priority
        $ListItem["Status"] = $Row.'Task Status'

        #Assigned To - People Picker Field
        $ListItem["AssignedTo"] = $Ctx.web.EnsureUser($Row.'Assigned To')

        $ListItem.Update()
        $Ctx.ExecuteQuery()
        Write-host -f Green "Done!"
        $i++
    }
}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}
SharePoint Online: Import Excel File Data Into Existing List using PowerShell SharePoint Online: Import Excel File Data Into Existing List using PowerShell Reviewed by Salaudeen Rajack on May 04, 2018 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.