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 other post describes importing data from a CSV file to SharePoint Online list, this script targeting importing the .XSLX file into existing SharePoint Online list.

import excel data into existing sharepoint online list using powershell

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

Let’s 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 [email protected]()
    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
}

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

5 thoughts on “SharePoint Online: Import Excel File Data Into Existing List using PowerShell

  • August 24, 2021 at 2:06 AM

    I’m getting this error not sure why.

    Reading Data from Excel File…Error: You cannot call a method on a null-valued expression.

    Reply
  • January 8, 2021 at 3:25 PM

    I am getting this error while running the script.
    My Source is Sharepoint Library file path and I have changes the parameter to mm/dd/yyyy format
    “.Error: Exception calling “ParseExact” with “3” argument(s): “String was not recognized as a valid DateTime.”

    Reply
  • September 1, 2020 at 10:53 AM

    Can you please tell me how to work with lookup column in this case.

    Reply
  • March 18, 2020 at 11:40 AM

    I have tried this but I am getting Error: Cannot bind argument to parameter ‘Name’ because it is an empty string.

    I am a bit confused by this because I can’t see where “Name” is being used as a parameter? Can you help please? Thanks!

    Reply

Leave a Reply