none
export list from sharepoint to excel RRS feed

  • Question

  • Hello!

    When I export a list to excel from Sharepoint 2016, but when excel open, one of the fields (status) didn't return the correct value, instead return the text "approved, refused, completed", returns codes, like "15,16,17".

    How can I fix this?

    Wednesday, November 1, 2017 11:24 AM

All replies

  • The Status field must be a lookup field. A Lookup field does NOT store the text, only the ID.

    There are multiple method to overcome this, but I don't know if you have SharePoint Designer, so I'll suggest an solution on the Excel side.

    Create a Status table within Excel, on a different tab, then use VLookup() to find the Status ID, and return the Status Name.


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA

    Wednesday, November 1, 2017 11:54 AM
  • We have the SharePoint Designer.

    How can I use the Designer to overcome this?


    Wednesday, November 1, 2017 12:43 PM
  • Check this forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/f3436b19-9184-4ed2-a4fe-4320cefeaaca/removing-the-id-of-a-lookup-column-when-exporting-sharepoint-list-to-excel?forum=sharepointgeneralprevious

    https://social.technet.microsoft.com/Forums/en-US/0727592c-3772-47ff-b7f0-3c8b67855cdf/sharepoint-export-to-excel-shows-internal-id-also-for-lookup-field?forum=sharepointgeneralprevious

    Regards

    Prasad Tandel

    Wednesday, November 1, 2017 1:20 PM
  • Hi.

    I think your column is workflow status column.

    Workflow status column is a special column, it have it's own type : [Workflow status type].

    Workflow Status type is inherit from number type, so it's value is all number like 16 or 1, but not string or other type of data.

    So, I'm afraid you have to make a dictionary to replace the value to string type in excel after you exported your list to a excel file.

    This following information is workflow status and value number mapping :  

     type / value

    NotStarted = 0

    FailedOnStart = 1

    InProgress = 2

    ErrorOccurred = 3

    StoppedByUser = 4

    Completed = 5

    FailedOnStartRetrying = 6

    ErrorOccurredRetrying = 7

    ViewQueryOverflow = 8

    Max = 15

    Canceled = 15

    Approved = 0x10 (16)

    Rejected = 0x11 (17)

    Thank you.

    P.S. When you search the workflow status column by value, you also have to search their mapped value like 16 means the workflow which one has been approved.

    Thursday, November 2, 2017 7:27 AM