Using Powershell for SQL Saturday Logos

Out of necessity are born the tools of laziness.  This is a good thing.  I have found that organizing and running a SQL Saturday event is a great way to create scripts and processes that make an organizers life that much easier.  The 2019 Louisville SQL Saturday helped me to create a quick script that would download all of my sponsor logos into a single folder.

The script is written in PowerShell simply because PowerShell has great versatility and it suited my needs.  The goal was to be able to download the logos and send them off to one of my volunteers who then was going to put them on signage and get them printed.At the time I had no easy way to do with without manually pulling each one off the site.

Let’s get to it!

The Script

First, I need to set some variables to make it easier to use.  I could make these into parameters for usability, however, for my needs since I only run this once a year having just variables is acceptable for me.

The event number is the number that corresponds to the particular SQL Saturday event you want to download the logos.  Note that this would work for any event, not just the one you might be organizing.

$eventNum = "883"
$outputfolder = "c:\temp\SponsorLogos"
$compress = $True

Next, I need to fetch the XML feed from the event.  The XML feed has a wealth of information in it, including the image URL for all of the sponsors.

I will also get the sponsor name, what level they sponsored at (that’s the label column) and the URL for their logo.

#let's get the XML from the SQL Saturday website
[xml] $xdoc = Invoke-WebRequest -Uri "http://www.sqlsaturday.com/eventxml.aspx?sat=$eventNum" -UseBasicParsing

#we only need a subset of each node of the XML, mainly the sponsors
$sponsors = $xdoc.guidebookxml.sponsors.sponsor | select name, label, imageURL

We want to ensure that our output folder (the path from the variable above) exists otherwise the process won’t work.  If the folder doesn’t exist, it will be created for us.

If there is an error, there is a CATCH block that will capture the error and react accordingly.

#let's make sure the folder exists
"Checking folder existence...."
if (-not (test-path $outputFolder)) {
    try {
        New-Item -Path $outputFolder -ItemType Directory -ErrorAction Stop | Out-Null #-Force
    }
    catch {
        Write-Error -Message "Unable to create directory '$outputFolder'. Error was: $_" -ErrorAction Stop
    }
    "Successfully created directory '$outputFolder'."
}else{
    "Folder already exists...moving on"
}

Now that I have a destination folder, I can begin to download the logos into the folder.   In this block, I will loop through all of the sponsors.

First, I need to do some clean up in the sponsor names.  Some sponsors have commas or “.com” in their name and I wanted to use the sponsor name as the file name so I knew who the logo belonged to.  Once the cleanup is done, I used the INVOKE-WEBREQUEST cmdlet to fetch the file from the respective URL and output the file into the destination directory.

#give me all of the logos
foreach ($sponsor in $sponsors){
    $filename = $sponsor.imageURL | split-path -Leaf

    #get the file name and clean up spaces, commas, and the dot coms
    $sponsorname = $sponsor.name.replace(" ", "").replace(",","").replace(".com","")
    invoke-webrequest -uri $sponsor.imageURL -outfile $outputfolder\$($sponsorName)_$($sponsor.label.ToUpper())_$($fileName)
}

Since I will be sending this to a volunteer to utilize, I wanted the process to automatically zip up the folder to make it easier.  I’ll name the archive the same name as the folder so I can use the SPLIT-PATH cmdlet to get the leaf leave of the directory path, which is the folder name.

Using the COMPRESS-ARCHIVE cmdlet, I can then compress the folder and put it put it into that same folder.

# zip things up if desired
If ($compress -eq $true){
    $filename = $outputfolder | split-path -Leaf
    compress-archive -path $outputFolder -DestinationPath $outputfolder\$($filename).zip
}

Finally, I wanted the process to open the folder when it was done.  This is simple accomplished by calling “explorer” along with the destination folder name.  This will launch the folder in Windows Explorer

# show me the folder
explorer $outputfolder

Summary

Powershell is a great way to quickly and easily accomplish tasks.  Whether that is working with online data or even manipulating things on your local computer, this was a quick and easy way to make my life as a SQL Saturday event organizer that much easier.

You can also find the full script on my GitHub Repository.

Enjoy!

© 2019, John Morehouse. All rights reserved.