theme-sticky-logo-alt

Importing Managed Metadata TermStore from .csv using PowerShell

12 Comments

During, a recent project I had to create Term Stores automatically by using PowerShell.  In SharePoint you can save TermStores in .csv files and upload them with the GUI, so If it can be done with the GUI, there’s always a way to do it in PowerShell.   After some searches on Google, I found the TechNet article (http://technet.microsoft.com/en-us/library/ee424396.aspx) that explains the format in which the .csv file must be. However a small mistake can mess everything up.

Luckily, I found this great Excel Macro on the Web done by Wictor Wilén.  

When you’re done writing all your metadata, just click the “Create Term Store File”.  The excel macro does it for you in a .txt. However, I changed the extension to .csv to make sure that it works well with SharePoint.

Now, here is the code to import it into SharePoint.

function ImportTermSet([Microsoft.SharePoint.Taxonomy.TermStore]$store, [string]$groupName, [PSCustomObject]$termSet) {  
  function ImportTerm([Microsoft.SharePoint.Taxonomy.Group]$group, 
                      [Microsoft.SharePoint.Taxonomy.TermSet]$set, 
                      [Microsoft.SharePoint.Taxonomy.Term]$parent, 
                      [string[]]$path) {        
    if ($path.Length -eq 0) {
      return
    } elseif ($group -eq $null) {
      $group = $store.Groups | where { $_.Name -eq $path[0] }
      if ($group -eq $null) {
        $group = $store.CreateGroup($path[0])
      }
    } elseif ($set -eq $null) {
      $set = $group.TermSets | where { $_.Name -eq $path[0] }
      if ($set -eq $null) {
 Write-Host “Create $path[0]”
        $set = $group.CreateTermSet($path[0])
Write-Host “Created $path[0]”
      }
    } else {
      $node = if ($parent -eq $null) { $set } else { $parent }
      $parent = $node.Terms | where { $_.Name -eq $path[0] }       
      if ($parent -eq $null) {
        $parent = $node.CreateTerm($path[0], 1033)
      } 
    }
    
    ImportTerm $group $set $parent $path[1..($path.Length)]                                     
  }
  

  $termSetName = $termSet[0].”Term Set Name”    
  $termSet | where { $_.”Level 1 Term” -ne “” } | foreach {
    $path = @($groupName, $termSetName) + @(for ($i = 1; $i -le 7; $i++) { 
      $term = $_.”Level $i Term”
        if ($term -eq “”) {
          break
        } else {
          $term
        }
      }
    )
        
    ImportTerm -path $path
  }
}


$url = “http://vlad.test.loc”



$session = Get-SPTaxonomySession -Site $url
$store = $session.TermStores[“Managed Metadata Service”]   
$termSet = Import-Csv “C:Vladmetameta1.csv”
ImportTermSet $store “vladcatrinescublogtermstore” $termSet
$store.CommitAll()

Start-Sleep -Seconds 10; 
Say Thanks if it helped. 

Previous Post
Unable to load assembly group. The user assembly group provider threw an exception while trying to provide user assemblies for the specified assembly group.
Next Post
Changing the CSS Style of a SharePoint Site

12 Comments

  • July 25, 2012 at 12:52 pm

    Thanks !

    How is it possible to get synonyms ?

    Again, thank you all for your generosity, time and effort.

    Reply
  • July 28, 2012 at 2:44 am

    Hi cc.

    I am really sorry but this is not supported by Microsoft in an import file.

    “You cannot represent synonyms or translations of terms by using a managed metadata import file.”

    http://technet.microsoft.com/en-us/library/ee424396.aspx

    I will however find if there is anyway we could modify it post-creation and will give you an update in the coming days.

    Thanks

    Reply
  • October 10, 2012 at 9:51 am

    Worked on my machine. Thanks!

    Reply
    • October 10, 2012 at 7:55 pm

      Happy I could help 🙂

      Reply
      • March 6, 2014 at 6:03 am

        Vlad I created an spoof import CSV file of heavy Metal categories. Did a straight term set import via my client’s SharePoint 2013 Intranet ( Oct CU) and no problems.
        I point the above PS script at it and there appears to be a bug as only the term set name is added tho the code does recursive the correct number lines in my file . Can you confirm you have tried this on sp2013

      • March 14, 2014 at 12:29 am

        Hey.. I tested it with 2010 but not 2013! Did you managed to get it working?

        Thanks!

  • April 8, 2015 at 3:05 am
    Anonymous

    Thanks, very helpful 🙂

    Reply
  • May 11, 2015 at 3:38 am
    Christian

    This is great! I was looking for something like this. But I have one Problem: What if I have also one (or maybe more) translations of my Terms. Let’s say I want to expand the CSV-file so that there is next to every “Level x Term”-column a column called “Level x Term German”. How do I need to Change your code that it will Import it correctly? I tried it with “CreateLabel” but I only get Error-Messages. Can you Help me with that?

    Reply
  • November 13, 2015 at 3:48 am
    Faisal

    It works well with level 2 term sets (continent, country) but as soon as there are level 3 (continent, country, city) it fails
    with the following error. Any thoughts?

    Exception calling “CreateTerm” with “2” argument(s): “Value cannot be null.
    Parameter name: name”
    At D:\PortCodes\ImportTermset.PS1:24 char:9
    + $parent = $node.CreateTerm($path[0], 1033)

    Reply
    • November 25, 2015 at 11:59 am
      Christian

      Hi Faisal,
      it should work, I used it myself. Check if you have the correct hierarchical structure and that you don’t leave any term out. Also, check if the user you are using for powershell does have the rights in the term store and also for the managed metadata Service in central Administration.
      Also, check if the Standard language in the term store is english! I used Vlads code to Import multiple languages and couldn’t Import unless Standard language was english!

      Hope this helps

      Reply
  • February 10, 2016 at 6:32 am
    Suresh

    Exception calling “CreateTerm” with “2” argument(s): “Value cannot be null.
    The problem with the extra space in import metadata csv file. Once remove the empty line at end , It work fine.

    Reply

Leave a Reply

15 49.0138 8.38624 1 0 4000 1 https://vladtalkstech.com 300 1