0

In a situation when on text field I have option 'Append to existing text' checked How I can export data to excel to contain all the data from this field. The default export to excel is only exporting the last comment.

Improve this question
2
  • Could you expand what you mean by "appended data"? You should be able to just export a SharePoint list via the libraries ribbon. From there if you're looking for "most recent additions" you should be able to sort or filter by date on either the "Modified" date or the "Created" date. Commented Mar 15, 2019 at 14:34
  • Suppose for any column "Append Changes to Existing Text " setting is active... in that case I want export all comments into excel...right now I am able to get only last comment... So is there any way where I can export all comment.. Commented Mar 15, 2019 at 14:40

4 Answers 4

Reset to default
1

You cannot see other comments because they are in previous version of the item.

Have a look at this question, I haven't tried it myself but it seems to work for some:

https://stackoverflow.com/questions/10561661/sharepoint-list-version-history-export-to-excel

  1. Use the Export to Excel option in the List section of the ribbon (make sure the list view you export includes a modified column - thanks T6J2E5).

  2. Save the owssvr.iqy file and open with notepad

  3. Copy just the URL from the file and paste it back into your browser, adding "&IncludeVersions=TRUE"

  4. Save the XML file and open in Excel (or your favorite XML viewer), selecting the "As an XML table" open option.
  5. You'll have to delete the first few columns and rows as they contain the schema data but other than that you should have all the version history (I suggest you add the Version column to the view). You can sort by the Modified column to get a chronological change log of the entire list
Improve this answer
3
  • 1
    With this solution I am able to get only last comment not all versions. Commented Mar 19, 2019 at 9:55
  • I also opened .iqy file in notepad, appended &IncludeVersions=TRUE, saved it, opened it, and only the last appended text appears in the spreadsheet cell. Commented Apr 21, 2020 at 0:50
  • I get Error Any way to do it with PowerShell? Commented May 5, 2021 at 18:29
1

I just had a request from Shawn Keene for help and clarification exporting XML of list items where one column appends text

It seems that for SharePoint online this technique no longer seems to work as outlined above. BUT If you need to export a SharePoint Online list's items INCLUDING the appended text for multi line columns you can do it with 2 changes to the URL syntax.

First download the .iqy file by clicking export to EXCEL button for your list view. Open the .iqy file it in a text editor

Get the URL from the top of it. It will look like this

https://YourTenantName.sharepoint.com/sites/YourSiteName/_vti_bin/owssvr.dll?XMLDATA=1&List={FE60B1F1-8050-4277-A96C-3B95508506F5}&View=CC945030-5723-4437-815A-9F622838DA29&RowLimit=0&RootFolder=

To download the XML file for this list, and this view, WITH the appended text values we need to make 2 changes.

  • First put {} around the view GUID
  • Second append to the end &IncludeVersions=TRUE

This will give the following URL

https://YourTenantName.sharepoint.com/sites/YourSiteName/_vti_bin/owssvr.dll?XMLDATA=1&List={FE60B1F1-8050-4277-A96C-3B95508506F5}&View={CC945030-5723-4437-815A-9F622838DA29}&RowLimit=0&RootFolder=&IncludeVersions=TRUE

Paste this into the URL bar of a webbrowser and click enter.

This url will download an XML file with data rows like the following In this example my multi line, append text column name is testappendmultiline

<rs:data>
   <z:row ows_LinkTitle='23' ows_test='TargetSize' ows_ID='1' />
   <z:row ows_LinkTitle='23' ows_test='TargetSize' ows_testappendmultiline='first entry' ows_ID='1' />
   <z:row ows_LinkTitle='23' ows_test='TargetSize' ows_testappendmultiline='second entry
' ows_ID='1' />
   <z:row ows_LinkTitle='9' ows_test='Ph' ows_ID='2' />
   <z:row ows_LinkTitle='9' ows_test='Ph' ows_testappendmultiline='first entry' ows_ID='2' />
   <z:row ows_LinkTitle='72' ows_test='Ph' ows_ID='3' />
   <z:row ows_LinkTitle='7 m²' ows_ID='4' />
   <z:row ows_LinkTitle='m²' ows_ID='5' />
   <z:row ows_LinkTitle='m²' ows_ID='6' />
</rs:data>
Improve this answer
0

thanks for your question. thanks also to @Marek Sarad for your answer which helped me out. I've actually used it and here are the steps I used

  • Make a view that contains list item ID and version, and all comment fields
  • Once you get the URL Paste the URL into the browser
  • An XML file downloads
  • From within Excel data / Get data and open the downloaded XML
  • Now you need to do some testing
    • In your browser bring up a view of one list item so you can see all the comments
    • filter the spreadsheet by that item's ID number
    • You'll see many more rows than comments, but if you use the "filter" button on different columns you'll see only the unique number of comments as you see in the actual item. Note the name of the column you find the same number of items as comments. In my case Attribute:ows_Latest_x0020_Comment
    • In Excel remove all filters
    • Select all rows
    • use the "Data tools" menu "remove duplicates, but select only the column you identified. In my case Attribute:ows_Latest_x0020_Comment
    • Now sort by ID and Version

You should have one row per comment, and still have the person who created the comment and the date time it was created.

Improve this answer
0

Ok, I figured out my issue. I was downloading and saving the file to my desktop as excel, not leaving it as .iqy. My apologies, disregard my last question.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.