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 - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

Leave a Reply

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