Using Powershell to remove strings in files

Published 31 October 11 10:12 PM | MartinBell

I was on a SQL 2005 site the other day, which was using a batch scripts to create their databases from files in version control. The process works well (if not that quickly!), and you can easily tell if a scripts has worked by the fact that the output file has zero length. This means you can quickly sort the output files and concentrate on the scripts which have failed. Unfortunately it was not that simple on this system because of the high occurrence of the messages like:

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table '<Some table>'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

So I decided to create a Powershell script to remove the occurrences of these strings in the output files. After a short while I ended up with this, which did the job (this may wrap!):

$Files=get-Childitem "C:\temp\database"  "*.out"  -rec;
foreach ($file in $Files) {
(get-Content $file.PSPath ) | where { $_ -inotmatch "Cannot add rows to sys.sql_dependencies"  } | Add-Content -literalPath ( $file.PSPath -replace '\.out','.txt' ) ;
remove-item -Force -Path $file.PSPath

I started off trying to replace the strings in the current file, but had issues with the file being already open. I then tried writing only lines that didn’t contain a given string and interim, and once processed I renamed this intermediate file. This was fine unless the file only contained lines that had the string in them, as they would be left alone, so I added the remove-item, and left the interim files.

If you need to check for additional string you can use the add additional checks in the where object with the –and option e.g.

$Files=get-Childitem "C:\temp\database" "*.out" -rec;
foreach ($file in $Files) {
(get-Content $file.PSPath ) | where { $_ -inotmatch "Cannot add rows to sys.sql_dependencies" -and $_ -inotmatch "The module will still be created; however, it cannot run successfully until the object exists." } | Add-Content -literalPath ( $file.PSPath -replace '\.out','.txt' ) ;
remove-item -Force -Path $file.PSPath
Filed under:


# Dew Drop – November 1, 2011 | Alvin Ashcraft's Morning Dew said on November 1, 2011 01:35 PM:

Pingback from  Dew Drop &ndash; November 1, 2011 | Alvin Ashcraft&#039;s Morning Dew

This Blog

SQL Blogs