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…”
   
4. Change:
   a. Decimal symbol                .               (full stop)
   b. List separator                     ,               (comma)
   
   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”

For another article on the same, see here: http://akbaraji.wordpress.com/2012/05/29/the-comma-and-semicolon-issue-in-excel-due-to-regional-settings-when-open-csv-file/

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
   
6. Click “View a sample import file”, on the right (properties pane)
   
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.
   

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?

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)
   
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
   
8. Click “Browse”
9. Choose the relevant csv file
   e.g.: ImportTermSetNew.csv
10. Click “OK”