How do I create a term set import file?

Here you find instructions on how to create a term set import file for SharePoint.

  1. Open the Term Store.
  2. Under the section “Sample Import”, on the right side of the screen, click on the link “View a sample import file”:
    Click on the link "View a sample import file"
  3. After opening the file this is how it will look like:
    The sample import file, in text format
  4. Mark the whole data set, e.g. by typing CTRL-A, select the “Data tab” in the ribbon and then select the “Text to Columns” command:
    Select the "Text to Columns" command
  5. Change the “Original data type” to “Delimited” and then click on the “Next” button:
    Set the "Original data type" to "Delimited" and then click on the "Next" button
  6. Change the “Delimiters” option to Comma and then click the “Finish” button:
    Change the "Delimiters" option to Comma and then click the "Finish" button
  7. The Excel file will now be easier to read and work with as it will have column headers and column data. This file could then be edited by any business-user with minimum instructions:
    The sample import file, in column format
  8. Now you can replace the sample text with real data (the columns that are bold are mandatory):
    1. Term Set Name – The name of the term set (note that if a term set with the same name already exists in the term group where you import the term set import file, the term set will be imported into a term set with 1 as a suffix, so there is no risk of overwriting any term sets in the term store using the out of the box term set import function).
    2. Term Set Description – A description of the term set. It can be left blank.
    3. LCID – The language that the term set will be imported as. It can be left blanc.
    4. Available for Tagging – TRUE if users are to be able to select the term and tag it to an item in a list/library, else FALSE.
    5. Term Description – If the term itself should have a description, apart from the term’s name itself. In MetaShare, term descriptions are shown when hovering over a term in the solution.
    6. Level 1 Term – A top-level term in the term set.
    7. Level 2 Term – If the term set is hierarchical, this is the name of the level 2 term. Note that the parent of the “Level 2 Term” needs to be specified in the row’s “Level 1 Term” column.
    8. Level 3 Term, etc. – same description as for Level 2 Term.
  9. The term import file needs to have at least one row, apart from the column header row and each term is added as a new line.
  10. Now right click on column “A” and select the “Insert” option:
    Right click on column "A" and select the "Insert" option
  11. Paste the following formula into Cell A1 (first enter the cell by pressing the F2 key and then paste in the formula):
    =IF(B1="";",";""""&B1&""""&",")&IF(C1="";",";""""&C1&""""&",")&IF(D1="";",";""""&D1&""""&",")&IF(E1="";",";""""&E1&""""&",")&IF(F1="";",";""""&F1&""""&",")&IF(G1="";",";""""&G1&""""&",")&IF(H1="";",";""""&H1&""""&",")&IF(I1="";",";""""&I1&""""&",")&IF(J1="";",";""""&J1&""""&",")&IF(K1="";",";""""&K1&""""&",")&IF(L1="";",";""""&L1&""""&",")&IF(M1="";",";""""&M1&"""")
  12. Copy the value of Cell “A1” to all cells in Column 1 (just for the rows that represent a term).
  13. Right click on column “A” and select the “Copy” option:
    Right click on column "A" and select the "Copy" option
  14. Open “Notepad” and paste in the copied cells. This is how the file should look like now (note that all terms are enclosed in quotes and that commas are used as column separators):
    Open "Notepad" and paste in the copied cells
  15. Save the file by choosing the “Save As…” option:
    Save the file by choosing the "Save As…" option
  16. Change “Save as type:” to “All files (*.*)”, choose a meaningful file name with “.csv” as extension, change Encoding to “UTF-8” and then click on the “Save” button:
    Save the file as .csv in UTF-8 format