Solved

PowerShell Script to Update Text Files

Posted on 2014-09-16
6
334 Views
Last Modified: 2014-09-22
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.
0
Comment
Question by:dbbishop
  • 3
  • 3
6 Comments
 
LVL 15

Author Comment

by:dbbishop
ID: 40326137
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
 
LVL 3

Expert Comment

by:Phil Bossman
ID: 40327102
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
 
LVL 15

Author Comment

by:dbbishop
ID: 40330371
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 3

Expert Comment

by:Phil Bossman
ID: 40330645
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
 
LVL 3

Accepted Solution

by:
Phil Bossman earned 500 total points
ID: 40330712
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
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 40337174
Works fantasically!! Thanks.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now