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

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!

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

  • November 15, 2021 at 1:56 PM

    it’s also good practie to use batching.. to minimizie API requests for update, and to improve the speed of update.: something like this:

    $batch = New-PnPBatch
    1..100 | ForEach-Object{ Add-PnPListItem -List “ItemTest” -Values @{“Title”=”Test Item Batched $_”} -Batch $batch }
    Invoke-PnPBatch -Batch $batch

    Reply
  • September 7, 2021 at 8:11 PM

    Does this work for updating list item metadata fields. I need to bulk update the list items with a new Author (Created By). My CSV only has ID and Author columns. Its updating something as I see the modified date is changing but the Author is not being updated. The powershell just shows the below. Should there be something after value:

    Updating List item ID 1 with value:
    Updating List item ID 2 with value:
    Updating List item ID 3 with value:
    Updating List item ID 4 with value:
    Updating List item ID 5 with value:
    Updating List item ID 6 with value:
    Updating List item ID 7 with value:
    Updating List item ID 8 with value:
    Updating List item ID 9 with value:
    Updating List item ID 10 with value:
    Updating List item ID 11 with value:
    Updating List item ID 12 with value:
    Updating List item ID 13 with value:
    Updating List item ID 14 with value:
    Updating List item ID 15 with value:
    Updating List item ID 16 with value:
    Updating List item ID 17 with value:
    Updating List item ID 18 with value:
    Updating List item ID 19 with value:
    Updating List item ID 20 with value:

    Reply
  • 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
    • October 13, 2021 at 11:08 AM

      Yes! Instead of retrieving all fields, If you limit fields – Performance would be definitely better.

      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