Term store import file conversion

This week I was working with the term store and had to create and import a big chuck of data in the term store. We all know that we have multiple options here

• Just enter them one by one in the browser (great for a few entries)
• Use the csv import
• Use CSOM
• Use Office Dev PnP PowerShell

This post will address the csv import.

Getting started with the csv

For the csv import we have a sample that can be downloaded from the term store (http://-admin.sharepoint.com/_layouts/15/1033/ImportTermSet.csv)

This csv file can be opened with Excel where you can edit this. When you open the file you see that the file is comma separated with "" as there are spaces in between. Now that the csv is opened in Excel and edited with all the terms in the csv you need to save it in the same format.

Well here is the problem, Excel does not have an option to save the csv in that exact same format.

Solving the export

We have 2 options here:

• Excel with Macro that will save it correctly
• PowerShell

Wictor Wilén has an Excel file that has a Macro in an Excel file that has the structure. This can be downloaded from http://www.wictorwilen.se/Post/Create-SharePoint-2010-Managed-Metadata-with-Excel-2010.aspx This is a great solution for this specific case and Wictor has a great description about this solution

The other option is to save the Excel file as a tab-delimited txt file and use PowerShell to convert to a CSV.

Now that we have a txt file we can use PowerShell to convert this tab delimited file to a csv file with a comma and ""

This command can also be used for any other excel file that needs to be converted to a csv file.