Term store import with Office Dev PnP
As a follow up on my post from last week I wanted to add some extra data in the terms. This is not possible with the default CSV import that SharePoint has. I mentioned in my previous post that Office Dev PnP has also an option to import terms. And with that option we can add a lot more than only the term itself.
What commands do we have?
We have a 2 options here to add term to Office 365.
The first one is to use the command ‘Import-SPOTermSet’. This command uses the same CSV file as SharePoint does and is a scripted version of the default import. The second one is the command ‘Import-SPOTermGroupFromXml’. This command expects an XML file in the format of the Office Dev PnP Provisioning engine.
The second command gives us the option to add extra options to our terms. It will give us the option to add labels in different language or custom properties. Per term it is also possible to set a separate language ID.
How does that XML look like?
The XML is part of the Office Dev PnP Provisioning engine and is formed like the XSD that describes the provisioning template. To get an example of this XML you can run ‘Export-SPOTermGroupToXml’. This will give you the XML from the current site that you are connected to.
In this image you see that we have a hierarchy of terms with extra labels for the Dutch language. The other great thing here is that all the ID’s are in there so you have the opportunity to move the terms between environments.
Creating this XML
Well now you can say creating this XML is a lot of work and why should I do that here and not in the UI itself, I still need to type in everything. Here you have a point, but like most metadata this already exists somewhere, perhaps an Excel sheet or database with a hierarchy. For this example, I will use a flatten CSV export from a database with the following headers:
ID, Name, parentID, sector
With this known we can create a PowerShell script that will generate the XML that is necessary for the import command. There is no single command that can generate this XML, the ‘Export-Clixml’ command just generates an XML file, but that does not represent the Office Dev PnP format. So we need to create our own.
To create our own XML, we need to go to our C# experience for creating a XML file. So we need to create and object of the type ‘System.Xml.XmlTextWriter’ with a file path and the encoding of the file.
Now that we have the basic skeleton for the XML TermGroup and TermSet we can add every term and its hierarchy to the XML. The easiest way is to use a recursive function for this.
In the CSV we have 1 base entry that does not have an parentID, that is the one we are going to start with and we will call the recursive function with this row in our CSV. The complete script can be found with this link
Importing the XML
Now that we have a XML file that is compliant with the Office Dev PnP format we can use the command ‘Import-SPOTermGroupFromXml’ with our XML
Conclusion
There is no easy way to convert and database or Excel file to a hierarchy in a termstore, but when you have a big amount of data, this script can save you a lot of time and when you have the base here you can easily adapt it to other structure.