SharePoint Online: Export Term Set to CSV using PowerShell
Requirement: Had to extract all terms in a term set to CSV format in SharePoint Online.
Unfortunately, there is no way to export the SharePoint Online export term set from the web browser UI. But PowerShell can help!
SharePoint Online PowerShell to Export Term Set
You probably have a few different SharePoint Online environments set up for development, testing, and other purposes. And you probably want to use the same term sets in each environment. Wouldn’t it be nice if there was an easy way to export your term set definitions from one environment and import them into another? Well, there is! In this blog post, I’ll show you how to export a term set in SharePoint Online using PnP PowerShell.
Here is the PowerShell to export all terms from a given term set in the term store of SharePoint Online:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$TermGroupName = "Regions"
$TermSetName = "East Africa"
$CSVFile="C:\Temp\TermSetData.csv"
Try {
#Get Credentials to connect
$Cred = Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
$Ctx.Credentials = $Credentials
#Get the term store
$TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx)
$TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
$Ctx.Load($TaxonomySession)
$Ctx.Load($TermStore)
$Ctx.ExecuteQuery()
#Get the Term Group
$TermGroup=$TermStore.Groups.GetByName($TermGroupName)
#Get the term set
$TermSet = $TermGroup.TermSets.GetByName($TermSetName)
#Get all tersm from the term set
$Terms = $TermSet.Terms
$Ctx.Load($Terms)
$Ctx.ExecuteQuery()
Write-Output $TermsetName > $CSVFile
#Export Terms to CSV
Foreach($Term in $Terms)
{
Write-Output $Term.Name >> $CSVFile
}
Write-host "Term Set Data Exported Successfully!" -ForegroundColor Green
}
Catch {
write-host -f Red "Error Exporting Term Set!" $_.Exception.Message
}
This script extracts each term in the given term set into a CSV file. But wait! Each term can go up to 7 levels deep! While the above script exports terms at one-single level, Here is my another PowerShell script to export terms and child terms of all levels of a given term set.
Export Term Set in SharePoint Online using PowerShell:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"
#Variables for Processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$TermGroupName = "Sectors"
$TermSetName = "Political Geography"
$CSVFile="C:\Temp\TermSet.csv"
#Custom Function get child terms of a given term
Function Get-Terms([Microsoft.SharePoint.Client.Taxonomy.Term] $Term,[String]$ParentTerm,[int] $Level)
{
$ChildTerms = $Term.Terms
$Ctx.Load($ChildTerms)
$Ctx.ExecuteQuery()
if($ParentTerm)
{
$ParentTerm = $ParentTerm + "," + $Term.Name
}
else
{
$ParentTerm = $Term.Name
}
Foreach ($SubTerm in $ChildTerms)
{
$Level = $Level + 1
#Terms may have upto 7 levels
$NumofCommas = 7 - $Level
$commas =[string]::Empty
#Append Commas
For ($j=0; $j -lt $NumofCommas; $j++)
{
$Commas = $Commas + ","
}
#Append the Output to CSV File
"," + "," + "," + $Term.IsAvailableForTagging + ",""$($Term.Description)""," + $ParentTerm + "," + $SubTerm.Name + $Commas >> $CSVFile
#Call the function recursively
Get-Terms -Term $SubTerm -ParentTerm $ParentTerm -Level $Level
}
}
Try {
#Get Credentials to connect
$Cred = Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminURL)
$Ctx.Credentials = $Credentials
#Get the term store
$TaxonomySession=[Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx)
$TermStore =$TaxonomySession.GetDefaultSiteCollectionTermStore()
$Ctx.Load($TaxonomySession)
$Ctx.Load($TermStore)
$Ctx.ExecuteQuery()
#Write Termset CSV Header (As in the standard format)
"Term Set Name,Term Set Description,LCID,Available for Tagging,Term Description,Level 1 Term,Level 2 Term,Level 3 Term,Level 4 Term,Level 5 Term,Level 6 Term,Level 7 Term" > $CSVFile
#Get the Term Group
$TermGroup=$TermStore.Groups.GetByName($TermGroupName)
#Get the term set
$TermSet = $TermGroup.TermSets.GetByName($TermSetName)
$Ctx.Load($Termset)
$Ctx.ExecuteQuery()
#Get all tersm from the term set
$Terms = $TermSet.Terms
$Ctx.Load($Terms)
$Ctx.ExecuteQuery()
#Write 2nd line as Termset properties(As per standard format)
$TermSet.Name + ",""$($TermSet.Description)""," + $TermStore.DefaultLanguage + "," + $TermSet.IsAvailableForTagging + ",""$($Terms[0].Description)""," + $Terms[0].Name + "," + "," + "," + "," + "," + "," >> $CSVFile
#Process each Term in the termset
Foreach($Term in $Terms)
{
write-host $Term.Name
Get-Terms $Term -Level 1 -ParentTerm ""
}
Write-host "Term Set Data Exported Successfully!" -ForegroundColor Green
}
Catch {
write-host -f Red "Error Exporting Term Set!" $_.Exception.Message
}
SharePoint Online: Export Term Set using PnP PowerShell
Use the Export-PnPTaxonomy cmdlet to export a term set in SharePoint Online:
#Config Variables
$AdminCenterURL = "https://crescenttech-admin.sharepoint.com"
$TermSetId="286f9c03-193d-4f23-9863-c1b7ccd2c451"
$FilePath="C:\Temp\TermsetData.txt"
#Connect to PnP Online
Connect-PnPOnline -Url $AdminCenterURL -Credentials (Get-Credential)
#Export Term set
Export-PnPTaxonomy -TermSetId $TermsetID -Path $FilePath
This PowerShell can export a term set in multi-factor authentication SharePoint Online sites too. My Term set:
and the Result:
i get Error Exporting Term Set! Exception calling “ExecuteQuery” with “0” argument(s): “The sign-in name or password does not match one in the Microsoft account system.” while executing. any help?
If you are sure about the user name and password, either Disable the MFA for the account or follow this post to use MFA enabled accounts with PowerShell: How to Connect to SharePoint Online using PowerShell with MFA Enabled Account?