Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Reading Text Files With PowerShell For Configurations Part I


By:   |   Last Updated: 2015-06-10   |   Comments   |   Related Tips: More > PowerShell

Problem

Using text files for configurations can be especially useful in some environments, especially to avoid re-use or long arrays with many elements in them. Can we use PowerShell with a configuration text file, where we could extract the contents of the file and then use the contents line by line, add each line to an array, or use them through another logical approach?

Solution

We can use the .NET library with PowerShell and one class that is available to quickly read files is StreamReader. Provided that each line holds data, we'll look at reading each line and either returning or writing the output and then disposing the reader. We'll look at three examples of reading each line: one where we store server and instance names on each line, one where we skip every nth line, and one where we return servers based on notes we save on the same line.

Let's start by writing in ten server names in a text file called servers.txt. On each line we'll put SERVER, the number, and INSTANCE. We should have ten lines in the file with ten different server names for a simple loop test. The below function will loop through each line, add it to an array, return the array, then loop through the array. We also exclude servers three and five and Write-Host the remaining. This is one way in which we can point to a file and loop through each line of the file. If we have one thousand servers, and we add a few each month, we can simply add them to the file.

Function Get-ServersFile {
    Param(
    [string]$file
    )
    Process
    {
        $read = New-Object System.IO.StreamReader($file)
        $serverarray = @()

        while (($line = $read.ReadLine()) -ne $null)
        {
            $serverarray += $line
        }

        $read.Dispose()
        return $serverarray
    }
}

$servers = Get-ServersFile -file "C:\configurations\servers.txt"

foreach ($server in $servers)
{
    if (($server -like "*THREE*") -or ($server -like "*FIVE*"))
    {
        Write-Host "We'll exclude the third and fifth servers from the loop."
    }
    else
    {
        Write-Host $server
    }
}
instance

Let's add two more and see the output:

server one

This simple example shows that we could build on our configuration file (we could even add notes that an IF statement could read), which would allow us to loop through as many servers as we'd like. Since the naming convention of our servers could also indicate what they are, we could exclude servers with names like QA, TEST (DEV), etc. depending on our naming convention, or depending on how we want to build our configuration file.

Let's look at another simple example of skipping certain lines by their number. One technique with data, especially to prevent data˙ compromise, is generating noise or data muddying. This technique involves intentionally placing bad data into a source with a little good data for the destination to determine what to use; the destination would know what to use and what to avoid, anything that compromised it would not. The below image shows a file with 21 lines, three of which have DATA, the rest have FAKE.

fake data

Our simple reader will simply return every seventh line:

Function Read-FileSkip {
    Param(
    [string]$file
    , [int]$skip
    )
    Process
    {
        $read = New-Object System.IO.StreamReader($file)
        [int]$lineno = 1

        while (($line = $read.ReadLine()) -ne $null)
        {
            if (($lineno % $skip) -eq 0)
            {
                Write-Host $lineno.ToString() $line
            }
            $lineno++
        }

        $read.Dispose()
    }
}

Read-FileSkip -file "C:\configurations\readtwo.txt" -skip 7
data

Data muddying ("crying wolf") is both a fun and effective technique for preventing data compromises, or invasions of privacy because no data are better than wrong data. We can also use skipping lines if we organize our environment numerically - for instance, production servers are the first two items in a set of twelve, QA servers are the next three servers in a list, etc. We can then extract those names and instances by the line number they appear. Likewise, some maintenance tasks may be organized by day of the week, so the above function could be useful with a text file for maintenance tasks, where the line number would indicate the numerical day of the week.

What if we want to put notes by our server and instance names? Let's use a simple example where each line has a new server on it and after a colon, we add some notes about what it is. In the below function, we extract the notes and using the $selection parameter for the servers we want (in this case, production). This or a derivative, can be especially useful when we have thousands of servers and want to quickly access the one we need based on some factor that we have saved in the notes.

instance
Function Read-FileLine {
    Param(
    [string]$file
    , [string]$selection
    )
    Process
    {
        $read = New-Object System.IO.StreamReader($file)
        $serverlist = @()

        while (($line = $read.ReadLine()) -ne $null)
        {
            $type = $line.Substring($line.IndexOf(":"),($line.Length - $line.IndexOf(":")))

            if (($type.ToLower()) -like "*$selection*")
            {
                $serverlist += $line.Substring(0,$line.IndexOf(":"))
            }
        }

        $read.Dispose()
        return $serverlist
    }
}

Read-FileLine -file "C:\Users\Timothy Smith\Desktop\MSSQLTips\Work_In_Progress\read\readthree.txt" -selection "PRODUCTION"
server one

After we extract the servers we want, based on the notes that we mined for, we receive the array with the servers we want for other functions. Provided that we note what we need, this can save us quite a bit of time when we have many servers and we're looking for a specific one or few.

Next Steps
  • Will a configuration file save time in your environment?
  • Test the scripts with a few simple files and test calling other functions with them.
  • See this tip for an example where I use StreamReader (and StreamWriter) to read and write good and bad lines in a file.


Last Updated: 2015-06-10


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools