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:
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 = "http://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!
