Forum Discussion

temhkam's avatar
temhkam
Copper Contributor
Feb 07, 2023

SharePoint 2013 "Export Spreadsheet" outputs in Excel only 30 rows

Hi, all!

SharePoint 2013, when I click on the "Export Spreadsheet" button from the survey, the file "owssvr.iqy" is downloaded. Opening it, it opens in Excel and there are only 30 rows in the table, although there are 81 answers in the survey. How do I make Excel output all the answers?

 

2013
Lists
SharePoint Server
  • Adnan_Amin's avatar
    Adnan_Amin
    MVP
    Feb 07, 2023

    Hi temhkam,

    In SharePoint 2013, when you export a survey to a spreadsheet, the maximum number of rows that can be exported is limited to 30. To export all the answers, you need to modify the query used by the "owssvr.iqy" file. Here's how:

    1. Open the "owssvr.iqy" file in a text editor like Notepad.
    2. Look for the line that begins with "URL;".
    3. Add the following query string parameter to the end of the URL: &RowLimit=0.
    4. Save the file and close the text editor.
    5. Double-click the "owssvr.iqy" file to open it in Excel.

    This should now export all the answers in the survey, not just the first 30. If you still have issues, try increasing the row limit by adding a larger number in place of "0", for example &RowLimit=1000.

     

  • shmowlana's avatar
    shmowlana
    Copper Contributor
    Oct 25, 2023
    If modifying the "owssvr.iqy" to set the RowLimit method does not work..
    Run a PnP PowerShell script to update the RowLimit of the default view of the survey list to 1000. This view is usually named "Overview" by default. The export to excel should then render items beyond the first 30.

    #Set View Properties
    Set-PnPView -List $ListName -Identity $ViewName -Values @{"RowLimit" = $RowLimit}

    Value for RowLimit might have to be in UInt32 format (in case it throws an exception)
    [UInt32]$RowLimit = [convert]::ToUInt32("1000")

Resources