How can I bulk update what it should me and replace some

Question

I have a mixture of old and new Metadata marked in SharePoint to files in different folders and documents.

Answer 1

Hi @Abri Theart,

To bulk update managed metadata applied to files/folders, 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.

1. PowerShell to Get All Files from a Document Library to CSV

      #Parameters
      $SiteURL = "https://xxx.sharepoint.com/sites/xxx"
      $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.
2. 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.

      #Parameters
      $SiteURL = "https://xxx.sharepoint.com/sites/xxx"
      $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.

---

If the answer is helpful, please click "Accept as Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.