Text extraction of document files

Question

I have this script that pulls text out of .docx, .doc and .pdf files and uploads that text to an Azure SQL Server. This is so users can search on the contents of those documents without using Windows Search / Azure Search.

The filenames are all in the following format:

firstname surname - id.extension

The id is incorrect though, the ID is from an outdated database and the new database that I am updating holds both (newID and oldID).

COLUMNS:

• ID - New ID of the candidate record
• OldID - Old ID of the candidate record (old database schema)
• OriginalResumeID - Document link ID for the candidate table to the document table
• CachedText - The field I am updating (holds the document text) at the moment this will mostly be NULL

Here is the script:

## Get resume list
$params = @{
    'Database' = $TRIS5DATABASENAME
    'ServerInstance' = $($AzureServerInstance.FullyQualifiedDomainName)
    'Username' = $AdminLogin
    'Password' = $InsecurePassword
    'query'    = "SELECT id, OldID, OriginalResumeID FROM Candidate WHERE OriginalResumeID IS NOT NULL"
}
$IDCheck = Invoke-Sqlcmd @params

## Word object
$files = Get-ChildItem -force -recurse $documentFolder -include *.doc, *.pdf, *.docx
$word = New-Object -ComObject word.application
$word.Visible = $false
$saveFormat = [Enum]::Parse([Microsoft.Office.Interop.Word.WdSaveFormat], "wdFormatText")

foreach ($file in $files) {
    Write-Output "Processing: $($file.FullName)"
    $doc = $word.Documents.Open($file.FullName)
    $fileName = $file.BaseName + '.txt'
    $doc.SaveAs("$env:TEMP\$fileName", [ref]$saveFormat)
Write-Output "File saved as $env:TEMP\$fileName"
    $doc.Close()

    $4ID = $fileName.split('-')[1].replace(' ', '').replace(".txt", "")
    $text = Get-Content "$env:TEMP\$fileName"
    $text = $text.replace("'", "''")

    $resumeID = $IDCheck | where {$_.OldID -eq $4id} | Select-Object OriginalResumeID
    $resumeID = $resumeID.OriginalResumeID

    <# Upload to azure #>
    $params = @{
        'Database' = $TRIS5DATABASENAME
        'ServerInstance' = $($AzureServerInstance.FullyQualifiedDomainName)
        'Username' = $AdminLogin
        'Password' = $InsecurePassword
        'query'    = "Update Document SET CachedText = '$text' WHERE id = $ResumeID"
    }
    Invoke-Sqlcmd @params -ErrorAction "SilentlyContinue"
    Remove-Item -Force "$env:TEMP\$fileName"
}

$word.Quit()

The problem is that running this on a large dataset, let's say 750000 documents takes far too long per document. I'm fairly certain that this is because it has to search through the entire $IDCheck object of 750000 records before it can get the originalResumeID of the record to upload to.

Running this on a smaller database is quite quick (around 200000 per 24 hours). I was thinking I could check the documents table and only pull rows where the CachedText field is null and loop that to run every 50000 documents so it would get quicker as it goes. Problem is the documents table will be massive and will take a long time to search through every time this is called.

Any help on speeding this up would be much appreciated.

EDIT:

Looks like it is the upload to azure causing the delay:

<# Upload to azure #>
$params = @{
    'Database' = $TRIS5DATABASENAME
    'ServerInstance' = $($AzureServerInstance.FullyQualifiedDomainName)
    'Username' = $AdminLogin
    'Password' = $InsecurePassword
    'query'    = "Update Document SET CachedText = '$text' WHERE id = $ResumeID"
}
Invoke-Sqlcmd @params -ErrorAction "SilentlyContinue"

Answer 1

I would try to use bulkcopy to load all of the IDs and their CachedText at once into a staging table in Azure, and then do a single update on your document table.

 CREATE TABLE document
(docKey     BIGINT IDENTITY(1, 1) PRIMARY KEY, 
 CachedText NVARCHAR(MAX), 
 id         INT
);
CREATE TABLE document_stage
(CachedText NVARCHAR(MAX), 
 id         INT
);

As you iterate over the files, you create a PSObject with the properties you want in your sql table and add it to an collection. Then after all files are done, or at set batching limits you can use Out-DataTable to convert the collection into a data table, and then let SqlBulkCopy upload to the stage table in one batch, and a single UPDATE will update your primary table.

UPDATE Document
  SET 
      CachedText = stg.CachedText
FROM document_stage stg
WHERE document.id = stg.id;

PS script

$files = Get-ChildItem -force -recurse $documentFolder -include *.doc, *.pdf, *.docx

$stagedDataAsArray = @()
foreach ($file in $files) {

    $fileName = $file.name
    $4ID = $fileName.split('-')[1].replace(' ', '').replace(".txt", "")
    $text = Get-Content "$($file.FullName)"
    $text = $text.replace("'", "''")


    $resumeID = $IDCheck | where {$_.OldID -eq $4id} | Select-Object OriginalResumeID
    $resumeID = $resumeID.OriginalResumeID

    <# create the row and add it to our  #>
     $fileInstance = New-Object -TypeName psobject 
    $fileInstance | add-member -type NoteProperty -Name cachedText -Value $text
    $fileInstance | add-member -type NoteProperty -Name resumeID -Value $resumeID
    $stagedDataAsArray += $fileInstance
    Remove-Item -Force "$env:TEMP\$fileName"

    }
    $stagedDataAsTable = $stagedDataAsArray | Out-DataTable
    $cn = new-object System.Data.SqlClient.SqlConnection("YOUR AZURE DB CONNECTION STRING");
    $cn.Open()
    $bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
    $bc.DestinationTableName = "dbo.document_stage"
    $bc.WriteToServer($stagedDataAsTable)
    $cn.Close()


    $params = @{
        'Database' = $TRIS5DATABASENAME
        'ServerInstance' = $($AzureServerInstance.FullyQualifiedDomainName)
        'Username' = $AdminLogin
        'Password' = $InsecurePassword
        'query'    = "UPDATE Document
  SET 
      CachedText = stg.CachedText
FROM document_stage stg
WHERE document.id = stg.id;"
    }
    Invoke-Sqlcmd @params -ErrorAction "SilentlyContinue"