JSON is very nearly valid PowerShell

JSON (JavaScript Serialized Object Notation) has become one of the standard formats for sending data by HTTP request between web browsers and other applications.

The basic types are objects, lists, strings, numbers, and nulls. All of the keys in an object must be strings.

PowerShell v3 Supports JSON

To convert a JSON string to a PowerShell object, use ConvertFrom-JSON.

Once converted, you can access individual properties of the PowerShell Object.

Including items in the array.

Manually Converting the JSON to PowerShell

Starting with the original JSON example. I’ll remove it from the string and do several edits to make it valid PowerShell.

  1. Adding an @ in from of the curly brackets makes it a hashtable
  2. I cast the hashtable to a PowerShell object by using [PSCustomObject]
  3. Replace the ‘:’ with ‘=’. Valid syntax for PowerShell hashtables
  4. We’re building up a hashtable so, remove the ‘,’ or replace the with ‘;’
  5. Change the ‘[]’ JSON array notation with ‘@()’ PowerShell array notation
  6. Add a $ to null
  7. Finally in the family property, I convert JSON Object notation to a PowerShell hashtable and cast it to an object with [PSCustomObject]

Now we can access individual properties just like before.

Converting PowerShell Objects to JSON

Coming from PowerShell or .NET we want take our objects and convert them to JSON strings so we can store them or send them over HTTP. PowerShell excels at this on many levels. Lets take our newly created list and use the building ConvertTo-JSON cmdlet to get a JSON string.

This string is ready to be sent over HTTP to an any endpoint that can handle JSON.

Love the quick one off automation you can do in PowerShell

I use markdown for lots of things. Note taking, blog posts, read me files for my GitHub repos and more.

Typically I launch MarkdownPad, start typing and then do a File|Save, navigate to the directory where I want it and save it.

Too Much Work

That workflow opens itself to lots of missteps. Eye hand coordination problems, fat fingering the directory where I want it saved, etc.

So, let’s whittle that down to 10 characters (less if you use tab completion) plus the name of the file.

New-MDFile blogEntry

The function adds the '.md' extension, then creates it, with the proper encodoing and finally does an Invoke-Item on the file name so it launches MarkdownPad with it ready for editing.

Select-String

On another note, I’ve been working with TypeScript recently. Often, I need to find text across multiple files. The typescript files are organized across directories and in subdirectories.

Rather then repeatably typing ls . -r *.ts and piping it to Select-String with the pattern you’re looking for (too much work). You create a function and combine these operations into one. The search string is optional, fts will return a list of typescript files if it is not specified.

fts showInformationMessage

Question: How would you adapt this to work with PowerShell files? C# files? Other files?

Export PowerShell Gallery Data to Excel for Analysis

You need to install the PowerShell Excel module to get this to work. Grab it from the Gallery or GitHub

The Excel

The PowerShell script creates two spreadsheets. The first spreadsheet is the scrubbed/enriched detail data from the gallery and the second sheet is a pivot table and chart showing the total number of modules published by month over the last two years.

image

More Data Wrangling with PowerShell

It’s estimated that data scientists spend 80% of their time converting data into a usable form.

Combining PowerShell’s ability for getting content from a web page and converting text to objects using the new “parse by example” functions is a potent cocktail for scrubbing and making data useable.

Let’s scrape this page for the Presidents of the United States, extract the name, plus starting and ending term. Then, we’ll used PowerShell’s Add-Member to calculate the TotalYears in office.

How to

Using Invoke-WebRequest cmdlet and the AllElements property gets content of the page. Piping the results to Where to find the the HTML element where the class attribute is equal to potus75Width (you can discover this by viewing the source of the web page).

The result of the Where is piped to ConvertFrom-String which uses the $template to parse the data by example. In the template you identify the Name, TermStart and TermEnd properties. Notice in the template you can tell ConvertFrom-String the data type you want the property to be cast to, here you cast TermStart and TermEnd to [int].

In the last step you pipe the objects that have been converted from text to ForEach so you can add a ScriptProperty named TotalYears. The Value of this property subtracts TermEnd from TermStart.

The PowerShell Code

Result

You pulled down the html from the web page, searched for specific data on that page and passed the results to ConvertForm-String with a template showing an example of how you want the data extracted. For the final step, you enriched the data by adding a property that calculates the total years in office.

image

Next Steps

Now that the data has been converted into a usable form, you can easily export it to SQL, CSV, JSON or create an Excel spreadsheet with this PowerShell Excel Module. As a bonus, this PowerShell module can create Excel spreadsheets and Excel does not need to be installed on that machine. Perfect for server side operations.

image