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've a document library with a 100s of documents in several sub-folders and wanted to bulk update metadata of all those documents. While quick edit is one option, its really cumbersome to navigate through each folder and update properties of each document.
The solution we arrived is to prepare a CSV file with 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 -UseWebLogin #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, populated the file with relevant metadata and used this script to update. This script uses "ServerRelativePath" as the key to get files and update. Here is my CSV file:
You can download the CSV from CSV to Bulk Update metadata in SharePoint Online
#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 -UseWebLogin #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 are matching the internal names (not the display names!) of the metadata columns in your document library. Also, verify the format of the metadata entered in your CSV file once. (E.g. If you supply invalid metadata, then the update would not take place - Obviously!) E.g. For MMS fields, the format should be: TermGroup|TermSet|Term.
Hi, Thank you for this script!
ReplyDeleteI 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.
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..
DeleteGreat! Script works like a charm!
ReplyDeleteI 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
ReplyDeleteI 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.
ReplyDeleteHey! 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!
ReplyDeleteHi how I can use to tag a metadata to the respective file when I upload it using Add-PnPFile ? In Powershell ?
ReplyDelete