Export document library meta-data to Excel

[Oz Oscroft]

Hi all - how do I export meta-data from a document library in SharePoint online to Excel?  It used to be easy in previous versions of SharePoint when you had the ribbon, but I can't find the 'Export to Excel' option any more.

Thanks in advance, Oz

[#2]

I hadn't noticed this was gone! While I don't *need* this (at least, not yet), I see its value and hope Microsoft can add this in to the Modern experience. In the meantime, I guess we'll have to switch between Classic/Modern view to accomplish this?

[#3]

Yep, this option has been removed from modern document libraries

[#4]

Quick Edit is still available, so you can copy and paste from that to Excel as a workaround.

[#5]

.. and I've just stepped back you the 1980's! Why such a massive step backwards - if you can't export to excel and update automatically one may aswell just keep all this stuff in Excel in the first place. What's going on???

[#6]

What is missing for you with the Quick Edit view? (not troling, just asking)

[#7]

This may become an issue for the recordkeeping people of the world who want to keep a record (in the metadata) of what was destroyed at the end of the retention period.

Traditionally, records were destroyed by the container/(paper) file, rather than the document but I've had records people ask me since 2012 how to keep a record of what was destroyed from a document library. Excel was the only real way to do this.

Retention policies in SharePoint and the new ones in Office 365 change the nature of disposal, from containers (document libraries mostly) to individual documents that 'fall off a cliff' into the digital void when they reach the end of their retention period (unless subject to a legal hold). It still raises the question, how do you know what's been destroyed, and when.

[#8]

Hi Carsten - when you export it creates a link in Excel to the source information in SharePoint.  When you update any info. in SharePoint it automatically updates in Excel.  Using copy & paste from Quick Edit doesn't have this link so you'd need to go through all the manual steps every time you wanted to report on the current situation.

[#9]

I was coming up against this same issue and was able to get my metadata into Excel by connecting to the library from inside Excel.  

For those with SharePoint Online/Office 365, to access your SharePoint library, in Excel go to the Data tab and click Get Data, then select From Online Services, and From SharePoint Online List. 

There is also an option for just SharePoint List under From Other Sources in the same menu -- perhaps this will work for on-prem users?

[#10]

Thanks @Melinda Morales - unfortunately when I go to Get Data > From Online Services, the only option I see is 'From Facebook'.  I'm signed in to Excel using my Office 365 account and we have everything on O365 online.  Do you know if something has to be configured to show SharePoint Online as an option please as it sounds like just what I'm looking for.

Thanks again, Oz

[#11]

Looks like it might be dependent on Power Query (Get & Transform for Excel 2016). Found these articles: Connect to a SharePoint List (Power Query) and Get & Transform in Excel 2016

[#12]

... and we have a solution!  It looks like the Get Data > From Online Services > From SharePoint is the simple option for those who have an Enterprise license.  Unfortunately, I believe it only appears for those with Power Query which is not included for Essentials / Premium licenses.

For those who don't have the required license, the same result can be achieved by following the steps in this excellent article:

http://www.sptechcon.com/news/sharepoint-dashboard-excel 

Hope this helps as it's taken me far too long to work out how to achieve something which required the click of a single button in SharePoint 2010! 

[#13]

To export document libraries to excel, click on the "..." to the right of the "Download" heading. There you will see "Export to Excel".  I have attached a screen shot.

Good Luck,

Karen