locked
SharePoint 2007 List Export to Excel Returning Superfluous Characters RRS feed

  • Question

  • I have two separate Excel reports which are derived from SharePoint list data connections that have errors. The first returns an error in a field where the results should appear as "1; 2" or "1" or "1; 2; 3;" however, the results are instead coming up as "1;#2," "1" and "1;#2;#3" respectively. This field type uses multiple selections with checkboxes.

    The other, more significant error appears in a field type using People and Groups, and the results, which should be "LastName, FirstName" return as "LastName, FirstName;#111" (or any other three digit number, which seems to be assigned to the person's account - as in, it is the same three digit number each time the person's name appears in the spreadsheet).

    I have tried scouring the Internet for other instances of these errors and am coming up empty-handed. I am sure I am just not hitting the right keywords. Has anyone else seen these errors? I would really like to get this cleaned up, because as it stands, I cannot present this report to my organization's leadership. They are already anti-SharePoint, and I am trying to win them over. This report isn't doing me any favors.


    • Edited by osarah Friday, January 4, 2013 3:06 PM typo
    Friday, January 4, 2013 3:05 PM

Answers

  • I finally solved it myself. Hurray!

    For anyone else who has this issue, I could not resolve it on the SharePoint side but found a workaround on the Excel side. Here's what I did:

    Create a macro and use/find replace. The first instance of the superfluous characters (in my case) was ";#???:#" (? being a wildcard). I repalced that with "; ". The second instance was ";#???" - that was replaced with "".

    • Marked as answer by osarah Monday, January 14, 2013 5:06 PM
    Monday, January 14, 2013 5:06 PM

All replies

  • Hi osarah,

    When you export from SharePoint and you have a lookup field (or person group field), the actual value that is returned in string format is the value as well as the reference ID of the lookup value.  In the case of the person/group entries.  The delimter for these is ;#

    You could do some formula magic to pull the values you need within excel (easier), or write your own custom web service to pull the values you need and consume the information from excel.


    - Xenox G.

    Monday, January 7, 2013 3:38 AM
  • Hi Xenox,

    Thank you for your help. I am not much of a programmer and just got saddled with this responsibility as no one else in my organization knows how to use SharePoint. Do you have any advice or resources for how to go about using the Excel route to pull the values?

    Monday, January 7, 2013 3:47 PM
  • I finally solved it myself. Hurray!

    For anyone else who has this issue, I could not resolve it on the SharePoint side but found a workaround on the Excel side. Here's what I did:

    Create a macro and use/find replace. The first instance of the superfluous characters (in my case) was ";#???:#" (? being a wildcard). I repalced that with "; ". The second instance was ";#???" - that was replaced with "".

    • Marked as answer by osarah Monday, January 14, 2013 5:06 PM
    Monday, January 14, 2013 5:06 PM