How to Export SharePoint Online Library Files to CSV with PowerShell
Question details
The user needs to generate an inventory of all PDF files located across multiple SharePoint Online site collections and document libraries, and export these results into a single CSV file.
- Product
- SharePoint Online
- Device & OS
- not provided
- Scenario
- Creating a comprehensive, tenant-wide file inventory for auditing or management purposes.
- Observed behavior
- The user requires an automated script-based approach to extract the file list into a CSV format, avoiding the repetitive task of manually exporting each library.
Ensure you have SharePoint Online Administrator privileges and that the SharePoint Online Management Shell is installed on your computer before attempting to run tenant-wide scripts.
Use PowerShell to Automate the CSV Export
Deploy a PowerShell script to automatically iterate through all site collections and document libraries, filter for PDF files, and export the aggregated data to a CSV.
Using PowerShell is the most efficient method for extracting data across multiple site collections, as it eliminates the need to visit each document library manually.
Open the SharePoint Online Management Shell on your computer as an administrator.
Use the 'Connect-SPOService' cmdlet and input your SharePoint admin center URL to authenticate your session.
Run a custom PowerShell script utilizing 'Get-SPOSite' and 'Get-PnPListItem' (if using PnP PowerShell) to loop through all document libraries and filter items where the file extension equals '.pdf'.
Append the 'Export-Csv' cmdlet at the end of your script pipeline to output the collected file properties (like File Name, URL, and Size) to a local CSV file on your hard drive.
Manually Export Individual Libraries to Excel
If you only need to inventory a few specific libraries, you can use the built-in Export to Excel feature and save the output as a CSV.
Analyze Your Exported CSV Inventory with WPS Office
Once your PowerShell script has successfully generated the CSV inventory of your SharePoint files, WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Excel for reviewing and managing that data.
- 1. Install WPS Office: Download and install the free WPS Office suite on your computer.
- 2. Open your CSV file: Launch WPS Spreadsheet, click 'Open', and select the CSV file generated by your PowerShell script.
- 3. Analyze the data: Use the Data tab to apply filters, adjust column widths, and format your SharePoint file inventory.
Frequently Asked Questions
Can I run the PowerShell export script without admin privileges?
No. Iterating through all site collections across a tenant requires SharePoint Administrator or Global Administrator credentials to ensure you have access to all document libraries.
Why is the 'Export to Excel' button missing in my SharePoint library?
The export option may be hidden if offline client availability is disabled in the library's advanced settings, or if you are using a customized view that restricts command bar features.
Does exporting to CSV via PowerShell impact SharePoint performance?
Running extensive scripts against a large tenant can cause throttling. It is recommended to use pagination, run the script during off-peak hours, or use Microsoft Graph API for large-scale operations to minimize performance impacts.
