Replace a changing string in a text file

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 + '\01-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

  • You could match just on the "SIZE = " and "FILEGROWTH =" and replace the whole line since the lines are different, but the text to be inserted is basically static. Example:

    Get-Content C:\text.txt | ForEach-Object{
    If($_ -match "SIZE\s="){
    "SIZE = 1024KB"
    }ElseIf($_ -match "FILEGROWTH\s="){
    "FILEGROWTH = 1024KB"
    }Else{
    $_
    }
    } | Set-Content C:\text.txt

     

  •  

    (get-content file.txt) -replace '(SIZE|FILEGROWTH)\s=.+','$1 = 1024KB' | set-content file.txt
    

     

  • Thanks guys for your help, however both methods truncate some text in the file.  My original file has many strings like this:

    ( NAME = N'MyDB_201212', FILENAME = N'C:\Path\MyDB_201212.ndf' , SIZE = 76288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),

    When I run: 

    $string | ForEach-Object{

        If($_ -match "SIZE\s="){

            "SIZE = 1024KB"

        }ElseIf($_ -match "FILEGROWTH\s="){

            "FILEGROWTH = 1024KB"

        }Else{

            $_

        }

    I get strings like this:

    SIZE = 1024KB

     FILEGROUP [MyDB_201212]

     

    When I run 

    $string -replace '(SIZE|FILEGROWTH)\s=.+','$1 = 1024KB'

    I get strings like this: 

    ( NAME = N'MyDB_201212', FILENAME = N'C:\Path\MyDB_201212.ndf' , SIZE = 1024KB

     

    Any ideas?

     

    Thanks.

     

  •  

    Roust_m

    My original file has many strings like this:

    ( NAME = N'MyDB_201212', FILENAME = N'C:\Path\MyDB_201212.ndf' , SIZE = 76288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB )

     

     001 
     002 
     003 
     004 
     005 
     006 
    (get-content file.txt) | foreach {
     
        ($_ -split ',' -replace '(\sSIZE|FILEGROWTH)\s=\s.+','$1 = 1024KB ' -join ',') + ')'
     
    } | set-content file.txt
     

     

  • Much betterer now, thanks. 

  • Ups, not quite, the replacement messes up some brackets.  Once I run the replacement, I get: 

    ( NAME = N'MyDB_201212', FILENAME = N'C:\SQLData\MyDB_201212.ndf' , SIZE = 76288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB ),

     FILEGROUP [MyDB_201302]

    replaced by: 

    ( NAME = N'MyDB_201212', FILENAME = N'C:\SQLData\MyDB_201212.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB , )  <-- the comma and the closing bracket swap places.

     FILEGROUP [MyDB_201302] )

    Any ideas?

  • And not only that, the replacement puts a closing bracket at the end of each line, e.g.:

    USE [master])

    IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB'))

    BEGIN)

    CREATE DATABASE [MyDB] ON  PRIMARY )

     

    FILEGROUP [MyDB_201212] )

     

    ALTER DATABASE [MyDB] ADD FILEGROUP [MyDB_201301])

    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 )

  • Get-Content -Path C:\Ephemeral\file.txt | foreach {
        $_ -replace '\s(SIZE|FILEGROWTH) = \d+KB',' $1 = 1024KB'
    } | Set-Content -Path C:\Ephemeral\file1.txt -Encoding Ascii

  • This one does not work on lines like this:

    ( NAME = N'MyDB_201302', FILENAME = N'C:\SQLDATA\MyDb_201302.ndf' , SIZE = 4608MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

    The size of the files may be in MB or even GB.

     

  • This is not uncommon when you present only a partial representation of the data.  We come up with solutions based on what you've shown us.  This happens so often I came up with a standard response (this is NOT original to this post).

    <StandardRequest rant="false" personal="false">
    In the future, please post several lines of "real" data, sanitized as
    necessary.  It's been our overwhelming experience that made up sample
    data (though it makes perfect sense in the mind of the original poster)
    is rarely ever accurate.  This results in multiple iterations trying
    to get to something that works with the real data.  This just wastes
    time and frustrates everyone.  Your cooperation is greatly appreciated.
    </StandardRequest>

  • Perhaps this will get you closer.

    Get-Content -Path C:\Ephemeral\file.txt | foreach {
        $_ -replace '\s(SIZE|FILEGROWTH) = \d+(KB|MB|GB)',' $1 = 1024KB'
    } | Set-Content -Path C:\Ephemeral\file1.txt -Encoding Ascii

  • I've ended up doing this:

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

    The space before "SIZE" is put on purpose, so it does not change the MAXSIZE.

     

    Thanks everyone for your help.

  • Here's one last variation on the replace.  In my RegEx's the MAXSIZE was not altered.

    Get-Content -Path C:\Ephemeral\file.txt | foreach {
        $_ -replace '\s(SIZE|FILEGROWTH) = \d+[KMG]B',' $1 = 1024KB'
    } | Set-Content -Path C:\Ephemeral\file1.txt -Encoding Ascii