SharePoint Online: Bulk Update Metadata Properties from a CSV File using PowerShell

Requirement: Bulk Update Metadata of Multiple Documents from a CSV in SharePoint Online.

SharePoint Online: PowerShell to Bulk Edit Metadata Properties from a CSV File

We have a document library with 100s of documents in several sub-folders and wanted to bulk update the metadata of all those documents. In comparison, the quick edit is one option, but it’s cumbersome to navigate through each folder and update the properties of each document.

The solution we arrived at is to prepare a CSV file with a list of files to update and populate the CSV with required metadata and use PowerShell script to bulk edit metadata of all documents from the CSV file.

PowerShell to Get All Files from a Document Library to CSV

Let’s generate document inventory from all folders and sub-folders of a given SharePoint Online document library:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName= "Branding"
$CSVOutput = "C:\Temp\DocInventory.csv"

#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -Interactive
 
#Get all Files from the document library
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000 -Fields "FileLeafRef" | Where {$_.FileSystemObjectType -eq "File"}

#Iterate through each item
$DocumentsList = @()
Foreach ($Item in $ListItems) 
{
    #Extract File Name and URL
    $DocumentsList += New-Object PSObject -Property ([ordered]@{
        FileName          = $Item.FieldValues.FileLeafRef
        ServerRelativeURL = $Item.FieldValues.FileRef
    })
}
#Export the results
$DocumentsList | Export-Csv -Path $CSVOutput -NoTypeInformation

This script generates a CSV file with “FileName” and “ServerRelativeURL” columns of all files from a given document library.

PowerShell to Bulk Update Metadata in SharePoint Online

Once we have the files list ready, populate the CSV file with relevant metadata and use this script to update. This script uses “ServerRelativePath” as the key to getting files and updates. Here is my CSV file:

bulk update document properties using powershell

You can download the CSV from:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName= "Branding"
$CSVFile = "C:\Temp\DocMetadata.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)  { Return $ParentLookupItem["ID"] }  Else  { Return $Null }
}

Try {
    #Connect to SharePoint Online site
    Connect-PnPOnline $SiteURL -Interactive

    #Get the data from CSV file
    $CSVData = Import-CSV $CSVFile

    #Get fields to Update from the List - Skip Read-only, hidden fields and content type
    $ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne  "ContentType") }

    #Loop through each Row in the CSV file and update metadata
    ForEach($Row in $CSVData)
    {
        #Get All columns from CSV - Exclude "FileName" and "ServerRelativeURL" Columns
        $CSVFields = $Row | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name | Where {$_.Name -notin ("FileName","ServerRelativeURL")}

        #Get the File to update
        $File = Get-PnPFile -Url $Row.ServerRelativeURL -AsListItem -ErrorAction SilentlyContinue
        If($File -ne $Null)
        {
            #Frame the Metadata to update
            $ItemValue = @{}

            #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)
                        }
                    }
                }
            }
            #Update document properties
            Write-host "Updating Metadata of the File '$($Row.FileName)' at '$($Row.ServerRelativeURL)' with values:"
            $ItemValue | Format-Table
            Set-PnPListItem -List $ListName -Identity $File.Id -Values $ItemValue | Out-Null
        }
        Else
        {
            Write-Host "Could not find File at $($Row.ServerRelativeURL)' , Skipped!" -f Yellow
        }
    }
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Make sure your CSV column headers match the internal names (not the display names!) of the metadata columns in your document library. Furthermore, verify the metadata format entered in your CSV file once. (E.g. If you supply invalid metadata, the update will not take place – Obviously!) E.g., For MMS fields, the format should be TermGroup|TermSet|Term.

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: Bulk Update Metadata Properties from a CSV File using PowerShell

  • Hi – I need to update the Created By (Author) field on 2500 lines on a SharePoint Online list. I need to change from LastName, First Name format to email address. I am migrating a list from on-prem to SharePoint Online that allows a user to view only those items that they created. But since moving to Office 365 the usernames are different and therefore nobody can see their list items.

    I can update one list item using this link and specifying the ID of the item but this will take forever.
    https://www.sharepointdiary.com/2016/11/update-created-by-modified-by-created-at-modified-at-values-using-powershell.html

    I also tried the above guide and had 3 columns in my csv, FileName, ServerRelativePath and Author like below:

    FileName ServerRelativeURL Author
    1_.000 /sites/EngineeringDevelopment/Lists/CPD_O365/1_.000 [email protected]

    Looks like its updating the list item but not the Author – not sure what’s being updated. It shows the below line on all 2500 lines in powershell – should I be seeing the updated value after values: below

    Updating Metadata of the File ‘2538_.000’ at ‘/sites/EngineeringDevelopment/Lists/CPD_O365/2538_.000’ with values:

    Reply
  • I am also having this issue and would like to know if there is a fix.

    Thank you

    Reply
  • Hi, the script looks very promising. However when I use it I get the below error. Any ideas how to fix this? Thanks. Cheers, Sander.
    Set-PnPListItem : Invalid request.

    At C:tempImportMetaDataProperties.ps1:74 char:13

    Set-PnPListItem -List $ListName -Identity $File.Id -Value …

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    CategoryInfo : WriteError: (:) [Set-PnPListItem], ServerException

    FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.SetListItem

    Reply
  • Hi, I had the same issue until I replace ; with , as a delimiter in the CSV file. Cheers, Sander.

    Reply
  • Hi how I can use to tag a metadata to the respective file when I upload it using Add-PnPFile ? In Powershell ?

    Reply
  • Hey! Do you have a pnp powershell to update Title from Filename (with no file extension) in SharePoint Online document library so that the name and title columns will have the same value across all files in the document library. Thanks a lot!

    Reply
  • I have a metadata.csv file with columns where there is no data for a few rows. The code errors out if a field is blank. How to modify the code to accept blank fields.

    Reply
  • I am unable to maintain modified date and modified by as -systemupdate is not worming with Set-PnPListItem -List $ListName -Identity $file1.ID -Values @{“RetentionDate” = $RetentionDate} -SystemUpdate any idea

    Reply
  • Great! Script works like a charm!

    Reply
  • Hi, Thank you for this script!
    I am getting the following error: Get-PnPFile : Cannot bind argument to parameter ‘Url’ because it is null.
    My CSV file does have urls’s under the ServerRelativeURL column.
    Please assist.

    Reply
    • does your CSV file has ‘ServerRelativeURL’ field with valid server relative URLs? E.g. /sites/sales/docs/document.docx? “-ErrorAction SilentlyContinue” should suppress the issue and pass-on..

      Reply

Leave a Reply