Tuesday, October 1, 2013

Set SharePoint Library Columns 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, Lets write a PowerShell script to extract the required data from 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 = "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
            "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 again!

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...