0

My pages library contains folders and items inside folders.
I could open a folder and export items by clicking on 'Export to Excel' in the ribbon menu.

I don't want to open each folder and click 'Export to Excel' and merge those files into one excel.

I want to simplify the task to export all folders items into excel using powershell script or out of the box feature.

0

You can use csom power shell to export all the files from the library. make sure you have Microsoft.SharePoint.Client.Runtime.dll and Microsoft.SharePoint.Client.dll

You create a power shell file by copying the below code and make sure the client dlls and this .ps1 file are at one place.

if you want to run from your desktop. make sure you install SharePoint Server 2013 Client Components SDK

#Add - PSSnapin Microsoft.SharePoint.PowerShell  

$host.Runspace.ThreadOptions = "ReuseThread" 

 function Get-AllPages
{ 
    param ($sCSOMPath,$sSiteUrl,$sUserName,$sPassword,$sWeburl,$FileName,$ImportFile,$FileList,$LibraryName) 
    try 
    {    
        Write-Host "Getting All pages in a page library"  
        Write-Host "-----------------------------------"

         #Adding the Client OM Assemblies         
        $sCSOMRuntimePath=$sCSOMPath +  "\Microsoft.SharePoint.Client.Runtime.dll"         
        $sCSOMPathdll=$sCSOMPath +  "\Microsoft.SharePoint.Client.dll"              
        Add-Type -Path $sCSOMPathdll          
        Add-Type -Path $sCSOMRuntimePath        

        #SPO Client Object Model Context 
        $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($sSiteUrl) 
        #$spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($sUserName, $sPassword)   
        $spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)   
        $spoCtx.Credentials = $spoCredentials       

        $web = $spoCtx.Web 
        $List = $web.Lists.GetByTitle($LibraryName);

        $caml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Lookup'>0</Value></Eq></Where></Query></View>"
        $cquery = New-Object Microsoft.SharePoint.Client.CamlQuery
        $cquery.ViewXml=$caml    
        $listItems = $List.GetItems($cquery)

        $spoCtx.Load($List)
        $spoCtx.Load($listItems) 
        $spoCtx.ExecuteQuery() 

        $AllListItems = @()

        foreach ($listitem in $listItems)  
        {  
            $Title =""
            $FileType =""
            $FileRef =""
            $FileSize ="0 KB"

            If([string]::IsNullOrEmpty($listitem.FieldValues["FileLeafRef"]))
            {
                $Title = "No Title"
            }
            else
            {
                $Title =$listitem.FieldValues["FileLeafRef"]
            }

            If([string]::IsNullOrEmpty($listitem.FieldValues["File_x0020_Type"]))
            {
                $FileType = "None"
            }
            else
            {
                $FileType =$listitem.FieldValues["File_x0020_Type"]
            }

            If([string]::IsNullOrEmpty($listitem.FieldValues["FileRef"]))
            {
                $FileRef = "None" 
            }
            else
            {
                $FileRef =($sWeburl + $listitem.FieldValues["FileRef"])
            }

            If([string]::IsNullOrEmpty($listitem.FieldValues["File_x0020_Size"]))
            {
                $FileSize = "0 KB" 
            }
            else
            {
                $value = [math]::Round(($listitem.FieldValues["File_x0020_Size"]/1024),2)        
                $decimal = "{0:N2}" -f $value        
                $FileSize = ($decimal + " KB")

            }

                $AllListItems += New-Object -TypeName PSObject -Property @{

                                    SiteUrl =$sSiteUrl
                                    FileName=$Title
                                    FileType= $FileType
                                    FileSize= $FileSize
                                    FileRef= $FileRef                               
                                    CreatedBy = $listitem.FieldValues["Created_x0020_By"]
                                    ModifiedBy = $listitem.FieldValues["Modified_x0020_By"]
                                    CreatedDate = $listitem.FieldValues["Created_x0020_Date"]
                                    ModifiedDate = $listitem.FieldValues["Last_x0020_Modified"]

                                    } | Select  SiteUrl,FileName,FileType,FileSize,FileRef,CreatedBy,ModifiedBy,CreatedDate,ModifiedDate

        } 
        $AllListItems| Export-CSV ($sCSOMPath+"\"+$FileName+".csv") -NoTypeInformation -Append #-Encoding UTF8

        $spoCtx.Dispose() 
        Read-Host -Prompt "file created Successfull..! in the following path $sCSOMPath, Press any key to close this window"

    } 
    catch [System.Exception] 
    { 
        Write-Host -ForegroundColor Red $_.Exception.ToString()    
        Read-Host -Prompt "Operation failed..! Press any key to close this and re run the script -main"
    }     
} 

$LibraryName= "Documents"
$FileName = "All_Pages"        #Read-Host -Prompt "Enter File Name to write (eg: XXXXX)"
$sSiteUrl = "Site url"    #Read-Host -Prompt "Enter Site Collection URL (eg: http://Server:port/Sites/Dev)" 
$sUserName = "user id"            #Read-Host -Prompt "Enter User Name" 
$sPassword = "password"                #Read-Host -Prompt "Enter your password" -AsSecureString 


$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath

Get-AllPages -sCSOMPath $dir -sSiteUrl $sSiteUrl -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName -LibraryName $LibraryName

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.