How doI use Powershell to take output from a SQL query and search another file for that output

Question

This is my first time using Powershell so please forgive my ignorance.

I have a SQL query that returns back a bunch of order numbers. I want to check another file to see if there is an existing PDF in that file with the same name as the orders numbers returned by the SQL query.

Everything in my code works up until the ForEach loop which returns nothing. Based on my google searches I think I'm pretty close but I'm not sure what I'm doing wrong. Any help would be appreciated.

I've removed the actual file name for obvious reasons, and I do know that the file is correct and other commands do access it so I know that is not my problem at the moment. I've also removed sensitive info from the SQL query.

$statement = "SELECT A, Date FROM XXXX
WHERE STAT = 1 AND Date >= trunc(sysdate)"

    $con = New-Object System.Data.OracleClient.OracleConnection($connection_string)

    $con.Open()

    $cmd = $con.CreateCommand()
    $cmd.CommandText = $statement

    $result = $cmd.ExecuteReader()
    $list = while ($result.Read()) { $result["A"]}


    Write-Output $list​

    #########Loop through the list above here to check for matching PDF 

    ForEach ($Order in $list){
         Get-ChildItem "\\xxxxxx\" -Filter $Order -File

    #########If FALSE - notify that PDF is missing

   }

$con.close()

I have also tried the following code, which gets me closer and actually finds the files I'm looking for, but gives the error

" Get-ChildItem : A positional parameter cannot be found that accepts argument

• CategoryInfo : InvalidArgument: (:) [Get-ChildItem], ParameterBindingException
  • FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand"

    ForEach ($Order in $list){
         if((Get-ChildItem "\\xxxxx\" + $Order)){
            Write-Output
         } else { Write-Host "Does not exist."}

Answer 1

I gather from your comment that $list is an array of order numbers. Next, you want to check if there is a file in a folder having that name, correct?

Then I'd suggest you use Test-Path instead of Get-ChildItem:

$folderToSearch = '\\servername\sharename\folder'
foreach ($Order in $list) {
    $fileToCheck = Join-Path -Path $folderToSearch -ChildPath ('{0}.pdf' -f $Order)
    if (Test-Path -Path $fileToCheck -PathType Leaf) {
        "File found: $fileToCheck"
    }
    else {
        "File $fileToCheck does not exist"
    }
}