Link to home
Start Free TrialLog in
Avatar of D B
D BFlag for United States of America

asked on

PowerShell Script to Update Text Files

I have a directory with well over 200 SQL scripts. Unfortunately, not all of the scripts end with a 'GO' statement.
I would like a simple script that will do two things:
1) If the last 'line' fo code is not 'GO', add it to the file, terminated with a CRLF (`r`n)
2) if there is a 'GO' and the line is not terminated, add CRLF.

The last line should be determined as anything without whitespace, since I have found some files that contain GO`r`n`r`n`r`n`r`n, in which case, leave the file as is, becasue it ends with GO followed by at least one CRLF.

The purpose for this is each file will generate a stored procedure. We have many clients who can pick and chise which procedures they want included in their 'package'. The current process is to open each one individually and execute in SSMS. A much easier procedure would be to do a multi-select of the files they want from the template directory, copy them into a temp directory and a 'COPY *.* all.sql /a' to concatenate all the files together, then execute one script.

Since not all are terminated with a GO + CRLF, the code 'runs on' In the case of a GO without CRLF at the end of the line, you end up with: 'GOCREATE PROC ...' which produces an error.

In the case of those without a GO, CREATE PROCEDURE has to be the first statement in a batch, so an error is generated.

I do not need (or want to) make changes up any file that has a GO followed by one or more CRLF's.

Also, the largest file is about 14K.
Avatar of D B
D B
Flag of United States of America image

ASKER

Also, the last line of the code may not already have CRLF, so just adding GO + CRLF could result in something like 'ENDGO', so before appending GO + CRLF, need to make sure the last two characters are CRLF, or else append `r`n + 'GO'  + `r`n
Use Get-ChildItem to iterate through the folder
Get the $contents of each file
Use regex to file match the string "GO" followed but "at least one" CRLF
If NOT found, add the CRLF "GO" CRLF to the $contents and write back to file
Write-verbose output listing the files that were modified

Get-ChildItem -Path "C:\tmp\*.sql" | ForEach-Object { 
    $FileContents = Get-Content $_.FullName -Raw
    If (  $FileContents -notmatch "GO[`r`n]+$"  ) {
        $FileContents =  $FileContents + "`r`nGO`r`n"
        Write-Verbose "Editing $($_.FullName)" -Verbose
        Set-Content -Path $_.FullName -Value $FileContents -Force
     }
}

Open in new window

Avatar of D B

ASKER

Okay, first I had to remove the -RAW parameter. It was not recognized. Second, the -NOTMATCH apparently is not workking, as it is adding to all files, even when the file ends with a GO\r\n. The top part of the attachment is the "before" code and the bottom is the "after". As you can see, there is a GO at the end of the file.

Running this code multiple times will just continually add GO to the end of the file (i.e. the -NOTMATCH never returns $false).

One final comment, as these are 'template' files, there are INSERT statements prior to the CREATE PROC statement that add data into 'driver' tables. These are always followed by a GO statement (which obviously is on a line by itself), so I need to make sure that doesn't trigger a 'false' match. I need to look at the end of the file for the literal 'GO' followed by one or more (\r\n) characters and add if not present.

I guess this is mostly a regex question than a PowerShell question. I am going to add that zone to this question.
User generated image
It seems that you are running from Powershell 2.  The -RAW parameter was introduced in Powershell 3.   IMO, you should be using Powershell 3, at minimum, unless you have an absolute requirement to run from a WinXP/Vista or Win2K3 server.  

It also looks like the regex is not working properly because the way Get-Content is working in Powershell 2. It's returning an array instead of a single stream.  Working with the array makes it harder. It ALWAYS returns true because every file has a line with something other than GO`r1n

I'll try and see if I can get a Powershell 2 version working, but it may be faster to just upgrade to Powershell 3 (at min), or simply run the script from a Win7 /  2K8 ( or higher machine ) with Powershell 3
ASKER CERTIFIED SOLUTION
Avatar of Phil Bossman
Phil Bossman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D B

ASKER

Works fantasically!! Thanks.