none
Read a text file RRS feed

  • Question

  • I have created a text file Servers.txt as below:

    hostName= sql1 ; instanceName= ins1
    hostName= sql2; instanceName= ins2


    Want to iterate through each hostname and pick up it's instance name as below:

    $servers=get-content "C:\Scripts\servers.txt"

    Foreach ($sqlServer in $servers) {
     
        $sqlHostName=$sqlServer.hostName
        $sqlInstanceName=$sqlServer.instanceName
       
        }

    But the above is NOT working..

    Please advice..


    Saturday, April 16, 2011 12:54 AM

Answers

  • Get-Content reads the file one line at a time, it does not parse the lines. If you could convert this into a comma delimited file, with a header line, you could use the Import-CSV cmdlet. The text file would need to be similar to:

    hostName,instanceName
    sql1,ins1
    sql2,ins2

     

    Then the code to read this file could be similar to:

    $Servers = Import-CSV "C:\rlm\PowerShell\servers2.txt"
    ForEach ($Line In $Servers)
    {
      $sqlHostName = $Line.hostName
      $sqlInstanceName=$Line.instanceName
    }

     

    Otherwise, if you use Get-Content, you will need to parse the lines yourself, perhaps with a regular expression.

     


    Richard Mueller - MVP Directory Services
    Saturday, April 16, 2011 2:29 AM
  • Complementing Richard’s answer, here are two methods that use a Regex to parse and retrieve the values from your text file:

    # Regex pattern to match nonwhite space characters between equal sign (which

    # maybe followed by whitespace) and either a semicolon or the End of Line

    # Regex anchor '$' (which maybe preceded by whitespace)

    [Regex]$patt = '(?<=\s*)\S+(?=\s*(;|$))'

     

    # 1st method

    # quote values, build expression and invoke it to output Hashtables

    foreach ($line in Get-Content C:\Scripts\servers.txt -ReadCount 0) {

     $sqlServer = Invoke-Expression ('@{' + ($line -replace $patt,'"$0"') + '}')

     $sqlHostName = $sqlServer.hostName

     $sqlInstanceName = $sqlServer.instanceName

    }

     

    # 2nd method

    # match and extract values, then assign to variables

    Select-String -Pattern $patt -Path C:\Scripts\servers.txt -AllMatches |

     ForEach-Object {

      $sqlHostName, $sqlInstanceName = $_.Matches | ForEach-Object {$_.Value}

     }

     

     


      Robert Robelo  
    Saturday, April 16, 2011 5:17 PM
  • gc servers.txt | % {
    	$sqlHostName,$sqlInstanceName = $_ -split ";" | convertfrom-stringdata | Select-Object -ExpandProperty Values
    }	
    Saturday, April 16, 2011 5:36 PM

