PowerShell command to replace a piece of text in a file

Question

I am trying to replace a piece of text in a file using PowerShell. For example, I have a .sql file, and I know the exact piece of SQL script that needs to be replaced in this particular file. After reading some examples of PowerShell replacement, it seems that PowerShell returns the contents of the file in the array (each line represents one entry in the array).

For instance:

GO :on error exit GO IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) BEGIN ALTER DATABASE [$(DatabaseName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$(DatabaseName)]; END GO PRINT N'Creating $(DatabaseName)...' GO CREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS GO USE [$(DatabaseName)] ....... ......... ..... MORE SQL SCRIPT 

I want to replace the text before USE [$ (DatabaseName)] in the above file.

Answer 1

Here's how I would go about it. First, when you need to get the entire contents of a file for purposes such as replacing text in multiple lines, do not use Get-Content . Use [IO.file]::ReadAllText() .

Then use the -replace operator, for example:

 [IO.File]::ReadAllText("$pwd\foo.sql") -replace ' '(?s).*?(USE \[\$\(DatabaseName\)\].*)$',"foo'n'$1" > foo.sql 

Here I replace the initial text with "foo". Also note that to get the regular expression used by -replace to match between newline, I add single line mode to the regular expression (?s) .

Mjolinor raises a good question when the replacement text contains characters that can be interpreted as special regular expression variables, for example, $1 , $2 , etc. Although you can use [regex]::escape() to exit the regular expression, there is still PowerShell code that will interpret $<something> as a variable or the beginning of a subexpression. In this case, it’s quite simple to work around, just grabbing the part you want to keep using the -replace operator, and then adding new text in the second step, for example:

 $keep = [IO.File]::ReadAllText("$pwd\foo.sql") -replace ' '(?s).*?(USE \[\$\(DatabaseName\)\].*)$','$1' $newText + $keep > foo.sql 

Please note that when replacing in this case I use single quotes around $1 , which prevents PowerShell from interpreting any special PowerShell characters. This is like a literal line in C #.

Answer 2

In Get-Content, you can set ReadCount to 0. This will create an array of one element. You can access it through $contents[0] :

 $contents = Get-Content file.sql -ReadCount 0 $contents[0].Replace($oldstring, $newString) 

You can also pass all this to an Out-String , but I think the first option will be a little faster.

Answer 3

I ended up doing below. It’s easier for me to understand.

 $fileContent = [System.Io.File]::ReadAllText($filePath) $toReplace = [System.Io.File]::ReadAllText($anotherPath) $afterReplace = $fileContent.Replace($toReplace,$newContent) [System.Io.Directory]::WriteAllText($filePath,$afterReplace) 

Answer 4

Assuming your replacement text is in the newscript.txt file:

  $new = Get-Content newscript.txt Get-Content file.sql | % { if ($_ -eq 'USE [$(DatabaseName)]') { $test = $true } if ($test) { $new += $_ } } $new | Out-File newfile.sql 

Answer 5

After solving the script, the command line solution. Assuming your replacement text is in the newscript.txt file:

 # Read the file $file into an array of lines PS > $file = Get-Content 'C:\temp\sql.txt' # Retrieve the line beginning the replacement PS > $begin = $file | Select-String -Pattern 'USE \[\$\(DatabaseName\)\]' PS > $begin.linenumber 17 # Selecting (in a new array) the last computed lines PS > $secondPart = $file | Select-Object -Last ($file.count - $begin.linenumber +1) PS > $secondPart USE [$(DatabaseName)] ....... ......... ..... MORE SQL SCRIPT # Creating the new file PS > $new = Get-Content newscript.txt PS > ($new + $secondPart) | Out-File newfile.sql