0
\$\begingroup\$

I am trying to write a PowerShell script that will check a folder for any files and then for every file it will grab part of the filename and check it against the SQL database.

If it found a match, it will rename the file and move it to another location. If it did not find a match, then it will move the file to another location and send an email.

While the below script seems to work, I don't think it is written very well and it is definitely not error-proof.

Could I please ask the community to take a look and advise how the script could be improved?

Filename example: 8000000^Surname^Forenames^PERPDF^^20220111^^GEN^GB_PER___INI_20220111_041358.pdf

param (
    $logPath = "U:\x\Logs\$(Get-Date -format yyyy)\$(Get-Date -format MM)",
    $logName = "Verification_$(Get-Date -format yyyyMMdd).log",
    $localPath = "U:\x\00_Downloaded_Files\",
    $finalPath = "U:\x\01_Verification_Passed\",
    $errorsPath = "U:\x\03_Verification_Failed\",
    $SMTPServer = "gateway.domain.com",
    $EmailFrom = "$env:[email protected]",
    $EmailTo = "[email protected]",
    $EmailSubject = "Veryfication failed",
    $EmailAttachment = "$logPath\$logName",
    $EmailBody = "Veryfication failed.`r`nLog file attached."
)
$Files = Get-ChildItem -Path $localPath -Recurse -Name

foreach ($File in $Files)
{

$EmployeeID = $File.Split("^")[0]
$Forenames = $File.Split("^")[2]
$Surname = $File.Split("^")[1]


Write-Host "EmployeeID: $EmployeeID"
Write-Host "Forenames: $Forenames"
Write-Host "Surname: $Surname"
Write-Host $File


$SQL="SELECT [EMPLOYEEID], [SURNAME] FROM [database].[dbo].[EMPLOYEE] WHERE EMPLOYEEID = '$EmployeeID' AND [SURNAME] = '$Surname'"

        $Verification=Invoke-Sqlcmd -Query $SQL -ServerInstance “server.domain.com,42000” -database “db” -Username "user" -Password "pass" -OutVariable sqlResults

        # Success or error?
            if ($Verification -ne $Null)
            {
                Write-Output "Found match of $EmployeeID and $Surname, moving to upload"
                # Verification passed, move source file to upload
                If(!(test-path $finalPath))
                {
                    New-Item -ItemType Directory -Force -Path $finalPath
                }
                Write-Host "Removing employee name from the filename"
                $finalFileName = $finalPath + $File.Replace($Surname+"^"+$Forenames+"^", "")
                Move-Item $localPath$File $finalFileName
            }
            else
            {
                Write-Output "Verification of $EmployeeID and $Surname failed. Moving to $errorsPath"
                Move-Item $localPath$File $errorsPath
                Send-MailMessage -To $EmailTo -From $EmailFrom -Subject $EmailSubject -SmtpServer $SMTPServer -Body $EmailBody -Attachments $EmailAttachment
            }

}```
\$\endgroup\$
1
  • \$\begingroup\$ I'm not sure who you employ; however you may want to look out for Bobby Tables... and have a look at Example 3 on how to properly do SQL variables \$\endgroup\$
    – Gregor y
    Mar 15, 2022 at 14:59

1 Answer 1

2
\$\begingroup\$

For starters, your loop has data type issues. $Files is a collection of FileInfo objects. You didn't show your output but I'd assume it's null or gibberish.

#Try replacing 
$File.Split(...)
#with
$File.Name.Split(...)
#or
$File.FullName.Split(...)

The properties of the $File object contain strings. I think you're assuming that $File itself is a string.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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