title: ForEach-Object removing lines from a file using Powershell
body: <p>I am putting together a simple PowerShell script that will find/replace a various number of keywords. If the keywords are found, it should replace them with a different phrase.</p>
  
  <p>This works for lines where the keywords exist, however the script is removing lines where the keywords DO NOT exist; this is not the behavior I want.</p>
  
  <p>My expectations is that it will replace KEYWORDS and leave everything else untouched.</p>
  
  <p>Script:</p>
  
  <pre><code>#*=============================================
  #* Script Name: SQLFILE_find_and_replace.ps1
  #* Created: 12-Jun-2013
  #*=============================================
  #* Purpose: Intended for SQLFILE scripts
  #* generated from a datapump dump file.
  #*
  #* Will find and replace "Create [Keyword]"
  #* with "Create or Replace [Keyword]" within a
  #* specified file.
  #*=============================================
  
  #Keywords for find/replace to act on; adjust as necessary.
  $KEYWORDS = @("PROCEDURE","PACKAGE","FUNCTION","TYPE","SEQUENCE","VIEW","DATABASE LINK","TRIGGER")
  
  # Get-FileName from http://blogs.technet.com/b/heyscriptingguy/archive/2009/09/01/hey-scripting-guy-september-1.aspx
  Function Get-FileName($initialDirectory)
  {
      [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |
      Out-Null
  
      $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
      $OpenFileDialog.initialDirectory = $initialDirectory
      $OpenFileDialog.filter = "All files (*.*)| *.*"
      $OpenFileDialog.ShowDialog() | Out-Null
      $OpenFileDialog.filename
  } #end function Get-FileName
  
  #Ask user for file
  $FILENAMELOC=Get-FileName -initialDirectory "C:\"
  
  
  Write-Progress -Activity "Loading file" -status "$FILENAMELOC" -percentComplete 0
  
  $file = (Get-Content -Path $FILENAMELOC)
  $lines =  ($file | Measure-Object)
  $lineprocessed = 0
  
  $file |
      ForEach-Object {
          $lineprocessed = $lineprocessed + 1
          ForEach ($KEYWORD in $KEYWORDS) {
              if ($_.indexof("$KEYWORD") -ge 0) {
                  if ($KEYWORD -eq "SEQUENCE" -and $_.indexof("MINVALUE") -ge 0) {
                      $SEQUENCENAME = $_.Substring($_.indexof("$KEYWORD"),$_.indexof("MINVALUE")-$_.indexof("$KEYWORD")-1)
                      $DROP = "DROP "+$SEQUENCENAME +";"
                      $CREATE = "CREATE "+$SEQUENCENAME +";"
                      $SQL = $DROP+"`r`n"+$CREATE
                      $_ -replace "CREATE $KEYWORD", $SQL
                  }
                  else {
                      $_ -replace "CREATE $KEYWORD", "CREATE OR REPLACE $KEYWORD"
                  }
              }
          }
          Write-Progress -Activity "Replacing keywords" -status "Running" -percentComplete ([System.Math]::Round((($lineprocessed / $lines.count)*100),2))
      } |
      Set-Content $FILENAMELOC
  </code></pre>
  
  <p>Test file:</p>
  
  <pre><code>This is a test.
  CREATE PROCEDURE Blah
  CREATE TRIGGER Boblawbla
  CREATE FUNCTION
  This is another test
  
  Watch me disappear.
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE Blah
  CREATE OR REPLACE TRIGGER Boblawbla
  CREATE OR REPLACE FUNCTION
  </code></pre>
  
  <p>This is a Windows 7 box.</p>