Replace a changing string in a text file

roustam-akhmetov · July 9, 2015, 12:17pm

Hi,

I have script, which fetches a text file (which is a script of a database) into a variable. Here is a portion of that script:
$DBScriptFile = $Path[0] + $dbname + ‘1-DB-’ + $dbname + ‘-’ + $Date + ‘.sql’
$string = Get-Content $DBScriptFile

Inside the file I have strings like:
SIZE = 76288MB
FILEGROWTH = 512MB

I need to replace them with:
SIZE = 1024KB
FILEGROWTH = 1024KB

The problem is, those numbers may be different, e.g.:
SIZE = 10000KB
FILEGROWTH = 2048KB

So I need to replace everything which looks like:
SIZE = XYZB
FILEGROWTH = XYZB
with:
SIZE = 1024KB
FILEGROWTH = 1024KB

Any ideas?

Thanks

flynnbundy · July 9, 2015, 5:15pm

Hey Mate,

Give this a try:

 $x = gc .\test1.txt 
#contains the content you wish the replace
$x -replace '\d+[A-Z]B','1024KB'
#regex to look for a sequence of more than 1digit + any letter a-z followed by a B (bytes)

That makes:
SIZE = 76288MB
FILEGROWTH = 512MB

magically turn into:
SIZE = 1024KB
FILEGROWTH = 1024KB

From there you could just Out-File the contents of $x to a new file. Let me know how you go.

Cheers.

roustam-akhmetov · July 11, 2015, 1:11am

Hey Flynn, thanks for your help. The approach works with a small glitch, there is a line in the file for the database log, which has this:
SIZE = 20992KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB
So when I run the script, I get it replaced with:
SIZE = 1024KB , MAXSIZE = 1024KB , FILEGROWTH = 1024KB
I don’t want the MAXSIZE to change, so I should be getting:
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB

kvprasoon · July 11, 2015, 3:20am

Hi Akhmetov,

Try This…

$r=cat $DBScriptFile
$r|ForEach-Object{$t=$_ -split ',';$t[0]='SIZE = 1024KB';$t[2]='FILEGROWTH = 1024KB';"$($t[0]) , $($t[1]) , $($t[2])"}

Hopes Works

Regards.,
kvprasoon

roustam-akhmetov · July 11, 2015, 3:59am

Getting an error:
ForEach-Object : Array assignment failed because index ‘2’ was out of range.
At line:1 char:23

$String|ForEach-Object <<<< {$t=$_ -split ',';$t[0]='SIZE = 1024KB';$t[2]='FILEGROWTH = 1024KB';"$($t[0]) , $($t[1]) , $($t[2])"}
- CategoryInfo : InvalidOperation: (2:Int32) [ForEach-Object], RuntimeException
- FullyQualifiedErrorId : IndexOutOfRange,Microsoft.PowerShell.Commands.ForEachObjectCommand

kvprasoon · July 11, 2015, 4:20am

Can You Mention

First three lines of the .txt file ($DBScript file)

roustam-akhmetov · July 11, 2015, 5:25am

