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

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

SharePoint Online: Import Excel to Existing List using PowerShell

Are you looking for a way to import an Excel spreadsheet into a SharePoint Online list regularly? If so, PowerShell can be a great option. In this article, I’ll show you how to use PowerShell to import data from Excel into a SharePoint Online list.

While my other post describes How to Import data from a CSV file to a SharePoint Online List?, this script targets importing the XSLX file into an existing SharePoint Online list.

Import Excel File to SharePoint Online List using PowerShell

Please make sure the data is in the right format! E.g., The date column should be in “dd/mm/yyyy” format if your regional setting is UK standards.

PowerShell to Import Excel Data into Existing SharePoint Online List

Let’s import the 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
}

Be sure to change the parameters and name of the Excel Sheet before running the script.

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

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

  • 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
  • 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
  • Can you please tell me how to work with lookup column in this case.

    Reply
  • 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

Your email address will not be published. Required fields are marked *