Solved

PowerShell Script to Update Text Files

Posted on 2014-09-16
6
332 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 3

Expert Comment

by:Phil Bossman
Comment Utility
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
Comment Utility
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
Comment Utility
Works fantasically!! Thanks.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

20 Experts available now in Live!

Get 1:1 Help Now