Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding SharePoint Exports to Excel


By:   |   Read Comments   |   Related Tips: > Sharepoint

Problem

I find it easier to analyze data using Excel. Is there a way to export my SharePoint List or Library metadata?

Solution

Yes.

The example below shows a SharePoint list presented in a web part zone added to a site page. For aesthetics, it is presented with a cleaner look using the "Summary Toolbar", rather than the "Full Toolbar". However, to access the Excel export option, a user must open the full list. This is easy to do by clicking on the web part title, in this case "Helpdesk Progress".

The full list shows the full toolbar. Click the "Actions" menu and then select "Export to Spreadsheet".

A prompt displays asking to "Open" or "Save" the export. For the purposes of this article I will save it first, but you can do either.

There is a default file name but I change it to something more meaningful. This can be useful because the export file can be saved and reused by using the export file directly in Excel rather than navigating through the SharePoint portal again..

The file is saved with the "iqy" file extension.

When opening the file in Excel 2007, I get a security prompt which must be enabled to use the file. (Excel 2003 works also...there maybe a different security prompt.)

Results

Here's what the Excel export looks like:

The iqy file is not a static file. The SharePoint data is not stored in the .iqy file. This is "Web Query File" and refreshes the SharePoint data every time you run it. It's a great feature, but don't forget, if you are not connected to the network (and if you don't have permissions) you won't get the data.

If you need to save a snapshot (a point in time) of the SharePoint data you should "Save As" an Excel Workbook (or some other static format). You would also need to "Save As" a workbook if you were sharing information with an external user (i.e. outside the organization).

  • Save the .iqy file if you want to rerun the export with fresh data without going through the "Export to Spreadsheet" procedure (described above).
  • "Save As" an Excel Workbook, if you need a static copy of the data. The workbook will NOT refresh new data. Only the web query (.iqy) file can.

Document libraries can also be exported to Excel, however, the export will not contain the document. In it's place a link to the document is available.

Next Steps
  • Check out MSSQLTips.com for great information about Microsoft SQL Server.


Last Update:






About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools