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 these other SharePoint tips.
