Wictor Wilén

Microsoft Certified Master (MCM) - SharePoint 2010 | Microsoft Most Valuable Professional (MVP) - SharePoint Server MVP | Author

Create SharePoint 2010 Managed Metadata with Excel 2010

Posted at 2010-04-22 08:01 by Wictor Wilén in Microsoft Office , SharePoint 2010 with 55 comments.

Building the metadata structure in the Term Store Manager in SharePoint 2010 is not the most convenient way. I prefer working with the metadata structure and terms in an Excel document so that I can discuss the structure with colleagues and clients before implementing it.

The Term Store Manager allows you to import a comma separated text file containing a Term Set. By default the Excel 2010 Save as CSV does not save in the correct format and for that I have made a Excel 2010 macro enabled template which produces the correct format.

The Excel document allows you to build a Term Set per sheet, see figure below. It contains a number of columns:

Term Set Name - name of term set. Only needed on first row
Term Set Description - description of term set. Only needed on first row
LCID - the locale identifier. Can be left blank for default LCID
Available for tagging - TRUE if the term should be available for tagging otherwise FALSE
Term Description - description of the term
Level N Term - the name of the term. See figure below on how to build the structure

When the term set is ready you click on the new Ribbon tab added by the Excel document called SharePoint 2010 and then select Create Term Store File. Excel will then ask you for a file name and produce an import file for the Term Store from the active sheet.

If you click on Create New Term Store Sheet an new workbook sheet will be added to the workbook in which you can add another term set.

To import the term set into your Managed Metadata store, create a new Group and select Import Term Set, as in the figure below.

SharePoint will then parse your file and import the term set. And it will look something like this:

Download the Excel 2010 document

You can download the macro-enabled Excel 2010 document here. Unzip the file and open the document (TermStoreCreator.xltm) to create a new workbook.

Comments and trackbacks

# Social comments and analytics for this post by Trackback

This post was mentioned on Twitter by wictor: Just blogged: Create SharePoint 2010 Managed Metadata with Excel 2010 http://bit.ly/c8Wr9t

# You don't have to start from scratch! by SharePointFrank

Developing a taxonomy can take considerable time, resources, and money. Depending on the complexity it may take many months, several iterations, and ongoing maintenance to continually improve the classification. Starting from a valid enterprise taxonomy, ready to use in SharePoint 2010, could help: http://www.layer2.de/en/products/Pages/SharePoint-2010-Taxonomy-Metadata.aspx

# SharePoint Kaffeetasse 176 by Trackback

Metadaten Create SharePoint 2010 Managed Metadata with Excel 2010 (Link gefixt!) Office Microsoft and

# Dude, excellent post by fabian williams

Just wanted to give you credit for this amazing work. My team at work already is excited about this.

# Brilliant by KylieNZ

Excellent tool Wictor. Will definitely be a huge time saver.

# Thanks all by Wictor

Thanks for the encouraging comments!

# syNPLMgyJNRginbw by hggtuvkb

