0

I have a record center site collection and has many doucment libraries and dataase size is 200 GB,

users will upload the pdf documnets and some pages in these documents

here i want to ge the below stats:

  1. Average number of pages per one document .more than 10mb documents and less than 10mb documents

  2. Average page size per document and average document size

can we get directly writing SQL Query or we have to use any powershell query ?

0

No, You cannot use direct SQL queries to the database (besides the fact, that it is unsupported approach by Microsoft , discussed and explained), because all files are stored in binary. Although possible, I doubt that there is an easy way how to parse PDF binary to object in SQL management studio.

But problem stays the same - PDF binary needs to be parsed anyway. One approach via using PowerShell is to connect to SharePoint library, download PDF file, process it and delete it. Parsing is provided with iTextSharp DLL.

The script below is quite simple and serves more as PoC than real production code. It needs heavy optimization and planning if needs to be executed in a huge list, multiple lists, multiple file types etc..

cls

$dllLoc = "C:\powershell\itextsharp.dll"
Add-Type -Path $dllLoc

# existing folder, where files will be downloaded, processed and deleted
$fileLoc = "C:\powershell\temp\"

$web = $null
$web = Get-SPWeb "https://contoso.com"
$web.title

$list = $null
$list = $web.lists["Test document library"]
$list.title

$pageCountArray = @()

foreach($item in $list.Items)
{
    $newFileLoc = $fileLoc + $item.File.Name

    $stream = New-Object System.IO.FileStream($newFileLoc,[System.IO.FileMode]::Create)
    $writer = New-Object System.IO.BinaryWriter($stream)
    $writer.Write($item.File.OpenBinary())
    $writer.Close()
    $stream.Close()

    $newFile = Get-ChildItem -Path $newFileLoc
    $reader = New-Object iTextSharp.text.pdf.pdfreader -ArgumentList $newFileLoc

    #
    # get info from PDF object 
    $pageCountArray += $reader.NumberOfPages

    $reader.Close();
    $newFile.Delete()
}

$averagePageSumm = 0;
foreach($i in $pageCountArray) { $averagePageSumm += $i }
$averagePageResult = [Math]::Round(([decimal]($averagePageSumm) / [decimal]($pageCountArray.Length)),2) 
Write-Host "Average page per PDF:" $averagePageResult

$web.Dispose()
Write-Host "End" -BackgroundColor DarkGreen -ForegroundColor Green

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.