powershell forEach loop cycling through more times than needed and starting with the wrong file-CodePudding

I am running a forEach loop in powershell to copy some SQL files to a specific folder, but I am getting some very strange output and the order of the files being copied is not making sense, hoping someone can see what I am doing wrong.

I have my files in order in a csv like such:

HelloWorld1.sql
HelloWorld2.sql
HelloWorld3.sql
HelloWorld4.sql

here is the loop:

ForEach ($column in $newfile){
    
    if($column.Extension -eq 'sql'){

        $path = $column.folder 

        $File = $column.Filename

        $path  = '\'

        $updatedPath = "C:\PathToOriginalLocation\$path$File"
    
     }

    Write-Host "copying" "$newPath\$path$File"

    echo f | xcopy /f /s /e /q /y $updatedPath "$newPath\$path"

the output I am getting looks like this

copying "C:\PathToNewLocation\HelloWorld4.sql"
copying "C:\PathToNewLocation\HelloWorld4.sql"
copying "C:\PathToNewLocation\HelloWorld1.sql" 
copying "C:\PathToNewLocation\HelloWorld2.sql"  
copying "C:\PathToNewLocation\HelloWorld2.sql"
copying "C:\PathToNewLocation\HelloWorld3.sql"
copying "C:\PathToNewLocation\HelloWorld4.sql"

can someone help me through why it is starting with number 4 twice, then going to number 1, then does number 2 twice, does number 3, and then does number 4 again?

enter image description here

Thanks All!

CodePudding user response：

The issue here is that you are running the loop in a polluted session. If you close PowerShell and run it fresh you would see different results, but since you've run the loop, and are running it again it is following your logic as you defined it.

It loops through your CSV entries, starting with the FileName = Tables line.

It runs the If statement first:

if($column.Extension -eq 'sql'){

    $path = $column.folder 

    $File = $column.Filename

    $path  = '\'

    $updatedPath = "C:\PathToOriginalLocation\$path$File"

 }

But $column.Extension does not equal 'sql', so it skips that scriptblock and moves on to run the next line:

Write-Host "copying" "$newPath\$path$File"

So, since it skipped the If scriptblock it did not define $file, $path, or $updatedPath, and it uses the last thing those were set to, which was from when the script was previously run, and the last thing that assigned them the previous run was the HelloWorld4.sql record. That is why those are showing up at the top when you run this. The exact same thing is happening on the ViewsFuncSP record, and it is repeating the variables that were set on the record before it. Clear your variables in each loop and that won't happen, but I think a better solution is to include all of your lines that you want run for a .sql file to be within the If scriptblock. Something like this:

ForEach ($column in $newfile){
    
    if($column.Extension -eq 'sql'){

        $path = $column.folder 

        $File = $column.Filename

        $path  = '\'

        $updatedPath = "C:\PathToOriginalLocation\$path$File"

        Write-Host "copying" "$newPath\$path$File"

        echo f | xcopy /f /s /e /q /y $updatedPath "$newPath\$path"
    }

Now, why it is showing HelloWorld4.sql twice, that doesn't make sense from your screenshot. I'm guessing it is related to how you are reading your CSV file, and somehow including the header row as a record. You don't show that line so it's hard to say and I can only speculate.