Set SharePoint Library Column Value from Document File Name

Requirement:
In a SharePoint 2007 site, We’ve a document library with 1000+ documents loaded. We got a requirement to add two new fields “Year” and “Quarter ” and update those meta data field values from the document name.

Here is the screenshot of the list:

Set SharePoint List Metadata Columns from its File Name

Solution:

PowerShell script! We analyzed that the Year & Quarter values are separated by “__”. So, Let’s write a PowerShell script to extract the required data from the document name and set the columns programmatically.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

#Using Get-SPSite in MOSS 2007
function global:Get-SPSite($url)
 {
    return new-Object Microsoft.SharePoint.SPSite($url)
 }

Function global:Get-SPWeb($url)
{
  $site= New-Object Microsoft.SharePoint.SPSite($url)
        if($site -ne $null)
            {
               $web=$site.OpenWeb();
       
            }
    return $web
}

$url = "https://sharepoint.crescent.com/finance/ARL/"
$ListName ="ARL Archive"

#Get the Web
$web = Get-SPWeb $URL
#Get the List
$list = $web.Lists[$ListName]

#Get All List Items
$ListItems = $list.Items

#Iterate through each item
 foreach($item in $listItems)
  {
     $FileName = $item["Name"]
   
     #Get the Year from File name  # Year is located after __ from the start
     $YearIndex = $FileName.IndexOf("__") + 2 
     $YearValue  = $FileName.Substring($YearIndex,4)
     write-host $YearValue
   
     #Get Month from File name # Month is located before __ from the end
     $MonthIndex = $FileName.LastIndexOf("__")
     $MonthValue= $Month = $FileName.Substring($YearIndex+5,$MonthIndex-$YearIndex-5) 
     write-host $MonthValue
    
     #Set the Quarter based on Month
        Switch($MonthValue)
        {
            "March" { $QuarterValue = "Q1" }
            "June" { $QuarterValue = "Q2" }
            "September" { $QuarterValue = "Q3" }
            "December" { $QuarterValue = "Q4" }
        }
   
     #Set the Field values
     $Item["Year"]  = $YearValue
     $Item["Quarter"]  = $QuarterValue
     $Item.Update()  
  } 

I Love PowerShell!

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *