Author Posts

August 13, 2015 at 6:45 am

My function includes a search for a file, after the files extension has been renamed, it will search and log the value of the path to the newly renamed file on the first result of the foreach loop, but any files after the first will not get search and in turn does not write the path value to the DB table:

Function
`
Function NuixTruncateLongFileExtension
{
param
(

[Parameter(Position = 1, Mandatory = $true)] [string]$ServerInstance,
[Parameter(Position = 2, Mandatory = $true)] [string]$ProjDatabase,
[Parameter(Position = 3, Mandatory = $true)] [string]$exportPath,
[Parameter(Position = 4, Mandatory = $true)] [int]$extLength,
[Parameter(Position = 5, Mandatory = $true)] [string]$PsScriptPath
)

$fnName = $MyInvocation.MyCommand
Write-Verbose -Message "Function Location: $fnName"

# Load helper functions
Write-Verbose -Message "Powershell script directory: $PsScriptPath"
. "$PsScriptPath\Functions\GetQueryOutput.ps1"

$nativePath = $exportPath + "\NATIVE"
$textPath = $exportPath + "\TEXT"

$files = Get-ChildItem "$nativePath" -Recurse -File | Where-Object {$_.Extension.Length -ge $extLength}

Write-Host "Looping through the files..."
ForEach ($file in $files)
{

Write-Verbose -Message "Truncating File Extension: $file"

# Get basename of original file to use for search after it is renamed
$baseName =$file.BaseName
Write-Host "Basename: $baseName"

# Use Basename of the file to create the txt file name to search and get the txt file size
$txtFileName = $baseName + ".txt"

# Rename file extensions to x amount of characters after the (.)
$file | Rename-Item -New "$($file.basename).$($file.extension.substring(1,3))"

#Search for renamed file in directories
$renamedFileInfo = Get-ChildItem -Path "$nativePath" -Filter "$baseName.*" -Recurse
Write-Host "RenamedFile: " $renamedFileInfo.FullName

# Recursively search the Export Set text Folders for the textFileName
$txtFileInfo = Get-ChildItem -Path "$textPath" -Filter "$txtFileName" -Recurse

# Get FullName of textFileName
$textFileFullPath = $txtFileInfo.FullName

# Get values required for database inserts
$docId = $baseName
$nativePath = $renamedFileInfo.FullName
$extractedTextfileSize = (Get-Item $textFileFullPath).Length

$queryText = "insert into EXT.longFileExtension (docId, nativePath, extractedTextFileSize) Values ('$docId', '$nativePath', $extractedTextfileSize)"

Write-Verbose -Message "Running SQL Insert for docID: $docId"
Get-QueryOutput $ServerInstance $ProjDatabase $QueryText

}

}
`

My console output shows:

VERBOSE: Function Location: NuixTruncateLongFileExtension
VERBOSE: Powershell script directory: \\servername\engineering_store\Development\Powershell\DEV
Looping through the files...
VERBOSE: Truncating File Extension: H11824RE0032.001.002.msg_2laskdjfasjdhfkasjhdfkhjsadfkasjdhfkjasd
Basename: H11824RE0032.001.002
RenamedFile:  E:\EXPORT\H11824032_H11824_49\NATIVE\H11824RE003204\H11824RE0032.001.002.msg
VERBOSE: Running SQL Insert for docID: H11824RE0032.001.002
VERBOSE: Function Location: Get-QueryOutput
VERBOSE: Running query on HLPROJD01 against H52062_EDD

August 13, 2015 at 7:07 am

So it ends somewhere in the first execution of the function Get-QueryOutput. Maybe we can see something in GetQueryOutput.ps1?

August 13, 2015 at 7:15 am

Actually, the get query output runs through each file in the loop and inserts into the table, however the nativePath is missing in every row after the first insert. That final verbose meesage is the last message in the getqueryoutput function, it's not actually stuck there, it's that last thing this function is doing when the loop completes...

August 13, 2015 at 7:21 am

I ran it again and added a write to the host to show it is not stuck in the GetQueryOutput function and I gave a 3rd file for it to attempt against, the output is as follows:

VERBOSE: Function Location: NuixTruncateLongFileExtension
VERBOSE: Powershell script directory: \\servername\engineering_store\Development\Powershell\DEV
Looping through the files...
VERBOSE: Truncating File Extension: H11824RE0032.001.001.msg_1laskdjfasjdhfkasjhdfkhjsadfkasjdhfkjasd
Basename: H11824RE0032.001.001
RenamedFile:  E:\EXPORT\H11824032_H11824_49\NATIVE\H11824RE003204\H11824RE0032.001.001.msg
VERBOSE: Running SQL Insert for docID: H11824RE0032.001.001
VERBOSE: Function Location: Get-QueryOutput
VERBOSE: Running query on HLPROJD01 against H52062_EDD
Insert complete for H11824RE0032.001.001
VERBOSE: Truncating File Extension: H11824RE0032.001.002.msg_2laskdjfasjdhfkasjhdfkhjsadfkasjdhfkjasd
Basename: H11824RE0032.001.002
RenamedFile:  
VERBOSE: Running SQL Insert for docID: H11824RE0032.001.002
VERBOSE: Function Location: Get-QueryOutput
VERBOSE: Running query on HLPROJD01 against H52062_EDD
Insert complete for H11824RE0032.001.002
VERBOSE: Truncating File Extension: H11824RE0032.001.003.msg_3laskdjfasjdhfkasjhdfkhjsadfkasjdhfkjasd
Basename: H11824RE0032.001.003
Get-ChildItem : Cannot call method. The provider does not support the use of filters.
At line:44 char:28
+         $renamedFileInfo = Get-ChildItem -Path "$nativePath" -Filter "$baseName. ...
+                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotImplemented: (:) [Get-ChildItem], PSNotSupportedException
    + FullyQualifiedErrorId : NotSupported,Microsoft.PowerShell.Commands.GetChildItemCommand
 
RenamedFile:  
VERBOSE: Running SQL Insert for docID: H11824RE0032.001.003
VERBOSE: Function Location: Get-QueryOutput
VERBOSE: Running query on HLPROJD01 against H52062_EDD
Insert complete for H11824RE0032.001.003

Here is what is in the DB table:

`
ID	DocID	NativePath	ExtractedTextFileSize
1	H11824RE0032.001.001	E:\EXPORT\H11824032_H11824_49\NATIVE\H11824RE003204\H11824RE0032.001.001.msg	474
2	H11824RE0032.001.002		456
3	H11824RE0032.001.003		479

`

August 13, 2015 at 8:42 am

It looks like you are changing the value of the $nativePath variable under the "Get values required for database inserts" section. So on the 2nd iteration when the script runs "$renamedFileInfo = Get-ChildItem -Path "$nativePath" -Filter "$baseName.*" -Recurse" the $nativePath variable is still pointing to the fullname of the previous renamed file.

Iteration 1 – $nativePath = $exportPath + "\NATIVE"
Iteration 2 – $nativePath = $renamedFileInfo.FullName (from Iteration 1)
Iteration 3 – $nativePath = $null

Try using another variable name under the "Get values required for database inserts" section.

-Chris

August 13, 2015 at 11:57 am

I found the problem, I declared the nativePath variable outside of the loop, so the value was set for the first iteration through the loop, then when it came back around, the value was no longer set, I moved the variable in the loop, called it something else and that resolved it.

Function NuixTruncateLongFileExtension
{
param
(

[Parameter(Position = 1, Mandatory = $true)] [string]$ServerInstance,
[Parameter(Position = 2, Mandatory = $true)] [string]$ProjDatabase,
[Parameter(Position = 3, Mandatory = $true)] [string]$exportPath,
[Parameter(Position = 4, Mandatory = $true)] [int]$extLength,
[Parameter(Position = 5, Mandatory = $true)] [string]$PsScriptPath
)

$fnName = $MyInvocation.MyCommand
Write-Verbose -Message "Function Location: $fnName"

# Load helper functions
Write-Verbose -Message "Powershell script directory: $PsScriptPath"
. "$PsScriptPath\Functions\GetQueryOutput.ps1"

# Identify all files with extensions longer than 25 characters
$files = Get-ChildItem "$exportPath\NATIVE" -Recurse -File | Where-Object {$_.Extension.Length -ge $extLength}

Write-Verbose -Message "Looping through results of the search..."

ForEach ($file in $files)
{
$filePath = $exportPath + "\NATIVE"
$textPath = $exportPath + "\TEXT"

Write-Verbose -Message "Truncating File Extension: $file"

# Get basename of original file to use for search after it is renamed
$baseName =$file.BaseName
Write-Host "Basename: $baseName"

# Use Basename of the file to create the txt file name to search and get the txt file size
$txtFileName = $baseName + ".txt"

# Rename file extensions to x amount of characters after the (.)
$file | Rename-Item -New "$($file.basename).$($file.extension.substring(1,3))"

#Search for renamed file in directories
$renamedFileInfo = Get-ChildItem -Path "$filePath" -Filter "$baseName.*" -Recurse
Write-Host $renamedFileInfo.Name
Write-Host "RenamedFile: " $renamedFileInfo.FullName

# Recursively search the Export Set text Folders for the textFileName
$txtFileInfo = Get-ChildItem -Path "$textPath" -Filter "$txtFileName" -Recurse

# Get FullName of textFileName
$textFileFullPath = $txtFileInfo.FullName

# Get values required for database inserts
$docId = $baseName
$nativePath = $renamedFileInfo.FullName
$extractedTextfileSize = (Get-Item $textFileFullPath).Length

$queryText = "insert into EXT.longFileExtension (docId, nativePath, extractedTextFileSize) Values ('$docId', '$nativePath', $extractedTextfileSize)"

Write-Verbose -Message "Running SQL Insert for docID: $docId"
Get-QueryOutput $ServerInstance $ProjDatabase $QueryText
Write-Host "Insert complete for $docId"

}

}