This post is migrated from previous hosting provider. There are still some issues with old posts. Please make a comment on this post with any issues.

Create SharePoint 2010 Managed Metadata with Excel 2010

Tags: Microsoft Office, SharePoint 2010

Apr 22 2010 10:01 AM
* * * . - -

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

Term Set in Excel 2010

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.

Term Store Ribbon tab in Excel 2010

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.

Import Term Set

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

image

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.

77 Comments

  • Trackback said

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

  • SharePointFrank said

    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

  • hggtuvkb said

    uNzViX <a href="http://xctdtxobcnnw.com/">xctdtxobcnnw</a>, [url=http://vyopxgxfwwye.com/]vyopxgxfwwye[/url], [link=http://zpqtnprknoyr.com/]zpqtnprknoyr[/link], http://tieflzxtkkdv.com/

  • kevin said

    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

  • Alexandr said

    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?

  • Anders Rask said

    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...

  • beargal34 said

    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.

  • Roy said

    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?

  • Roy said

    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?

  • Roy said

    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?

  • Polly Kahler said

    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.

  • Polly Kahler said

    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.

  • Wictor said

    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...

  • vmrxkr said

    18oGRh <a href="http://xvytjxnkychg.com/">xvytjxnkychg</a>, [url=http://aaszqgybndnf.com/]aaszqgybndnf[/url], [link=http://ghwfglujrins.com/]ghwfglujrins[/link], http://pveptdmvypzs.com/

  • Lisa Atarian said

    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?

  • Wictor said

    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 :-)

  • Christian Buckley said

    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.

  • Alan Whitehouse said

    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?

  • mulvitly said

    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.

  • Graham Tyler said

    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.

  • Graham Tyler said

    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.

  • Eddie said

    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

  • ami said

    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.

  • Stefan Waler said

    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 0="" then="" soutput="sOutput" &="" ","="" else="" soutput="sOutput" &="" """"="" &="" rcell.value="" &="" ""","="" end="" if="" end="" if=""></= 0 then soutput = soutput & "," else soutput = soutput & """" & rcell.value & """," end if end if >

  • Jan said

    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!

  • Directory Scape said

    I want to say that Building the metadata structure in the Term Store Manager is a great storage structure. <a href="http://directoryscape.net/">Directory Scape</a>

  • Nathan DeWitt said

    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!

  • Top Business Listings said

    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

  • OLED TV Reviews said

    Thanks for the post Inspirational Designs for Nike. One more important component is that if you are a senior, travel insurance regarding pensioners is something that is important to really take into account. Thanks alot : ) for your blog post.

  • dogwood tree said

    it’s really a good write-up, would be interested to read such more informative draft from you ahead, can be a wonderful help to the new readers like me.

  • Anna said

    Hi Wictor
    I have to develop a taxonomy for our SharePoint 2010 intranet and found your Excel sheet. I am going to use it! But now have to ask a really stupid question (I am a librarian, not an IT person). The C1, C2, C3, etc - will be replaced by real terms, as I understand it, e.g. Information Services, Human Capital, etc. etc. So the the C1, C2, etc, will not feature. It is just listed as such to represent the terms that must be enterd there?

    I just need to make dead sure - that there may be some magic involved which I do not understand.

    And one last question: I do not see a place for synonyms. One will have to go to the Term Store management function afterwards and enter it there under "Other labels?"

    Thanks in advance!

    Anna

  • Vlad Catrinescu said

    Thank you for your work. Your document and how to automate the import using PowerShell are explained over here:

    Importing Managed Metadata TermStore from .csv using PowerShell
    http://www.vladcatrinescu.com/2012/06/importing-managed-metadata-termstore.html

  • Rathmann Wolfgang said

    Hi Wictor,

    is it possible to import a taxonomy in German and English?
    LCID is 1033 + LCID 1031

    Thanks in advance!

    Wolfgang

  • Bobby said

    Hello Wictor,

    what a great tool, to save effort and automatically import huge masses of values into SP2010`s TermStore.

    Thankx a lot - great job ! :-)

    Bobby

  • Bobby Taylor said

    Hello Wictor,

    is it also possible to update an existing termset with new values, without destroying the existing values or termset itself ?

    Cheers !

  • small pet collars said

    And one last question: I do not see a place for synonyms. One will have to go to the Term Store management function afterwards and enter it there under "Other labels?"

  • anu said

    Good post indeed!
    I would like to know, Is there any way we can enabled the Term-Driven Page with Friendly URL option for any term?

  • seo said

    And one last question: I do not see a place for synonyms. One will have to go to the Term Store management function afterwards and enter it there under.

  • www.pizza-sellyoak.co.uk said

    I can see that you are are genuinely passionate about this! I am trying to build my own website and youve helped me with some great information.
    Dear friend. I truly just like your posting and your current web page all in all! That write-up is really plainly composed and without difficulty understandable. Your Wordpress style is awesome as well! Would be awesome to discover exactly where My partner and i are able obtain this. Please hold up the very good work. We all need much more this kind of website owners just like you on the web and much less spammers. Wonderful man!

Add a Comment

Facebook

About Wictor...

Wictor Wilén is a Director and SharePoint Architect working at Connecta AB. Wictor has achieved the Microsoft Certified Architect (MCA) - SharePoint 2010, Microsoft Certified Solutions Master (MCSM) - SharePoint  and Microsoft Certified Master (MCM) - SharePoint 2010 certifications. He has also been awarded Microsoft Most Valuable Professional (MVP) for three consecutive years.

And a word from our sponsors...

SharePoint 2010 Web Parts in Action