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:\x00円_Downloaded_Files\",
$finalPath = "U:\x01円_Verification_Passed\",
$errorsPath = "U:\x03円_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
}
}```
-
\$\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– Gregor y2022年03月15日 14:59:49 +00:00Commented Mar 15, 2022 at 14:59
1 Answer 1
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.