Sometimes, you might see interesting information on websites. For example, navigating to http://www.ssa.gov/OACT/babynames/index.html will open a site with popular baby names. If you'd like to work with that data, the traditional approach is to read the raw HTML from the website and parse it using regular expressions.

A completely different approach uses Invoke-WebRequest (new in PowerShell 3.0) to grab the HTML table, write it to disk and feed it to Microsoft Excel. The result is an Excel table that holds the data published by the website.

Here's a piece of sample code that illustrates how PowerShell can grab the baby names and feed them to Excel:

$URL = "http://www.ssa.gov/OACT/babynames/index.html"
$OutputFile = "$env:temp\tempfile.html"

# reading website data:
$data = Invoke-WebRequest -Uri $URL 

# get the first table found on the website and write it to disk:
@($data.ParsedHtml.getElementsByTagName("table"))[0].OuterHTML |
  Set-Content -Path $OutputFile

# open the HTML table with Excel:
$Excel = @(Resolve-Path "C:\Program Files*\Microsoft Office\Office*\EXCEL.EXE")[0].Path
& $Excel $OutputFile 

You can easily adapt this code to other websites. For example, by changing the $URL variable to http://www.x-rates.com/, you'll get current exchange rates instead of baby names. Maybe you want to do the same with your company restaurant website and their weekly menu.

Twitter This Tip! ReTweet this Tip!

Anonymous