SharePoint 2010: Managed Metadata Converts Ampersand and Double Quotes to Unicode
We have been making a lot of use of Managed Metadata lately. One use is to associate people with a team, so we have created a user profile property associated with a Teams term set. We also created a CSV export for people search so it will export this field among many others for all people returned in the search, with the intention that this will be opened in Excel.
This was working fine until we tested a CSV export in Excel where the Team field contained an ampersand (&). It unexpectedly appeared in Excel as a collection of symbols. I had noticed previously that the ampersand looked a little unusual in SharePoint but thought nothing more of it until now.
It transpires that the ampersand (&) is automatically converted to a Unicode/wide character version (＆) when it is added to the Term Store. This is represented using hex code FF06 or integer 65286. The only documentation I have found confirming this so far is in the TaxonomyItem.NormalizeName method which “Normalizes a name of a TaxonomyItem object”, or in other words, converts the given string to the format used in the Term Store.
The remarks section says:
The name will be normalized to trim consecutive spaces into one and replace the & character with the wide character version of the character (\uFF06).
However upon reflecting the method I found the following code:
return CommonValidator.trimSpacesRegex.Replace(name, " ").Replace('&', 0xff06).Replace('"', 0xff02);
This shows that not only does it modify the spaces and ampersand but it also replaces double-quotes (“) with a Unicode version too (＂), using hex code FF02 or integer 65282. This was unexpected and to my knowledge undocumented.
Now that we are armed with this information, to fix the CSV export issues now simply involves replacing these Unicode characters back to the original ASCII characters that we used.
You might also use the TaxonomyItem.NormalizeName method if you want to compare a string with a term name taken from the term store, by using it to ‘normalize’ the string prior to comparing with the term.
- Click to email (Opens in new window)
- Click to print (Opens in new window)
- Click to share on Twitter (Opens in new window)
- Share on Facebook (Opens in new window)
- Click to share on Reddit (Opens in new window)
- Click to share on Google+ (Opens in new window)
- Click to share on Tumblr (Opens in new window)
- Click to share on Pinterest (Opens in new window)
- SharePoint: How to check which Site Template was used to create a site just using a web browser
- SharePoint Online: Adding Term Store Administrators
- SharePoint 2010: How to set Taxonomy Field Values programmatically
- SharePoint 2010: List View Threshold Explained
- SharePoint Online: Sign up for a free Office 365 Developer Site from your MSDN subscription
- June 2015 (1)
- June 2013 (1)
- May 2013 (2)
- April 2013 (3)
- March 2013 (1)
- February 2013 (1)
- December 2012 (1)
- November 2012 (3)
- October 2012 (3)
- August 2012 (1)
- July 2012 (1)
- June 2012 (1)
- May 2012 (1)
- April 2012 (2)
- March 2012 (5)
- February 2012 (8)
- September 2011 (1)
- July 2011 (2)
- June 2011 (6)
- August 2010 (1)