Here is the entire thing:
USE [master]
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N’MyDB’)
BEGIN
CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N’PRIMARY’, FILENAME = N’C:\SQLData\MyDB_Data.mdf’ , SIZE = 1034048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB ),
FILEGROUP [MyDB_201212]
( NAME = N’MyDB_201212’, FILENAME = N’C:\SQLData\MyDB_201212.ndf’ , SIZE = 76288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201303]
( NAME = N’MyDB_201303’, FILENAME = N’C:\SQLData\MyDB_201303.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201304]
( NAME = N’MyDB_201304’, FILENAME = N’C:\SQLData\MyDB_201304.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201305]
( NAME = N’MyDB_201305’, FILENAME = N’C:\SQLData\MyDB_201305.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201306]
( NAME = N’MyDB_201306’, FILENAME = N’C:\SQLData\MyDB_201306.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201307]
( NAME = N’MyDB_201307’, FILENAME = N’C:\SQLData\MyDB_201307.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201308]
( NAME = N’MyDB_201308’, FILENAME = N’C:\SQLData\MyDB_201308.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201309]
( NAME = N’MyDB_201309’, FILENAME = N’C:\SQLData\MyDB_201309.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201310]
( NAME = N’MyDB_201310’, FILENAME = N’C:\SQLData\MyDB_201310.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201311]
( NAME = N’MyDB_201311’, FILENAME = N’C:\SQLData\MyDB_201311.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201312]
( NAME = N’MyDB_201312’, FILENAME = N’C:\SQLData\MyDB_201312.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201401]
( NAME = N’MyDB_201401’, FILENAME = N’C:\SQLData\MyDB_201401.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201402]
( NAME = N’MyDB_201402’, FILENAME = N’C:\SQLData\MyDB_201402.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201403]
( NAME = N’MyDB_201403’, FILENAME = N’C:\SQLData\MyDB_201403.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201404]
( NAME = N’MyDB_201404’, FILENAME = N’C:\SQLData\MyDB_201404.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201405]
( NAME = N’MyDB_201405’, FILENAME = N’C:\SQLData\MyDB_201405.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201406]
( NAME = N’MyDB_201406’, FILENAME = N’C:\SQLData\MyDB_201406.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201407]
( NAME = N’MyDB_201407’, FILENAME = N’C:\SQLData\MyDB_201407.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201408]
( NAME = N’MyDB_201408’, FILENAME = N’C:\SQLData\MyDB_201408.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201409]
( NAME = N’MyDB_201409’, FILENAME = N’C:\SQLData\MyDB_201409.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201410]
( NAME = N’MyDB_201410’, FILENAME = N’C:\SQLData\MyDB_201410.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201411]
( NAME = N’MyDB_201411’, FILENAME = N’C:\SQLData\MyDB_201411.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201412]
( NAME = N’MyDB_201412’, FILENAME = N’C:\SQLData\MyDB_201412.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201501]
( NAME = N’MyDB_201501’, FILENAME = N’C:\SQLData\MyDB_201501.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201502]
( NAME = N’MyDB_201502’, FILENAME = N’C:\SQLData\MyDB_201502.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201503]
( NAME = N’MyDB_201503’, FILENAME = N’C:\SQLData\MyDB_201503.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201504]
( NAME = N’MyDB_201504’, FILENAME = N’C:\SQLData\MyDB_201504.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201505]
( NAME = N’MyDB_201505’, FILENAME = N’C:\SQLData\MyDB_201505.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201506]
( NAME = N’MyDB_201506’, FILENAME = N’C:\SQLData\MyDB_201506.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201507]
( NAME = N’MyDB_201507’, FILENAME = N’C:\SQLData\MyDB_201507.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201508]
( NAME = N’MyDB_201508’, FILENAME = N’C:\SQLData\MyDB_201508.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201509]
( NAME = N’MyDB_201509’, FILENAME = N’C:\SQLData\MyDB_201509.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201510]
( NAME = N’MyDB_201510’, FILENAME = N’C:\SQLData\MyDB_201510.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201511]
( NAME = N’MyDB_201511’, FILENAME = N’C:\SQLData\MyDB_201511.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201512]
( NAME = N’MyDB_201512’, FILENAME = N’C:\SQLData\MyDB_201512.ndf’ , SIZE = 4608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201601]
( NAME = N’MyDB_201601’, FILENAME = N’C:\SQLData\MyDB_201601.ndf’ , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201602]
( NAME = N’MyDB_201602’, FILENAME = N’C:\SQLData\MyDB_201602.ndf’ , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201603]
( NAME = N’MyDB_201603’, FILENAME = N’C:\SQLData\MyDB_201603.ndf’ , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201604]
( NAME = N’MyDB_201604’, FILENAME = N’C:\SQLData\MyDB_201604.ndf’ , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),
FILEGROUP [MyDB_201605]
( NAME = N’MyDB_201605’, FILENAME = N’C:\SQLData\MyDB_201605.ndf’ , SIZE = 1048576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 524288KB ),
FILEGROUP [MyDB_201606]
( NAME = N’MyDB_201606’, FILENAME = N’C:\SQLData\MyDB_201606.ndf’ , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ),
FILEGROUP [MyDB_201607]
( NAME = N’MyDB_201607’, FILENAME = N’C:\SQLData\MyDB_201607.ndf’ , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ),
FILEGROUP [DBSupport]
( NAME = N’DBSupportData’, FILENAME = N’C:\SQLData\MyDB_DBSupportData.ndf’ , SIZE = 1438720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
LOG ON
( NAME = N’Log’, FILENAME = N’C:\SQLLogs\MyDB_Log.ldf’ , SIZE = 20992KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
COLLATE SQL_Latin1_General_Pref_CP1_CI_AS
END

ALTER DATABASE [MyDB] ADD FILEGROUP [MyDB_201301]
ALTER DATABASE [MyDB] ADD FILEGROUP [MyDB_201302]
ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 100
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [MyDB].[dbo].[sp_fulltext_database] @action = ‘enable’
end
ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT ON
ALTER DATABASE [MyDB] SET ANSI_NULLS ON
ALTER DATABASE [MyDB] SET ANSI_PADDING ON
ALTER DATABASE [MyDB] SET ANSI_WARNINGS ON
ALTER DATABASE [MyDB] SET ARITHABORT ON
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
ALTER DATABASE [MyDB] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [MyDB] SET AUTO_SHRINK OFF
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [MyDB] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [MyDB] SET CURSOR_DEFAULT LOCAL
ALTER DATABASE [MyDB] SET CONCAT_NULL_YIELDS_NULL ON
ALTER DATABASE [MyDB] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [MyDB] SET QUOTED_IDENTIFIER ON
ALTER DATABASE [MyDB] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [MyDB] SET DISABLE_BROKER
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [MyDB] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [MyDB] SET TRUSTWORTHY OFF
ALTER DATABASE [MyDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [MyDB] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [MyDB] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [MyDB] SET HONOR_BROKER_PRIORITY OFF
ALTER DATABASE [MyDB] SET READ_WRITE
ALTER DATABASE [MyDB] SET RECOVERY SIMPLE
ALTER DATABASE [MyDB] SET MULTI_USER
ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [MyDB] SET DB_CHAINING OFF

kvprasoon · July 11, 2015, 2:27pm

I got it…Only for this file…

$r=cat $DBscriptFile
[array]$OP=@()
foreach ($line in $r)
{
   
   $t=$line -split ','
   if(($line |select-string SIZE -Quiet ) -eq $true)
   {
    $t[2]='SIZE = 1024KB';$t[4]='FILEGROWTH = 1024KB';$OP+="$($t[0]) , $($t[1]) , $($t[2]), $($t[3]), $($t[4]), $($t[5])"
   }
   else
   {
    $OP+="$line"
   }
}
$op

roustam-akhmetov · July 12, 2015, 10:28am

Having a bit of a problem. I get:
( NAME = N’MyDB_201212’ , FILENAME = N’C:\SQLData\MyDB_201212.ndf’ , SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB,
FILEGROUP [MyDB_201302]

I should be getting:
( NAME = N’MyDB_201212’ , FILENAME = N’C:\SQLData\MyDB_201212.ndf’ , SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB),
FILEGROUP [MyDB_201302]

So there is a missing bracket after FILEGROWTH = 1024KB which used to be there, but disappeared.

roustam-akhmetov · July 12, 2015, 11:01am

Removed. Error in the post.

flynnbundy · July 12, 2015, 12:19pm

Sorry I was away for a few days. uhh since we only want those specific fields you can just make two little regex’s rather than one. I’m sure a Regex pro out there will have another (better) way of doing this.

 $x = Get-Content C:\temp\x.txt

$newx = $x -replace 'SIZE = \d+[A-Z]B','SIZE = 1024KB'
$final = $newx -replace 'FILEGROWTH = \d+[A-Z]B','FILEGROWTH = 1024KB'
$final

updated to cater for MB/GB/KB

kvprasoon · July 12, 2015, 2:17pm

Hi Flynn,
Thats Great, But We Have To make Sure That , It’s in KB size all the time.

Hi Roustam-Check TIss out,

$r=cat $DBscriptFile
[array]$OP=@();$f='';$rep='';$t=''
foreach ($line in $r)
{
   
   $t=$line -split ','
   if(($line |select-string SIZE -Quiet ) -eq $true)
   {
    if(($line -match 'MyDB_+\d') -eq $true)
    {
        $F=$t[1] -split "_"
        $Rep= $F[1].substring(0,6) 
    }
    $t[2]='SIZE = 1024KB';$t[4]='FILEGROWTH = 1024KB)';$OP+="$($t[0]) , $($t[1]) , $($t[2]), $($t[3]), $($t[4]), $($t[5])"
   }
   else
   {

       if(($line -match 'MyDB_+\d') -eq $true)
        {
            $f=$line -split '_'
            $f[1]=$rep
            $OP+="$($f[0])_$($f[1])]"
        }
    $OP+="$line"

   }
}
$op

tim-curwick · July 13, 2015, 9:51am

To make it easier to write, easier to read, and easier to maintain (I know it’s a one-off, but still), I would get rid of the regex and do something like this (untested).

$Script = Get-Content $FilePath
ForEach ( $Line in $Script )
    {
    If ( $Line.Contains( 'MAXSIZE' ) )
        {
        $Line  = $Line.Substring( 0, $Line.IndexOf( "SIZE =" ))
        $Line += 'SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB '
        $Line += $Line.Substring( $Line.IndexOf( ")" ))
        }
    }
$Script | Set-Content $FilePath

roustam-akhmetov · July 13, 2015, 10:44am

This on puts additional commas after the last data file line and the only log file line, which creates a syntax error when running the script.

I get:
( NAME = N’DBSupportData’ , FILENAME = N’C:\SQLDATA\MyDB_Test_DBSupportData.ndf’ , SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB),
LOG ON
( NAME = N’Log’ , FILENAME = N’C:\SQLLOGS\MyDB_Test_Log.ldf’ , SIZE = 1024KB, MAXSIZE = 2048GB , FILEGROWTH = 1024KB),
COLLATE SQL_Latin1_General_Pref_CP1_CI_AS

I should be getting:
FILEGROUP [DBSupport]
( NAME = N’DBSupportData’, FILENAME = N’E:\SQLDATA\OLB01\Y0\MyDB_DBSupportData.ndf’ , SIZE = 1438720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
LOG ON
( NAME = N’Log’, FILENAME = N’C:\SQLLOGS\MyDB_Log.ldf’ , SIZE = 20992MB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
COLLATE SQL_Latin1_General_Pref_CP1_CI_AS

So I had to stick with one of the previous methods for now, which replaces the MAXSIZE string for the log file with incorrect value:

$string = $string -replace ‘SIZE = \d+[A-Z]B’,‘SIZE = 1024KB’
$string = $string -replace ‘FILEGROWTH = \d+[A-Z]B’,‘FILEGROWTH = 1024KB’

At least I can run the result script.

flynnbundy · July 13, 2015, 10:49am

if you just did

  -replace ’ SIZE = \d+[A-Z]B’,‘SIZE = 1024KB’

it would not replace the MAXSIZE area…

Notice the space in front of SIZE.

roustam-akhmetov · July 13, 2015, 1:52pm

2Tim Curwick: Maxsize is not always “unlimited”, sometimes MAXSIZE = 2048GB

kvprasoon · July 13, 2015, 2:41pm

Hi Roustam-Mak a note on Flynn’s Last Updtae. Its working fine and The Best Way to do It.

$x = Get-Content $DBscriptFile

$newx = $x -replace ' SIZE = \d+[A-Z]B','SIZE = 1024KB'
$final = $newx -replace 'FILEGROWTH = \d+[A-Z]B','FILEGROWTH = 1024KB'
$final

Hi Flynn-Its Great.

roustam-akhmetov · July 13, 2015, 5:17pm

Thanks guys for your help.

Topic		Replies	Views
replacing text in a txt file. PowerShell Help	5	244	May 16, 2024
replace string with database value PowerShell Help	7	291	May 16, 2024
-replace multiple text and save PowerShell Help	4	197	May 16, 2024
Search string Copy numbers at end PowerShell Help	1	175	May 16, 2024
Regex in Powershell, line by line from txt-file? PowerShell Help	28	805	May 16, 2024

Replace a changing string in a text file

Related topics