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
$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
    [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

    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')

        Write-host -f Green "Done!"
Catch {
    write-host -f Red "Error:" $_.Exception.Message


  1. 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!

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

  3. 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."


Please Login and comment to get your questions answered!

Powered by Blogger.