Set SharePoint Library Column Value from Document File Name

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


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.


#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)
    return $web

$url = ""
$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
            "March" { $QuarterValue = "Q1" }
            "June" { $QuarterValue = "Q2" }
            "September" { $QuarterValue = "Q3" }
            "December" { $QuarterValue = "Q4" }
     #Set the Field values
     $Item["Year"]  = $YearValue
     $Item["Quarter"]  = $QuarterValue

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 *