Wednesday, 24 July 2013

How to Update the SharePoint 2013 Term Store by Importing it from an Excel 2013 Spreadsheet

Changing your Region Settings

The first thing you need to do is make sure your region setting are formatted correctly. This is because the file format used by the SharePoint Term Store is a csv. This converts table using the “List separator” in your PC’s region settings. If you don’t set these first you have difficulty opening and converting the spreadsheet to and from a csv file format.

The csv extension literally stands for Comma Separated Values.

Change the “List separator” from a semi-colon to a comma

  1. Open “Control Panel”
  2. Click “Region” or “Region and Language”, depending on your Windows operation system
  3. Click “Additional settings…”
     photo Region1.jpg
  4. Change:
    a. Decimal symbol                .               (full stop)
    b. List separator                     ,               (comma)
     photo Region2.jpg
    Both need to change. It seems to conflict if you just change the “List separator” and not both. If they are both comma’s, the csv conversion returns semi-colon’s.
  5. Click “OK”
  6. Click “OK”

Import the csv sample template from SharePoint 2013 Term Store

  1. Open SharePoint 2013
  2. Click the cog (Site Actions), top right corner
  3. Select “Site Settings”
  4. Click “Term store management”, under “Site Administration”
  5. Select the Managed Metadata Service application, on the left tree view
     photo TermStore3.jpg
  6. Click “View a sample import file”, on the right (properties pane)
     photo TermStore2.jpg
  7. Click “Save”

Working on the Terms

  1. Open this file (ImportTermSet.csv) with Excel 2013.
    If all the region setting are correct, it will open correctly, in the correctly formatted columns.
     photo TermStore4.jpg
To see the raw csv, you can open it in Notepad.
For more on the Managed metadata input file format, here: http://technet.microsoft.com/en-us/library/ee424396.aspx

When creating your Managed Metadata terms store it is always better to plan what you are going to do and what they are going to be used for. TechNet has some nice articles on this: http://technet.microsoft.com/en-us/library/ee530389(v=office.15).aspx

Compare Excel spreadsheet with Term Store

What will the spreadsheet going to look like in the Term Store?

 photo TermStore5.jpg

 photo TermStore8.jpg


Save to csv

If you have competed creating the terms in Excel and have the spreadsheet in xlsx format and you want to re-save it to into csv format.
  1. Open Excel 2013
  2. Click "File" tab, on the Ribbon
  3. Select "Save As", on the left
  4. Click "Browse"
  5. Select the file type: CSV (Comma delimited) (*.csv)
     photo TermStore9.jpg
  6. Give the file a relative name
    e.g. ImportTermSetNew

Import csv to SharePoint 2013 Term Store

Remember permission. The authentication account which you use to access the Term Store Management Tool, must be listed as a "Term Store Administrator", or you will not be able to accomplish this task.
  1. Open SharePoint 2013
  2. Click the cog (Site Actions), top right corner
  3. Select “Site Settings”
  4. Click “Term store management”, under “Site Administration”
  5. Select the Managed Metadata Service application, on the left tree view
  6. On the left, in the tree view, select the “Term Group” you would like to upload to.
  7. Select “Import Term”
    e.g.: Test Term Group
     photo TermStore6.jpg
  8. Click “Browse”
  9. Choose the relevant csv file
    e.g.: ImportTermSetNew.csv
  10. Click “OK”
     photo TermStore7.jpg

5 comments:

  1. You saved my day! Thank you so much!

    ReplyDelete
  2. Hi Jax - Thanks. Do you know of any other Excel format that allows us to enter the default and "other" labels for the each term?

    ReplyDelete
  3. Thanks a lot. I am new to SharePoint and your post solved my issues !

    ReplyDelete
  4. Useful article. I want to know best approach to add some new terms to the store without affecting the existing ones. If we import from a file of new values will they be appended to the term store or will the entries in the file replace all existing entries?

    ReplyDelete
  5. Unfortunately,
    The new entries replace existing entries,I am trying to find a solution for this...

    ReplyDelete