Search code examples
filepowershellout

When Running PS1 from CMD Out-File cannot access file


When I run the following script in the Powershell ISE or regular powershell window the script runs fine and builds my .SQL query file based on my CSV (Tab Seperated) file. However If I run the same script from a CMD wrapper it fails.

$site1.rawcontent | out-file $source1 ASCII -Width 9999
$site2.rawcontent | out-file $source2 ASCII -Width 9999
$site3.rawcontent | out-file $source3 ASCII -Width 9999
$site4.rawcontent | out-file $source4 ASCII -Width 9999
$site5.rawcontent | out-file $source5 ASCII -Width 9999

$start | Out-File -filepath $target1 -append

$infile = $source1
$reader = [System.IO.File]::OpenText($infile)
$writer = New-Object System.IO.StreamWriter $file1;

$counter = 1
try {
    while (($line = $reader.ReadLine()) -ne $null)
    {
        $myarray=$line -split "\t" | foreach {$_.Trim()}
        if ($myarray[0] -Match "\d{1,4}\.\d{1,3}" -and $myarray[1] -ne {$null}){
$myarray[1] = $myarray[1] -replace "'","''"
$myarray[2] = $myarray[2] -replace "'","''"
$myarray[3] = $myarray[3] -replace "'","''"
$myarray[4] = $myarray[4] -replace "'","''"
$myarray[5] = $myarray[5] -replace "'","''"
"Insert into #terrorist Select convert(varchar(60),replace('OSFI Name: "+$myarray[1],$myarray[2],$myarray[3],$myarray[4],$myarray[5]+,"','''''','''')), no_,branch,name,surname,midname,usual,bname2 " | Out-File -filepath $target1 -append -force
If ($myarray[1] -eq "") {$myarray[1]="~"}
If ($myarray[2] -eq "") {$myarray[2]="~"}
If ($myarray[3] -eq "") {$myarray[3]="~"}
If ($myarray[4] -eq "") {$myarray[4]="~"}
If ($myarray[5] -eq "") {$myarray[5]="~"}
"from cust where cust.surname in ('"+$myarray[2]+,"','"+$myarray[1]+,"','"+$myarray[3]+,"','"+$myarray[4]+,"','"+$myarray[5]+,"') and ( name in ('"+$myarray[1]+,"', '"+$myarray[2]+,"', '"+$myarray[3]+,"', '"+$myarray[4]+,"', '"+$myarray[5]+,"') or 
midname in ('"+$myarray[1]+,"', '"+$myarray[2]+,"', '"+$myarray[3]+,"', '"+$myarray[4]+,"', '"+$myarray[5]+,"') or 
usual in ('"+$myarray[1]+,"', '"+$myarray[2]+,"', '"+$myarray[3]+,"', '"+$myarray[4]+,"', '"+$myarray[5]+,"') or 
bname2 in ('"+$myarray[1]+,"', '"+$myarray[2]+,"', '"+$myarray[3]+,"', '"+$myarray[4]+,"', '"+$myarray[5]+,"') ) 
go" | Out-File -filepath $target1 -append -force

        }   

            #$writer.WriteLine($original);

            #Write-Output  $original;
            #Write-Output  $newlin
    }
}
finally {
    $reader.Close()
    $writer.Close()
}

$end1 | Out-File -filepath $target1 -append

and it gives the following error..

Out-File : The process cannot access the file
'C:\Users\First.Last\Documents\Working\terrorist525.sql' because it is
being used by another process.
At C:\Users\First.Last\Documents\Working\OSFI.PS1:233 char:202
+ ... ual,bname2 " | Out-File -filepath $target1 -append -force
+                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Out-File], IOException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.Ou
   tFileCommand

The Terrorist525.SQl does not exist before the line $start | Out-File -filepath $target1 -append When i check the SQL files on the information from $end1 | Out-File -filepath $target1 -append makes it into the file.


Solution

  • $start | Out-File -filepath $target1 -append
    
    $infile = $source1
    $reader = [System.IO.File]::OpenText($infile)
    $writer = New-Object System.IO.StreamWriter $file1;
    $writer.Close()
    $counter = 1
    try {
    

    By adding in the $writer.close() in this specific spot it resolved the issue. Thanks @UnhandledExcepSean