Export MetaData Terms

Central administration provides a way to import metadata term sets and terms by uploading a CSV file. However, it does not provide a mechanism for exporting terms to a CSV.

There are quite a lot of PowerShell and command line utilities on the web for accomplishing this, however, the ones that I found did not cater for terms that are more than one level deep. After all, you can create a term hierarchy that is up to seven levels deep.

So, to help fill this gap I’ve written a simple PowerShell script that does exactly this.

One caveat with importing CSV files through Central Administration is that it can only import one term set per CSV. So when exporting more than one set you have to create a separate file for each of them. You’d have thought the import script would have been able to handle multiple sets…

The PowerShell script that I’ve written allows you to specify a single term group to export. It will be easy to modify to export all (or a specific set of) term groups if required.

I’ll provide a link to the complete script at the end of this post, so won’t show the entire script here, just the main functions.

function Export-SPTerms {
  param (
        [string]$siteUrl = $(Read-Host -prompt "Please provide the site collection URL"),
        [string]$termGroupName = $(Read-Host -prompt "Please provide the term group name to export"),
        [string]$saveLocation = $(Read-Host -prompt "Please provide the path of the folder to save the CSV file to")
  )
 
  if ([IO.Directory]::Exists($saveLocation) -eq $false)
  {
    New-Item ($saveLocation) -Type Directory | Out-Null
  }
 
  $taxonomySession = Get-SPTaxonomySession -site $siteUrl
  $taxonomyTermStore =  $taxonomySession.TermStores | Select Name
  $termStore = $taxonomySession.TermStores[$taxonomyTermStore.Name]
 
  # Ampersands are stored as fullwidth ampersands (see http://www.fileformat.info/info/unicode/char/ff06/index.htm)
  [Byte[]] $amp = 0xEF,0xBC,0x86
 
  foreach ($group in $termStore.Groups) {
    if ($group.Name -eq $termGroupName) {
      foreach ($termSet in $group.TermSets) {
        # Remove unsafe file system characters from filename
        $parsedFilename =  [regex]::replace($termSet.Name, "[^a-zA-Z0-9\\-]", "_")
        $file = New-Object System.IO.StreamWriter($saveLocation + "\termset_" + $parsedFilename + ".csv")
 
        # Write out the headers
        $file.Writeline("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")
 
        try {
          Export-SPTermSet $termSet.Terms
        }
        finally {
          $file.Flush()
          $file.Close()
        }
      }
    }
  }
}

This function accepts various parameters for configuring which term store group to export and the location to save the CSV file(s).

Next, it iterates through the top level term sets for the specified group and generates a CSV file to store the terms for the current term set. It also adds the headers to the first row. As you can see, it adds seven columns for storing up to the maximum allowable number of term levels.

Lastly, this function calls the Export-SPTermSet function which will export the term set’s terms to the CSV file.

function Export-SPTermSet {
  param (
        [Microsoft.SharePoint.Taxonomy.TermCollection]$terms,
		[int]$level = 1,
		[string]$previousTerms = ""
  )
 
  if ($level -ge 1 -or $level -le 7) {
    if ($terms.Count -gt 0 ) {
      $termSetName = ""
      if ($level -eq 1) {
        $termSetName =  """" + $terms[0].TermSet.Name.Replace([System.Text.Encoding]::UTF8.GetString($amp), "&") + """"
      }
 
      $terms | ForEach-Object {
        $currentTerms = $previousTerms + ",""" + $_.Name.Replace([System.Text.Encoding]::UTF8.GetString($amp), "&") + """";
 
        $file.Writeline($termSetName +
                        ",""" + $_.TermSet.Description + """" + 
                        ",," + $_.IsAvailableForTagging +
                        "," + $_.GetDescription() + $currentTerms);
 
        if ($level -lt 7) {
          Export-SPTermSet $_.Terms ($level + 1) ($previousTerms + $currentTerms)
        }
      }
    }
  }
}

This function recursively calls itself up to seven times, depending on how far down the hierarchy the terms go for a particular term set.

As promised, here’s a link to the complete script, which works for both SharePoint 2010 and 2013. For 2013, make a small change to the script so the SharePoint assembly being referenced is version 15.0.0.0 instead of 14.0.0.0.

13 comments

  1. Tor I. says:

    Really nice to have when you have multiple farms which will share terms, or you want to “version control” the terms. Strange that Microsoft added an import funtion but no export function themselves though.

  2. Nice shell script, I used this for SP 2013 by small modification inside it. Just changed the version of Microsoft.SharePoint assembly version to 15.0.0.0

  3. Nate says:

    Very nice, saved me a lot of time.

  4. Can says:

    I get error “The given guid does not exist in the term store” when I want use TermStore programatically.

  5. Gyon says:

    Hello Stu,

    Great job. Anyway,
    the script exported only 1 only level term sets.
    Do you know why?
    Thanks

    • Gyon says:

      I see it wasn’t imported because the term set was used for navigation and not beacuse of the levels. Anyhow, the sctipt imports only one level.

      • Stu says:

        Hi Gyon,

        Can you send me a screenshot of your expanded terms in the managed metadata service app?

        Curious why you only get one level of terms as I’ve ran this on various configurations and have always got all of the terms exported.

        Did you download the script or create your own based on the scripts shown on the page?

        • Gyon says:

          Did you make it working even with navigation term sets? I have tried with another term set (flagged as Managed Navigation: Term Set in the navigation menu) and got the same problem . Anyway, your script at the end worked. I only had to copy the term set that wasn’t working. The new copy was correctly imported with all subterms. So thanks

  6. Very nice article. I definitely appreciate this site. Thanks!

  7. srinivas says:

    Hi Stu,

    Thanks, It’s Nice article but when we export the terms in csv, it is exporting but its not inserting the “,” when a particaular level contains blank value.

    please let me know how to insert the “,” when a particulat level of term contains blank value every csv file should contain header values and structure , because i am using this csv files to import into other farm, which importing through power shell script file

    Regards,
    Srinivas.S

  8. Vishal says:

    Thanks Stu. Very Nice & time saving script.

  9. Nathan Dunlap says:

    Very nice script! This provided exactly what I needed. Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the maths problem shown below before posting: *

Follow

Get every new post delivered to your Inbox

Join other followers: