Four ways to Get Report of SharePoint Folders and Files for a Library

SP_FoldersFiles_Report_02

Version: 1.0

Scenario

You might have many layers of folders insides folders and files insides those folders in a SharePoint Library. How to get a quick report of folders and files for a single Document Library?

There are different ways to get that information:

a. Using Generate File Plan Report Feature of Document Library
b. Using Mapped Drive with Dir Command
c. Using SQL Query
d. Using Power Shell

a. Using Generate File Plan Report Feature of Document Library

SharePoint has a feature in Document Library called ‘Generate File Plan Report’. Follow the steps below:

1. Go to a document library for which you need to get the list of folders and files
2. Go to Library > Library Settings
3. Click on ‘Generate File Plan Report‘ under Permissions and Management

SharePoint Document Library - Generate File Plan Report
SharePoint Document Library – Generate File Plan Report

5. On next page, Save location field will default to the same library. That is the place where the system will generate and save the report file. Click Ok
6. Report will be generated and you will see a link on next page. Click on the page to open the report in MS Excel

SharePoint Document Library - Click the link to review report
SharePoint Document Library – Click the link to review report

7. Click OK and you will back to the Document Library. You can locate the file in the same library or to a place you picked in step 5

Example Report File

b. Using Mapped Drive with Dir Command

The above report doesn’t show you the folders and files that are inside those folders. It just reports the Total # of items at each folder layer.

To get the full hierarchy of folders including files inside each level of hierarchy, you need to switch back to basics. My 1992 study of  Peter Norton’s Dos Guide came to rescue from here.

1. Copy the URL http://sharepoint.svlab.com/sites/biz1/project1/ProjectsDocLib/                                [notice I removed .aspx view file and form folder that contains the view pages]
2. Map an available drive letter on your PC say W: and use the above URL
3. Open a command prompt and go to W: drive
4. Use the command
dir /s > c:FilesFolders.txt

You can add /b switch to get rid of file / folder details and just get the files / folder names.
dir /s /b > c:FileFolders.txt

5. Open the txt file and you will see the folders and files in hierarchical structure
6. Make sure to disconnect the W: drive.

Navigating using Windows Explorer with SharePoint is not the fastest way to work with SharePoint.

c. Using SQL Query

You can use the SQL query to get the list of files and folders. Run at your own risk in a pre-production system.

— Query to list Files and Folders within Sites
— By Faisal Masood
SELECT 
Webs.Title AS ‘WebApp’,
AllLists.tp_Title AS ‘ListName’,
AllDocs.DirName AS ‘URL’,
AllDocs.LeafName AS ‘FileName’,
AllDocs.ExtensionForFile AS ‘FileType’,
AllDocs.TimeCreated
FROM AllDocs 
JOIN AllLists
ON AllLists.tp_ID = AllDocs.ListId
JOIN AllDocStreams
ON AllDocStreams.Id = AllDocs.Id
JOIN Webs
ON Webs.Id = AllLists.tp_WebId
WHERE — Webs.Title LIKE ‘%SharePoint Company Sitet%’ AND
AllDocs.DirName like ‘documents%’
–AND AllDocs.TimeCreated > ‘2010-05-10 00:00:00.000’
ORDER BY Webs.Title

c. Using Power Shell

PowerShell might be the best option to get customized report with selective information. The PowerShell code below will get show all documents under a document library names that has Documents in name and that are under site sharepoint.svlab.com site.

# List Files and Folders under a Documents Library
# By: Faisal Masood – PMP, MCSE, MCITP, MCSA
#Add SharePoint PowerShell SnapIn if not already added
 if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin “Microsoft.SharePoint.PowerShell”
}

# http://sharepoint.svlab.com/
Get-SPWeb http://sharepoint.svlab.com/ |
  Select -ExpandProperty Lists |
    Where { $_.GetType().Name -eq “SPDocumentLibrary” -and
            -not $_.Hidden } |
    Select -ExpandProperty Items |
    Where { $_.Url -like “*Documents*” } |
    Select Name, {$_.File.Length}, url

Download .ps1 PowerShell Script File

Keywords:

  • get report of files and folders in SharePoint
  • report folders and files in SharePoint
  • Four ways to get Files Folder structure
  • SQL query to get Files Folder structure
  • PowerShell to Files Folder structure
SVLogo
Visit www.softvative.com for professional services.

4 Responses

    1. @Steve
      SharePoint uses that 5000 item limit as default threshold. My guess is a, b and d options to get report will not work in that case. Option C using SQL query should work though.

Leave a Reply

Sign up for Softvative Newsletter

Get notified about new articles and deals

Receive the latest technology and leadership news and resources from us

Subscribe To Softvative Newsletter