Confirm existence of subdirectories

Question

I am writing a script to query a SQL server to aggregate data that I can use to search through various directories. If the folder does not exist it should write the path that does not exist to a text file at the end of the code. I created an array to prefill the strings of the paths which are completed by using the SQL data. I'm coming into the issue of input sting was not in a correct format and the issue of it not correctly filling the text document. It will fill the text document with folders that both do and do not exist. I have tried various configurations. I believe the array could be my issue but I am currently unsure.

Error is as follows:

Cannot convert value "Walker" to type "System.Int32". Error: "Input string was not in a correct format."
At \\cottonwood\users\C.B\My Documents\Untitled1.ps1:36 char:115
+ ... Address)#> + $($Row.'Last Name') + $array[$i]
+                    ~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvalidCastFromStringToInteger

Code is as follows:

$SQLServer = "REDWOOD" #use Server\Instance for named SQL instances! 
$SQLDBName = "MARS"
$SqlQuery = "select Account, IsActive, [Last Name] FROM vw_loans WHERE ( Account NOT IN ('100040A','100041A','100044A','100044B','100044C','100079A','100040A','100041A','100044A','100044B','100044C','100079A','100153B','100413B')) AND LEFT(Account,1)<>'_' AND (Account NOT like '%B%') AND (LoanStatus != 'PRELIM') ORDER BY Account"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)


$SqlConnection.Close()

clear

$DataSet.Tables[0]

$array =   "\I. Originations\Final Originations Package","\II. Servicing\A. Communications", "\II. Servicing\B. Foreclosure Documents","\II. Servicing\C. Bankruptcy Documents", "\II. Servicing\D. Amendments & Extensions", "\II. Servicing\E. Property", "\II. Servicing\F. Previous Servicer Data", "\III. Loan Documents", "\IV. Taxes, Insurance, HOA\HOA", "\IV. Taxes, Insurance, HOA\Insurance","\IV. Taxes, Insurance, HOA\Insurance\PMI","\IV. Taxes, Insurance, HOA\Taxes"

foreach ($Row in $dataset.Tables[0].Rows) 
{ 

   for($i=0;$i -lt $array.Length; $i++)
    { 
     if($Row.IsActive -eq $True)      
        {
              $CorrPath = "U:\Shared\Loan Documents - Active\" + $($Row.Account) + " - " + <#$(Row.Address)#> + $($Row.'Last Name') + $array[$i]
        }    

     if($Row.IsActive -eq $False)
         {
           $CorrPath = "U:\Shared\Loan Documents - Inactive\" + $($Row.Account) + " - " + <#$(Row.Address)#> + $($Row.'Last Name') + $array[$i]
         }

    $FileExist = Test-Path $CorrPath

    If($FileExist -eq $False) 
    {Add-Content $Corrpath -Path "\\cottonwood\users\IT\Missing Folder Location\MissingSubFolders.txt"}

    }



}

If you are getting error messages, please include the complete and exact text thereof in your question - often, with PowerShell, the meaning of the message is not immediately obvious, but an experienced PowerShell coder can decipher them and pinpoint the error, as the message almost always gives complete information. — Jeff Zeitlin, Jun 29 '17 at 20:04

score 1 · Answer 1 · answered Jun 29 '17 at 20:20

Looks to me like the parser is confused by what the + operator is supposed to be doing. It's trying to add when it should be concatenating. Try forcing everything to a string:

if($Row.IsActive) {
    $CorrPath = "U:\Shared\Loan Documents - Active\" + $($Row.Account.ToString()) + " - " + <#$(Row.Address)#> + $($Row.'Last Name'.ToString()) + $array[$i].ToString()
} else {
    $CorrPath = "U:\Shared\Loan Documents - Inactive\" + $($Row.Account.ToString()) + " - " + <#$(Row.Address)#> + $($Row.'Last Name'.ToString()) + $array[$i].ToString()
}

Or else try formatting the filename differently:

$CorrPath = "U:\Shared\Loan Documents - Active\{0} - {1}{2}" -f $Row.Account,$Row.'Last Name',$array[$i]

I have tried the modifications that you have recommended to no avail. If you can, can you direct message me so that I may inquire further? — Cameron B, Jun 30 '17 at 00:02

Confirm existence of subdirectories

1 Answers1