Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Understanding SharePoint Exports to Excel


By:   |   Last Updated: 2010-04-14   |   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".

helpdesk progress

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

edit in datasheet

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.

file download

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..

excel export

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

excel

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.)

security notice

Results

Here's what the Excel export looks like:

microsoft excel

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


Last Updated: 2010-04-14


next webcast button


next tip button



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





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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools