Make metadata column containing multiple terms readable with PowerShell

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();