Probably every SharePoint-Admin/-Consultant knows the macro-enabled Excel document by Wictor Wilén. Many times I used it to import long lists into the Termstore.
Recently I had to import a long list of managed metadata again, but this time in German with English translation. Wictors solution only works for one language. Luckily I found Vlad Catrinescus solution via PowerShell. Again, this only works for one language, but with PowerShell almost everything is possible J
With a little help from my colleague Carsten we were able to adjust the script so that you can use the Excel-file for preparing the data and PowerShell to transfer it to the Termstore.
Simply add 7 new columns to the Excel-file (Level 8 Term…Level 14 Term). These 7 columns will be used for your second language.
[sourcecode language=”csharp”]
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].Split(“;”)[1] }
if ($parent -eq $null) {
$path[0].Split(“;”)[0]
$parent = $node.CreateTerm($path[0].Split(“;”)[0], 1031)
($parent.Labels | ? {$_.Language -eq 1033}).Value = $path[0].Split(“;”)[1]
$path[0].Split(“;”)[1]
“################”
}
}
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” + “;” + $_.”Level $($i + 7) Term”
if ($term -eq “;”) {
break
} else {
$term
}
}
)
ImportTerm -path $path
}
}
 
$url = “http://yoururl[:port]/”
$session = Get-SPTaxonomySession -Site $url
$store = $session.TermStores[“Managed Metadata Service Application”]
$termSet = Import-Csv “C:\temp\TESTFILE.csv” -Encoding Unicode
ImportTermSet $store “mystore” $termSet
$store.CommitAll()
[/sourcecode]
Depending on which is the default language in your Termstore you probably need to change the LCIDs in the script! Also note that if you are using german special characters in the csv (ö, ä, ü, …) the script will also encode it in Unicode.
Download my example: TermStoreCreator (save it as csv [unicode]) and Powershell Script.
You have a better way to do this? I would love to hear from you!
Hi Christian,
would it be possible to link the CSV file. Seems that you have a link to the wrong resource in your post.
Unfortunately, without sample CSV it is hard to follow the instructions.
br,
Hi Patrick,
just updated the text, you should find now a .xslm-file which you can download and save as csv. Save it in Unicode so that you don’t have any Problems with the Special characters.
Please reply if you have still Problems.
Thanks, Chris!
Do you have the script which can used against SPO (SharePoint Online 2013),
Hi Gaddam,
I am sorry, I don’t and unfortunately right now I don’t have time to look at this problem. In case you find an answer let me know!
Chris