SharePoint Online: Update List Items from a CSV File using PowerShell

Requirement: Update SharePoint Online List from a CSV using PowerShell.

PowerShell to Update SharePoint Online List from CSV File

We have a list called “Projects” with a bunch of columns and wanted to update items from a CSV file. Here is the PowerShell CSOM script to update the SharePoint Online list from CSV:

update sharepoint list from csv

PowerShell Script to Update List Items from CSV:

Set parameters in the script, make sure CSV columns are mapped with the respective field internal name with your SharePoint Online list, and then run the script:

#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"

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
$CSVFile = "C:\Temp\ListData.csv"

#Setup Credentials to connect
$Cred = Get-Credential
 
Try {
    #Get the data from CSV file
    $CSVData = Import-CSV $CSVFile

    #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)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()

    #Loop through each Row in the CSV file and update the matching list item based on "ID"
    ForEach($Row in $CSVData)
    {
        Try {
            #Get list item to update
            $ListItem = $List.GetItemById($Row.ID)
            $Ctx.Load($ListItem)
            $Ctx.ExecuteQuery()

            #Update List Item
            $ListItem["ProjectName"] = $Row.'Project Name'
            $ListItem["IsActive"] = $Row.'Is Active'
            $ListItem["Start_x0020_Date"] = $Row.'Start Date'
            $ListItem["Status"] = $Row.'Status'
            $ListItem.Update()
            Write-host "Updated List Item:"$Row.ID
        }
        Catch {
            write-host "Error Updating Item $($Row.ID): $($_.Exception.InnerException.Message)" -foregroundcolor Yellow
        }
    }
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

This script works for simple field types such as single line of text, Multiple lines of text, Date and Time, Yes/No, etc. What if you have a very large list with complex field types such as Person or Group, Hyperlink or Picture, Managed Metadata, Lookup and you have to update the list items from a CSV using PowerShell. Here is my list to update:

update sharepoint online list items from csv

List structure:

update sharepoint online list from csv

My CSV File with data to update

powershell update sharepoint online list from csv

You can download this CSV file here:

PowerShell script to Update List Items from CSV:

Just set the parameters, make sure the data in the right format as in the CSV file. List items are updated with the matching ID field value from the given CSV file.

#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/Projects"
$ListName = "Projects"
$CSVFilePath = "C:\Temp\ProjectData.csv"
 
#Function to get Lookup ID from Lookup Value
Function Get-LookupID($ListName, $LookupFieldName, $LookupValue)
{
    #Get Parent Lookup List and Field from Child Lookup Field's Schema XML
    $LookupField =  Get-PnPField -List $ListName -Identity $LookupFieldName
    [Xml]$Schema = $LookupField.SchemaXml
    $ParentListID = $Schema.Field.Attributes["List"].'#text'
    $ParentField  = $Schema.field.Attributes["ShowField"].'#text'
    $ParentLookupItem  = Get-PnPListItem -List $ParentListID -Fields $ParentField | Where {$_[$ParentField] -eq $LookupValue} | Select -First 1
  
    If($ParentLookupItem -ne $Null)
    {
        #Get the Parent Item ID
        Return $ParentLookupItem["ID"]
    }
    Else
    {
        Return $Null
    }
}
 
Try {
    #Connect to the Site
    Connect-PnPOnline -URL $SiteURL -UseWebLogin
 
    #Get the data from CSV file
    $CSVData = Import-CSV $CSVFilePath
 
    #Get the List
    $List = Get-PnPList -Identity $ListName
     
    #Get fields to Update from the List - Skip Read only, hidden fields, content type and attachments
    $ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne  "ContentType") -and ($_.InternalName -ne  "Attachments") }
      
    #Loop through each Row in the CSV file and update the matching list item ID
    ForEach($Row in $CSVData)
    {
        #Get the List Item to update
        $ListItem = Get-PnPListItem -List $List -Id $Row.ID -ErrorAction SilentlyContinue
        If($ListItem -ne $Null)
        {
            $ItemValue = @{}            
            $CSVFields = $Row | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name
            #Map each field from CSV to target list
            Foreach($CSVField in $CSVFields)
            {
                $MappedField = $ListFields | Where {$_.InternalName -eq $CSVField}
                If($MappedField -ne $Null)
                {
                    $FieldName = $MappedField.InternalName
                    #Check if the Field value is not Null
                    If($Row.$CSVField -ne $Null)
                    {
                        #Handle Special Fields
                        $FieldType  = $MappedField.TypeAsString 
                        If($FieldType -eq "User" -or $FieldType -eq "UserMulti") #People Picker Field
                        {
                            $PeoplePickerValues = $Row.$FieldName.Split(",")
                            $ItemValue.add($FieldName,$PeoplePickerValues)
                        }
                        ElseIf($FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") #Lookup Field
                        {
                            $LookupIDs = $Row.$FieldName.Split(",") | ForEach-Object { Get-LookupID -ListName $ListName -LookupFieldName $FieldName -LookupValue $_ }                
                            $ItemValue.Add($FieldName,$LookupIDs)
                        }
                        Else
                        {
                            #Get Source Field Value and add to Hashtable
                            $ItemValue.Add($FieldName,$Row.$FieldName)
                        }
                    }
                }
            }
            Write-host "Updating List item ID $($Row.ID) with value:"
            $ItemValue | Format-Table
            #Update List Item value
            Set-PnPListItem -List $ListName -Identity $Row.ID -Values $ItemValue | Out-Null
        }
        Else
        {
            Write-host "Could not find item ID $($Row.ID) in the list, Skipped!" -f Yellow
        }
    }
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}
Note: Make sure your CSV column names are exactly matching with the list column internal names.

Please note, this script updates existing list items from a given CSV file. If you want to import (add new items) from a CSV file, use: SharePoint Online: Import CSV File into SharePoint List using PowerShell

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

4 thoughts on “SharePoint Online: Update List Items from a CSV File using PowerShell

  • April 30, 2021 at 7:37 PM

    I rewrote this to do what I want. I did find it was extremely slow because line 44 is getting only a single list item at a time from SharePoint Online. That results in an API call for each item. To improve performance I moved that line up out of the array and assigned the result to $ListItems like this:

    $ListItems = Get-PnPListItem $List -Fields $Fields

    I then used $ListItems in the array instead on line 44. That resulted in much faster performance and fewer API calls to SharePoint.

    Reply
  • April 21, 2021 at 1:56 PM

    I’m looking for the same thing. Did you ever find a solution?

    Reply
  • January 12, 2021 at 3:33 PM

    Great article, my csv has a few fields like date and email id’s as blanks, it errors out when i run the above script. How to assign $null in add().

    Reply
  • December 12, 2020 at 2:08 AM

    Is there a way to synchronize from csv to sharepoint online so that any new items/or changes found in csv will be reflected in the sharepoint online list?

    Reply

Leave a Reply