Sometimes
you find yourself in a situation where you need to read the metadata column,
which contains multiple values. These values are sometimes hard to read, and
contain a lot of extra information (that you may or may not need) that needs to
be stripped, to make the report readable. The string may look like this:
10;#Process|c6a2e0b3-0ac7-41d3-a0d4-bfca9d113c2f;#35;#Report|ea772779-c5ca-4992-a338-ff686479032f;#31;#Policy|523d8c9b-23b7-4746-96f2-a497f37012fb
But what
you really want is Process;Report;Policy which is stripped from the column
value. Starting in the beginning lets add the PSSnapin to our PowerShell if it’s
not loaded.
$snapin =
Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
if ($snapin
-eq $null)
{
Write-Host "Loading SharePoint
Powershell Snapin"
Add-PSSnapin
"Microsoft.SharePoint.Powershell"
-EA SilentlyContinue
}
Hook up to
your web and iterate yourself down to the metadata column.
$url = "https://intranet.company.com/document-center/"
$web =
Get-SPWeb $url
foreach($list
in $web.Lists)
{
if($list.BaseType -eq
"DocumentLibrary")
{
foreach($item in $list.items)
{
$file = $item.File
Having an
instance of the file, makes us check whether or not the metadata column has any
value. If it has any value, let’s clean it from the ugly output
# Subject
if($file.Properties["Subject"]
-ne "")
{
$Subject = $file.Properties["Subject"]
if($Subject -like '*#*')
{
At first,
we need to find out how many occurrence’s we have of the hash-character through
the following script
$SubjectSplit =
""
$char = "#"
$result =
0..($Subject.length -1) | ? {$Subject[$_] -eq $char}
Calling $result.count
will give us the number of times “#” is used in the string. When we know that,
we can iterate over the string and pull the information we need. We’re
separating terms by semi colon “;” which unfortunately leaves one extra “;” in
the end
for($i=1; $i -le
$result.count;$i=$i+2)
{
$SubjectSplit =
$SubjectSplit + $Subject.split("#")[$i].split("|")[0] +
";"
}
But it’s
quite easy to remove
$Subject =
$SubjectSplit.TrimEnd(";")
And finally
write the output to the host, so you know you got it right before putting the
information in a csv-file.
}
}
Write-Host $Subject
}
}
}
$web.Dispose();
The
complete script:
$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null)
{
Write-Host "Loading SharePoint Powershell Snapin"
Add-PSSnapin "Microsoft.SharePoint.Powershell" -EA SilentlyContinue
}
$url = "https://intranet.company.com/document-center/"
$web = Get-SPWeb $url
foreach($list in $web.Lists)
{
if($list.BaseType -eq "DocumentLibrary")
{
foreach($item in $list.items)
{
$file = $item.File
# Subject
if($file.Properties["Subject"] -ne "")
{
$Subject = $file.Properties["Subject"]
if($Subject -like '*#*')
{
$SubjectSplit = ""
$char = "#"
$result = 0..($Subject.length -1) | ? {$Subject[$_] -eq $char}
for($i=1; $i -le $result.count;$i=$i+2)
{
$SubjectSplit = $SubjectSplit + $Subject.split("#")[$i].split("|")[0] + ";"
}
$Subject = $SubjectSplit.TrimEnd(";")
}
}
Write-Host $Subject
}
}
}
$web.Dispose();
