Reading a table from a text file

• MiguelSQL

  Hall of Fame

  Points: 3112

  More actions

  March 28, 2016 at 2:56 pm

  #325254

  Hello,

  There got to be a easier way.

  I'm writing a script that reads the Summary.txt file when we patch SQL.

  If the server has multiple instances, I want to read those instances from the Summary.txt file

  (yes... there are other better ways, but I want to know what instances were patched... so stay with me.

  This is the snippet of the code that I run

  $Location = "C:\Program Files\Microsoft SQL Server\"

  $SummaryFile=Get-ChildItem -Recurse -Path $Location -Filter Summary.txt | select-String -Pattern "patch" | group path

  $SummaryFile = Get-Content $SummaryFile.Name

  $a = $SummaryFile | Select-String -pattern "Updated product edition:"

  $b = $SummaryFile | Select-String -Pattern "User Input Settings:"

  $line = (($SummaryFile)[($a.LineNumber)..($b.LineNumber-3)])

  Which yields something like this:

  Instance Edition

  MSSQLSERVER ENTERPRISE

  SPDSTAGE ENTERPRISE

  Anyway I can use some CSV thing where I can simply say "select instance" ?

  I mean... I am able to do it, but it's god ugly.

  My code:

  $Location = "C:\Program Files\Microsoft SQL Server\"

  $SummaryFile=Get-ChildItem -Recurse -Path $Location -Filter Summary.txt | select-String -Pattern "patch" | group path

  $SummaryFile = Get-Content $SummaryFile.Name

  $a = $SummaryFile | Select-String -pattern "Updated product edition:"

  $b = $SummaryFile | Select-String -Pattern "User Input Settings:"

  $line = (($SummaryFile)[($a.LineNumber+1)..($b.LineNumber-3)])

  $data = $line.split(" ",[System.StringSplitOptions]::RemoveEmptyEntries)

  # $Line gets the data between lines $a.LineNumber+1) and ($b.LineNumber-3

  # $Line retrieves the instances and its licenses, somthing like this:

  # MSSQLSERVER ENTERPRISE

  # SQLINST1 STANDARD

  #

  $Results = @{}

  $j=1

  For ($i=0;$i -lt ($b.LineNumber-3 - $a.LineNumber+1);$i=$i+2)

  {

  $Results += @{"Instance $j"= $data[$i] }

  $j=$j+1

  }

  $Results

  Am I missing something?

  Help! There has to be an easier way

  Thank you

  Miguel