I’m trying to get some data I can play around with Tableau, fortunately information for Canadian climate data is available in CSV and XML from http://www.climate.weatheroffice.gc.ca/. I thought I’d go fetch the Vancouver daily weather data since 1953 using PowerShell.

Here’s a few PowerShell snippets for anyone interested. I haven’t cleaned these up yet, but should be pretty functional.

Script to Fetch Data

$yearfrom = 1953
$yearto = 2012
$folder = "C:TempWeather Files"
for ($i = $yearfrom; $i -le $yearto; $i++)
   $filename = "weather_$($i).csv"
   $weatherfile = Join-Path $folder $filename
   $url = "http://www.climate.weatheroffice.gc.ca/climateData/bulkdata_e.html?Prov=BC&StationID=889&Year=$($i)&Month=1&Day=1&timeframe=2&format=csv"
   $webclient = New-Object System.Net.WebClient
   $webclient.UseDefaultCredentials = $true
   $webclient.DownloadFile($url, $weatherfile)

Script to Remove Extra Lines

$path = "C:TempWeather Files*.*"
$newfolder = "C:TempWeather FilesClean"
Get-ChildItem -Path $path -Include "*.csv" |
Foreach {
   #need to delete lines 1-25, leave only data
   $file = $_
   $newfilename =  "clean_$($file.Name)"   
   $newfile = Join-Path $newfolder $newfilename
   $filecontents = Get-Content $file.FullName   
   $filecontents[25..($filecontents.length-1)] | Out-File $newfile -Encoding ascii

Script to Merge Files

$csvfolder = "C:TempWeather FilesClean"
$mergefolder = "C:TempWeather FilesMerge"
$mergedfilename = "merged_all_weather.csv"
$header = @"
"Date/Time","Year","Month","Day","Data Quality","Max Temp","Max Temp Flag","Min Temp","Min Temp Flag","Mean Temp","Mean Temp Flag","Heat Deg Days","Heat Deg Days Flag","Cool Deg Days","Cool Deg Days Flag","Total Rain (mm)","Total Rain Flag","Total Snow (cm)","Total Snow Flag","Total Precip (mm)","Total Precip Flag","Snow on Grnd (cm)","Snow on Grnd Flag","Dir of Max Gust (10s deg)","Dir of Max Gust Flag","Spd of Max Gust (km/h)","Spd of Max Gust Flag"
#put headers in final merged file
$header | Out-File $fullmergedfilename
$fullmergedfilename = Join-Path $mergefolder $mergedfilename
Get-ChildItem -Path $csvfolder | 
Get-Content |
Out-File $fullmergedfilename -Append
#open up windows explorer, let's check
explorer $mergefolder
VN:F [1.9.22_1171]
Rating: 10.0/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)