How to Export a OneDrive File Inventory to CSV or Excel
Question details
The user needs to generate a comprehensive inventory report of all files stored in a OneDrive for Business account, including metadata such as file paths, modification dates, and the names of the individuals who last modified the documents, and export this data to a CSV or Excel format.
- Product
- OneDrive for Business
- Device & OS
- not provided
- Scenario
- Generating an audit or administrative inventory report to track file details and modifications across a OneDrive or SharePoint environment.
- Observed behavior
- Standard OneDrive interfaces do not offer a direct one-click export for comprehensive metadata, requiring users to leverage SharePoint administration tools, PowerShell scripts, or audit logs to compile the required file inventory into a spreadsheet.
Ensure you have SharePoint Administrator or Global Administrator privileges in your Microsoft 365 tenant, as running PowerShell commands against OneDrive for Business sites requires elevated permissions.
Use SharePoint PnP PowerShell Module
PnP PowerShell is the recommended method for enumerating files within a user's OneDrive for Business site and extracting properties like name, path, and modified date directly to a CSV file.
Because OneDrive for Business is built on SharePoint Online architecture, each OneDrive is essentially a personal SharePoint site collection. Using the PnP PowerShell module allows you to loop through the site's document library and selectively export the metadata you need.
Open PowerShell as an Administrator. Run the command 'Install-Module -Name PnP.PowerShell' and press Enter. Accept any prompts to install from the untrusted repository.
Use the command 'Connect-PnPOnline -Url <UserOneDriveURL> -Interactive' to authenticate against the specific OneDrive for Business site collection.
Fetch the documents by running 'Get-PnPListItem -List "Documents" -PageSize 1000 -Fields "FileLeafRef","FileRef","Modified","Editor"' to capture the necessary metadata fields.
Pipe the results to a CSV file using the command 'Export-Csv -Path "C:\OneDriveInventory.csv" -NoTypeInformation'. You can then open this file in your spreadsheet software.
Extract File Activity Data via Microsoft Purview
If you require detailed historical data on who modified files and when, and this data is missing from standard file properties, Microsoft Purview audit logs provide comprehensive activity tracking.
Analyze Your Exported OneDrive CSV Files with WPS Spreadsheet
After successfully exporting your OneDrive for Business inventory using PowerShell or Purview, you will need a reliable spreadsheet tool to analyze the CSV data. WPS Office serves as a highly compatible, fast, and free alternative to Microsoft Office, making it effortless to organize your audit logs.
- 1. Download and Install WPS Office: Download WPS Office for free from the official website and complete the installation on your device.
- 2. Open Your Exported CSV: Launch WPS Spreadsheet, go to Menu > Open, and select your newly exported 'OneDriveInventory.csv' file.
- 3. Analyze the Inventory: Use the Data > AutoFilter tool to easily sort through file names, paths, dates, and editors within your OneDrive inventory.
Frequently Asked Questions
Why are some metadata fields missing when I open my exported CSV?
When exporting via PowerShell without explicitly specifying fields, only default properties are returned. Ensure your Get-PnPListItem command includes the '-Fields' parameter with specific names like 'Editor' or 'Modified' to capture complete details.
Can I get an inventory of OneDrive files without using PowerShell?
Yes, but with limitations. You can sync your OneDrive for Business library to your local PC using the OneDrive client, open Command Prompt, navigate to the folder, and type 'dir /s /o:n > inventory.csv'. However, this only captures local file metadata and misses SharePoint-specific data like 'Last Modified By'.
Is Microsoft Graph API an alternative for generating a file inventory?
Yes, developers can use the Microsoft Graph API by querying '/drives/{drive-id}/root/children' to fetch file and folder metadata. However, writing a script to iterate through all folders and parse the JSON output to CSV requires programming expertise.
How do I fix CSV formatting issues where all data appears in one column?
This happens when your spreadsheet software does not recognize the delimiter used in the export. Open the file in WPS Spreadsheet, select the column containing the data, navigate to the 'Data' tab, and use the 'Text to Columns' feature, selecting 'Comma' as your delimiter.
