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
Metadata in SharePoint Online files helps to classify, categorize, and manage the files more efficiently. 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:
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.
Conclusion
In conclusion, bulk updating metadata in SharePoint Online can be helpful for organizing and managing your files more effectively. By making changes to the metadata of multiple files at the same time, you can save time and streamline your workflow. Although, there are a variety of 3rd party tools you can use to bulk update metadata in SharePoint Online, we have used the built-in features of CSV and PowerShell script to update the metadata of multiple files. By following the steps outlined in this guide, you can mass update metadata for your files and improve your overall productivity.
Hi Salaudeen, nice guide.
Why does Connect-PnPOnline – interactive prompt me to grant seemingly ALL permissions ?
As soon as I run “Connect-PnPOnline $SiteURL -Interactive” it opens up a browser window and asks me to request approval for these below permissions? (I haven’t posted all of them)
Create, read, update, and delete your tasks and task lists
Read all transcripts of online meetings.
Access the PowerApps Service API
Access Common Data Service as you
View all datasets
Read activity data for your organization
Read service health information for your organization
Read and write managed metadata
Have full control of all site collections
Read and write user profiles
Hello there,
Do you have a version of this script that updates the metadata of folders instead of files?
Any suggestions would be appreciated.
Thanks,
Dan
Hi There,
I was wondering if I could use this script to update the metadata on a folder instead of a file.
Would it just be a matter of replacing the file name in the csv to the folder and changing the get-pnpfile to get-pnpfolder? Any suggestion would be appreciated.
Hi! A quick question:
Based on your script how would you update a Managed Metadata Field with the hashtable ?
In which format do you have to save to value to the key? I’m guessing you would use the field’s internal name as key, but what would be the value?
Hi Salaudeen, thanks for the script.
However, I have one problem.
I would like to update a MultiChoice field with several values
Is this possible?
Many thanks, Kevin
Yes! Just separate the choices with “;#” characters. Here is how: How to Update Choice Field Values in SharePoint Online using PowerShell?
How do you write that in the excel file, i dont get the multicoice to work
Many thanks Per
For Multiple choices, separate them with “;#” (semicolon and # symbol). E.g.,
“Aerospace;#Automation;#Energy”.
More here: How to update Multiple Choices Field value in SharePoint Online using PowerShell?
I saw that but if you for exameple as in you excel sheet has a row PMO|Classification|IT and PMO|Classification|HR that should be a multivalue how do you specify that?
Regards
Per
Wrap them in double-quotes: E.g., “PMO|Classification|IT”,”PMO|Classification|HR”
@Salaudeen – For some reason wrapping the multiple term values in quotes only causes the overlay to cancel with the following message: “WARNING: Unable to find the specified term. Skipping values for field ‘Department’.”
I have experimented with various delimiters and have had no luck.
The following only fails when the CSV for “Department” field is setup in the following way:
“People|Department|Manager”,”People|Department|Director”
The following works fine if run individually, but this will not allow me to multi-select for the two terms:
People|Department|Director
People|Department|Manager
Thank you for this fantastic resource. Your input would be greatly appreciated!
-=Adam
Please refer: How to update MMS Field values in SharePoint Online using PowerShell?
Many thanks – that works
Hello!
Can’t understand what’s wrong in the code.
Getting next eror:
Get-PnPField: C:\Scripts\svlibmetadata.ps1:33
Line |
33 | $ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.Re …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The remote server returned an error: (400) Bad Request.
Could not find File at /sites/svlib/Shared%20Documents/sv_3dss-1.33_install-config-guide__en_core_20220530.pdf , Skipped!
Could not find File at /sites/svlib/Shared%20Documents/sv_ip-3.0_admin-guide__en_core_20220527.pdf , Skipped!
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@company.com
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:
I am also having this issue and would like to know if there is a fix.
Thank you
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
Hi, I had the same issue until I replace ; with , as a delimiter in the CSV file. Cheers, Sander.
Hi how I can use to tag a metadata to the respective file when I upload it using Add-PnPFile ? In Powershell ?
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!
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.
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
Great! Script works like a charm!
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.
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..