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.
LVL 15
dbbishopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbbishopAuthor Commented:
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
0
Phil BossmanSenior Client Systems AdminstratorCommented:
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

0
dbbishopAuthor Commented:
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.
Results.png
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Phil BossmanSenior Client Systems AdminstratorCommented:
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
0
Phil BossmanSenior Client Systems AdminstratorCommented:
It seems that in Powershell 2,  You'll need to use the .NET class to get at the RAW text data as one string.

Use IO.File to read the whole file as one stream
[IO.File]::ReadAllText( $_.FullName )

I also changed the regex expression to make sure that the final GO is on it's own line.  I'm not sure if this is an absolute requirement from a SQL perspective, but I thought it was a good idea.
"[`r`n]GO[`r`n]+$"


Get-ChildItem -Path "C:\tmp\*.sql" | ForEach-Object { 
    $FileContents = [IO.File]::ReadAllText( $_.FullName )
    If (  $FileContents -notmatch "[`r`n]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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:
Works fantasically!! Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.