uNzViX xctdtxobcnnw, [url=http://vyopxgxfwwye.com/]vyopxgxfwwye[/url], [link=http://zpqtnprknoyr.com/]zpqtnprknoyr[/link], http://tieflzxtkkdv.com/

# thanks by kevin

now if we just had a tool to create the records library folders to match the termsets file classsifications we are making, thanks for the tool

# @kevin by Wictor

Thanks! Glad you like it!

# A little trouble by Alexandr

Hello! Nice tool, but I have a little trouble with it. When I try to load result csv file into SharePoint I get an error: "error in string 1001". But this string is similar to the previous string. Any ideas?

# Special chars by Anders Rask

Beware that the csv need to be UTF-8 to preserve special chars like ÆØÅ and others. Just do a save-as in Notepad and save in UTF-8 instead of ANSI and you are good...

# This totally rocks! by beargal34

Wow - what a great tool. Thanks so much. The only thing I had to change was when I clicked Create New Term Store file, it saved as a .txt (my only option). So I did that and then renamed the file with .csv extension and it came over perfectly.

# Is support Labels (synonymous) by José

Hi Wictor, great tool, but it support Term Labels (synonymous)? Thanks!

# @Jose by Wictor

No synonyms is not supported in the import file used by the MMS

# Missing something here... by Roy

Hi Wictor - thanks for sharing this. But I am having a little trouble getting going. When I open the template, Excel creates a new file using the template as a start, but the new tab does not display in the Ribbon. I don't see any macros when I look at the list of macros either. I made sure macros are enabled but still no luck. I'm sure I'm missing something obvious. Any ideas?

# Missing something here... by Roy

# @Roy by Wictor

Make sure that you are using Excel 2010

# limits suck by bobo

7 max sucks man

# Exposing all three levels of taxonomy by Polly Kahler

Wictor, Been working with Fabian on learning the term store. Is there a way to display the multiple tiers of a my taxonomy in the document library? For example if my term store shows Mammals-->Dogs-->Golden Retriever When I apply the taxonomy I browse through my taxonomy tree to Golden Retriever and select that. But when I look at my document library I'd like to see Mammals-->Dogs-->Golden Retriever rather than just Golden Retriever.

# Exposing all three levels of taxonomy by Polly Kahler

# @Polly by Wictor

Hi Polly. Not out of the box. You have to create a custom field control for this one; it's actually not that hard, all the information is there - Fabian will help you. I've made similar feature before, let's see if I can get the permissions to share it...

# swdprwoheehgzyt by vmrxkr

18oGRh xvytjxnkychg, [url=http://aaszqgybndnf.com/]aaszqgybndnf[/url], [link=http://ghwfglujrins.com/]ghwfglujrins[/link], http://pveptdmvypzs.com/

# @Polly by Didier Danse

Polly, you can set the "show complete path in the field" parameter in the field definition

# SharePoint by SharePoint Consulting

Tremendous blog post, loads of beneficial information. I am about to show my buddies and ask them what they think.

# Import into Existing Term Set by Lisa Atarian

Hi Wictor. Thanks for sharing such a great tool! Is there a way to import terms into an existing Term Set (or level within in the term set)? Your tool will re-create the Term Set (appending a 1 to the Term Set name) and all nodes. Is there a modification that can be done to allow import into an existing node?

# @Lisa by Wictor

Thanks Lisa. No, there are no OOB utility that will import the file and merge with existing. You have to create one yourself. I have some PowerShell cmdlets that do just that - but they are unfortunatley not ready for a public release :-)

# Sharepoint 2007 & Sharepoint 2010 Tutorials by Amit Chouhan

Hi, Huge collection of tutorials for free visit the link below today for more information:: http://www.tutorialsdirect.com/sharepoint2007/

# Metadata management by Christian Buckley

Great tool, Wictor. For 2003 and 2007, our migration platform (echo for SharePoint) also used Excel to allow users to map out their content, schemas, and metadata, and make edits/updates online or offline. Definitely interested in extending this similar to what you've done to help automate migrations to 2010. Will reach out as we QA and see if we can get your feedback on this.

# Complete Automation & Integration by Alan Whitehouse

Is there anyway to bypass Excel altogether and if we have the right data source use something like SSIS to integrate new terms automatically on a nightly basis?

# @Alan by Wictor

You could of course build something that uses the Taxonomy API to do this.

# Bulk Taxonomy TermSet Importer/Exporter by Peter

This tool can be used to export / import taxonomy from Sharepoint. http://termsetimporter.codeplex.com

# SharePoint by mulvitly

Looking forward to reading more. Great article post.Thanks Again. Really Great. SharePoint Consulting is very important today to improve the work process, all organizations of the company or companies.

# Custom Sort Order by Graham Tyler

Hi Wictor - for my taxonomy, the custom sort order is important. If I import a custom term set, the sort order defined in the comma separated text file is ignored and the term store defaults to sorting all the terms in alphabetical order. I can manually reorder the terms via the Term Store UI but as the taxonomy is large this is not practical. Is there a way to force my custom sort order to be respected upon import? Many thanks.

# Custom Sort Order by Graham Tyler

# @Graham by Wictor

No, not when importing

# Thanks by Morten

Wictor, thank you for creating the Excel tool. It has saved me a lot of time today!

# excel by Sharepoint consulting

This is another wonderful post. I just had a customer inquire about MS excel and SharePoint.

# Country and Region Term Set for SharePoint by Trackback

Body: I thought I would share a term set that I have created using Wictor Wilén's Excel Macro Template

# Language handeling by Eddie

Hi Wictor and thanx for the excel file. Do you have any tips on how to make the termset with multiple language. I have a excelfile with 4000 english keywords and about 2000 of them in swedish. do you have any idea on how to solve this

# utf8 by ami

hi, great tool, but i think that just opening the excel file in notepad and saving as a UTF8 csv file does the work as well.

# Country and Region Term Set for SharePoint by Trackback

Body: I thought I would share a term set that I have created using Wictor Wilén's Excel Macro Template

# Country and Region Term Set for SharePoint by Trackback

Body: I thought I would share a term set that I have created using Wictor Wilén's Excel Macro Template

# Boolean Values by Stefan Waler

Hi, thanks a lot for this. To translate the Boolean values ("Available for Tagging") into correct ones, you should replace the macro loop by the following. Otherwise you wouldn't be able to import the file at least using any non-english Excel version. If VarType(rCell.Value) = vbBoolean Then ' Translate boolean into English If (rCell.Value = True) Then sOutput = sOutput & ",True" Else sOutput = sOutput & ",False" End If Else If Len(rCell.Value) <= 0 Then sOutput = sOutput & "," Else sOutput = sOutput & """" & rCell.Value & """," End If End If

# Multiple language by Sda

Is there a solution to manage the translation using your tool? If not which solution could you advise?

# perfect ! by kipik

Great job !! Thanks a lot, saved me hours.

# Nice1! by Counie

thanks for taking the time to share this with the community.

# Excellent tip! by Tracy - SharePoint Consultant

As SharePoint becomes more widely used in the organization, Taxonomy is key. This is a great overview of easily managing metadata. Thanks

# Reusing terms from CSV by Jan

Thanks for a great tool. I have two term sets, with a couple of terms occurring in both sets. Will SharePoint import these values as "reused" or as seperate terms? If the latter, do I have to remove one of those imported terms and then reuse it in the other set? Thanks in advance!

# add by Directory Scape

I want to say that Building the metadata structure in the Term Store Manager is a great storage structure. Directory Scape

# add by Daily Momentum Trader Review

I would like to add that the guestbook is the best place for sharing the ideas.

# some problems by Nathan DeWitt

First, this is a nice tool. However, it has two problems. First, it doesn't escape double-quotes that exist in the name. While double-quotes are invalid in SharePoint, at least you should escape them so as to not cause the entire import to barf. Secondly, how do we escape ampersands? They are imported as boxes. I can manually change them in the term store management tool, but that is kinda crazy. Thanks!

# bili by online technology review

Hello there, You have done an excellent job. I'll definitely digg it and personally suggest to my friends. I am sure they'll be benefited from this site. -

# bili by Top Business Listings

An fascinating dialogue is value comment. I feel that it is best to write more on this subject, it may not be a taboo subject but usually individuals are not sufficient to talk on such topics. To the next. Cheers

# Easier Share point by Nigerian Movies

Building a metadata structure with sharepoint has been made less complex since this post was published.

Make a comment on this post:

Subject:

Your name:
Your Url:

Note: submissions may have to be approved before being visible, so don't submit your comment multiple times.

Latest Tweet...

"@harbars on a day like this, try the SharePoint Hail Mary - iisreset!"

Awards

MVP Public Profile

Check it out!

A Charting Web Part solution for SharePoint for free!

msfeedicon The one and only feed enhancement utility for Internet Explorer 7.
Read more...

SharePoint 2010 Web Parts in Action

Copyright © Wictor Wilén, 2006-2011.
These postings are provided "AS IS" with no warranties, and confers no rights. The content of this site are my own personal opinions and do not represent my employer's view in anyway. In addition, my thoughts and opinions open to change. Therefore you should not consider past posts to necessarily reflect my current thoughts and opinions.
Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries.

Wictor Wilén

Create SharePoint 2010 Managed Metadata with Excel 2010

Download the Excel 2010 document

Comments and trackbacks

Make a comment on this post:

Latest Tweet...

Awards

Categories

Check it out!