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”
    

How to update the Content Type Hub URL

We recently moved our DEV SharePoint 2013 DB’s to our QA environment. We moved the main content DB, the managed metadata DB, and the content hub DB. Everything was recreated and moved over correctly. 

But when I went to change a content type in the Site Collection Content Type Hub, the content type appeared as “read only”. After some investigation I realised the “Managed Metadata Service” was still showing the old DEV Content Type Hub URL.

How to check the Content Type Hub URL:

1. Open Central Administration
2. Click “Manage service applications”, under “Application Management”
   
3. Select “Managed Metadata” service (the first one). Click on the side, to select it, but not open it.
4. Click “Properties”, in the Ribbon
   
5. Scroll to the bottom, and confirm the Content Type Hub is the correct Content Type Hub URL

How to update the Content Type Hub URL:

1. Goto the server (Server 2012) which hosts the SharePoint 2013 Central Administration.
2. Open the “SharePoint 2013 Management Shell” as the Administrator
   a.  Open the Search
        i.  Hover over the bottom right corner
        ii. Click on the Search icon
   b.  Search for “SharePoint 2013 Management Shell”
   c.  Right click “SharePoint 2013 Management Shell”
   d.  Select “Run as Administrator”, at the bottom
3. Run the script below:
   Set-SPMetadataServiceApplication -Identity "{Managed Metadata Service name}" -HubURI "{Content Type Hub URL}"
   Example:
   Set-SPMetadataServiceApplication -Identity "Managed Metadata" -HubURI "http://vm-sharepoint2013-qa-hub"
4. Check the Content Type Hub URL is correct, see above section (How to check the Content Type Hub URL)

Now to edit the content type:

1. Open the Content Type Hub (http://vm-sharepoint2013-qa-hub)
2. Click the cog (Site Actions), top right corner
3. Select “Site Settings”
4. Click “Site content types”, under “Web Designer Galleries”
5. Click on the Content Type you want to change
6. Click “Advanced Settings”, under "Settings", near the top
   
7. Select “No”, under “Read Only”
   
8. Click “OK”
9. Now change the Content Type as needed.

How to find a particular Site Collection URL from SharePoint Central Administration

1. Open “Central Administration”
2. Click “Manage web applications”, under “Application Management”
   
3. Here you will see the list of all the Site Collection in your farm