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 SharePoint Online 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:

List structure:

My CSV File with data to update:

You can download this CSV file here: CSV to Update SharePoint Online List

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