All replies

  • Get-Content reads the file one line at a time, it does not parse the lines. If you could convert this into a comma delimited file, with a header line, you could use the Import-CSV cmdlet. The text file would need to be similar to:

    hostName,instanceName
    sql1,ins1
    sql2,ins2

     

    Then the code to read this file could be similar to:

    $Servers = Import-CSV "C:\rlm\PowerShell\servers2.txt"
    ForEach ($Line In $Servers)
    {
      $sqlHostName = $Line.hostName
      $sqlInstanceName=$Line.instanceName
    }

     

    Otherwise, if you use Get-Content, you will need to parse the lines yourself, perhaps with a regular expression.

     


    Richard Mueller - MVP Directory Services
    Saturday, April 16, 2011 2:29 AM
  • Complementing Richard’s answer, here are two methods that use a Regex to parse and retrieve the values from your text file:

    # Regex pattern to match nonwhite space characters between equal sign (which

    # maybe followed by whitespace) and either a semicolon or the End of Line

    # Regex anchor '$' (which maybe preceded by whitespace)

    [Regex]$patt = '(?<=\s*)\S+(?=\s*(;|$))'

     

    # 1st method

    # quote values, build expression and invoke it to output Hashtables

    foreach ($line in Get-Content C:\Scripts\servers.txt -ReadCount 0) {

     $sqlServer = Invoke-Expression ('@{' + ($line -replace $patt,'"$0"') + '}')

     $sqlHostName = $sqlServer.hostName

     $sqlInstanceName = $sqlServer.instanceName

    }

     

    # 2nd method

    # match and extract values, then assign to variables

    Select-String -Pattern $patt -Path C:\Scripts\servers.txt -AllMatches |

     ForEach-Object {

      $sqlHostName, $sqlInstanceName = $_.Matches | ForEach-Object {$_.Value}

     }

     

     


      Robert Robelo  
    Saturday, April 16, 2011 5:17 PM
  • gc servers.txt | % {
    	$sqlHostName,$sqlInstanceName = $_ -split ";" | convertfrom-stringdata | Select-Object -ExpandProperty Values
    }	
    Saturday, April 16, 2011 5:36 PM
  • Nice!

    That takes the cake :)


      Robert Robelo  
    Sunday, April 17, 2011 3:39 AM
  • Hi Kazen,

    In the below script, how you are getting the path for the text file called Servers.txt?

    gc servers.txt | % {
    
    	$sqlHostName,$sqlInstanceName = $_ -split ";" | convertfrom-stringdata | Select-Object -ExpandProperty Values
    
    }	

     and used the above as is and getting the below error:

    ConvertFrom-StringData : Data line 'sql, ins1' is not in 'name=value' format.
    At C:\Scripts\Monitor-SQLServerErrorLog.ps1:39 char:72
    +     $sqlServiceName,$InstanceName = $_ -split ";" | convertfrom-stringdata <<<<  | Select-Object -ExpandProperty Values
        + CategoryInfo          : InvalidOperation: (:) [ConvertFrom-StringData], PSInvalidOperationException
        + FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.ConvertFromStringDataCommand

     

    The text file servers.txt format is as below:

    sql1,ins1
    sql2,ins2

    Please advice

    Thanks


    Tuesday, April 19, 2011 9:55 PM
  • your file is comma separated and your code splits on ; so just change the split ";" to split ","

    Wednesday, April 20, 2011 11:58 AM
  • For file ,which contains :

    sql1,ins1
    sql2,ins2

    gc servers.txt | % { $sqlHostName,$sqlInstanceName = $_ -split ","}

    Ps. But prefer using is: Import-Csv servers.txt -Header Server,Instance
    Wednesday, April 20, 2011 12:09 PM
  • Ok..I'm posting the complete script I'm working on..The script name is Monitor_Errorlog

     

    executing as belwo

    ./Monitor_Errorlog.ps1

     

    Script:

    $Servers = Import-CSV "C:\Scripts\Servers.txt"


    ForEach ($sqlhost In $Servers)
    {
     $sqlServiceName = $sqlhost.hostName
     $InstanceName=$sqlhost.instanceName
    }


    # Get the Database Engine service name of the SQL Server instance $instanceName

    if ($instanceName -eq 'MSSQLSERVER') {
        $sqlServiceName='MSSQLSERVER'    
    }
    else {
        $sqlServiceName='MSSQL$' + $instanceName    
    }

    # Only if the SQL Server instance is running, the monitoring starts.

    if ((Get-Service $sqlServiceName).Status -eq 'Running' ) {
        $query= "SELECT * FROM ERRORLOG WHERE Severity >= 16"
        $sqlNamespace= "root\Microsoft\SqlServer\ServerEvents\$instanceName"
        $selections= "ComputerName","SQLInstance", "Error","Severity","TextData"

        Notify-WMIEvent $query $sqlNamespace $selections

     

    But I'm getting the below error:

    Get-Service : Cannot find any service with service name 'MSSQL$'.

    At C:\Scripts\Monitor-ErrorLog.ps1:16 char:17
    + if ((Get-Service <<<<  $sqlServiceName).Status -eq 'Running' ) {
        + CategoryInfo          : ObjectNotFound: (MSSQL$:String) [Get-Service], ServiceCommandException
        + FullyQualifiedErrorId : NoServiceFoundForGivenName,Microsoft.PowerShell.Commands.GetServiceCommand

     

    Please advice

     

    Wednesday, April 20, 2011 5:04 PM
  • pretty obvious error, cant find that service :)

    the InstanceName is blank, so when it adds them together it ends up with just MSSQL$ which isnt a valid service.

    you probably want to handle that error with a try/catch or some other method..

    did you add column headers to your file? (first line being> hostname,instancename)

    Wednesday, April 20, 2011 5:33 PM