Monday, March 30, 2015

Export Term Set to Excel - SharePoint Metadata Services - Powershell Script

# Add SharePoint PowerShell Snapin
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

 # File and Directory Location
$dirLocation = "D:\temp\TermStore\"
$date = get-date -Format yyyyMMdd
New-Item ($dirLocation + $date) -Type Directory | Out-Null
$file = New-Object System.IO.StreamWriter(($dirLocation + $date) + "\Terms.csv")

# Connect to site with MMS service connection
$taxonomySite = Get-SPSite -Limit 1
 
# Connect to Term Store in the Managed Metadata Service Application
$taxonomySession = Get-SPTaxonomySession -site $taxonomySite
$taxonomyTermStore =  $taxonomySession.TermStores | Select Name
$termStore = $taxonomySession.TermStores[$taxonomyTermStore.Name]

# Ampersands are stored as full width ampersands within the MMS database.
[Byte[]] $amp = 0xEF,0xBC,0x86

# CSV headers
$file.Writeline("Term Name,Id,Owner,CreatedDate,LastModifiedDate,TermSet,TermSetOpenStatus,GroupName")

# Term counter
$i = 0

foreach ($group in $termStore.Groups) {
    foreach ($termSet in $group.TermSets) {
        foreach ($term in $termSet.GetAllTerms()) {
            [Byte[]] $amp = 0xEF,0xBC,0x86;
            $file.Writeline("""" + $term.Name.Replace([System.Text.Encoding]::UTF8.GetString($amp), "&") + """" + "," + $term.Id + "," + $term.Owner + "," + $term.CreatedDate + "," + $term.LastModifiedDate + "," + $term.TermSet.Name + "," + $term.TermSet.IsOpenForTermCreation + "," + $term.TermSet.Group.Name);
            $i++
            Write-Host -ForegroundColor Cyan  "# Exporting TermSet: " -NoNewline
            Write-Host -ForegroundColor White $termSet.Name -NoNewline
            Write-Host -ForegroundColor Cyan  " Term: " -NoNewline
            Write-Host -ForegroundColor White $term.Name -NoNewline
            Write-Host -ForegroundColor Green " - Done"        
            }          
        }              
    }
$file.Flush()
$file.Close()

Write-Host
Write-Host -ForegroundColor Cyan  "# Exported " -NoNewline
Write-Host -ForegroundColor Green  $i -NoNewline
Write-Host -ForegroundColor Cyan  " terms"

No comments: