PowerShell command to replace a chunk of text in a file

Question

I am trying to replace a chunk of text in a file using PowerShell. For instance, I have a .sql file and I know the exact chunk of an SQL script that needs to be replaced in that particular file. After reading through some of the PowerShell replace examples it looks like PowerShell returns the content of the file in an array (each line representing one entry in the array).

For example:

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 till USE [$(DatabaseName)] in the above file.

Peter Mortensen · Accepted Answer · 2019-09-01 11:51:44Z

10

Here's how I would go about this. First, when you need to get the entire contents of a file for purposes like replacing text over multiple lines, don't use Get-Content. Instead, 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'm replacing the beginning text with "foo". Also note that to get the regex used by -replace to match across newlines I prefix the regex with (?s) - single-line mode.

Mjolinor brings up a good point, in the case where the replacement text has characters that could be interpreted as regex special variables e.g. $1, $2, etc. While you can use [regex]::escape() to escape the regex there's still PowerShell code that will interpret $<something> as a variable or start of a sub-expression. In this case, it's pretty simple to work around by just capturing the part you want to keep with the -replace operator and then prepend the new text in a second step e.g.:

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

Note that in the replace in this case, I use single quotes around $1 which prevents PowerShell from interpreting any special PowerShell characters. It's kind of like a verbatim string in C#.

edited Sep 1, 2019 at 11:51

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 22, 2011 at 4:56

Keith Hill

204k44 gold badges370 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mjolinor Over a year ago

Depending on what that chunk of text is getting replaced with, that could get pretty ugly.(IMHO)

Keith Hill Over a year ago

In that case, don't bother to try to insert the new text in the replace op - just grab the trailing part to keep. Then separately prepend the text to the trailing part.

mjolinor Over a year ago

Or put the new text in a here-string, and use that as your replace argument.

stackoverflowuser Over a year ago

Saw your reply after posting mine. Pretty much the same. So you get the green tick :)

Peter Mortensen · Accepted Answer · 2019-09-01 12:17:03Z

1

I ended up doing the below. I find it easier 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)

edited Sep 1, 2019 at 12:17

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 22, 2011 at 17:20

stackoverflowuser

22.4k31 gold badges73 silver badges93 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2019-09-01 12:19:11Z

1

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

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

You can also pipe the whole thing to Out-String, but I think the first option would be a bit faster.

edited Sep 1, 2019 at 12:19

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 22, 2011 at 1:36

Andy Schneider

8,7126 gold badges38 silver badges53 bronze badges

2 Comments

Keith Hill Over a year ago

Actually specifying a ReadCount of 0 sends the entire content as one array (one line per index) down the pipeline.

Andy Schneider Over a year ago

Good to know - i thought -readcount 0 translated down to IO.FIle.ReadAllText(), Thanks Keith, good to know.

Peter Mortensen · Accepted Answer · 2019-09-01 12:10:03Z

0

Assuming your replacement text is in newscript.txt:

 $new = Get-Content newscript.txt

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

 $new | Out-File newfile.sql

edited Sep 1, 2019 at 12:10

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 22, 2011 at 2:49

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

Comments

Peter Mortensen · Accepted Answer · 2019-09-01 12:14:44Z

0

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

# 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

edited Sep 1, 2019 at 12:14

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 22, 2011 at 4:42

JPBlanc

73.1k17 gold badges137 silver badges180 bronze badges

Collectives™ on Stack Overflow

PowerShell command to replace a chunk of text in a file

5 Answers 5

4 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related