PowerShell Fuzzy Lookup

You might know about my most favourite add-in for Microsoft Excel called Fuzzy Lookup. It’s the best, most accurate and fastest tool to compare a string of text against a table of data. For example, I can have a table of songs (my entire music library of 12K+ songs), then in another table I can have a list of songs that a radio station has played over the past week – for example. With Fuzzy lookup, I can compare the two tables. Table A which contains songs played on a radio station and Table B which represents my library. For each song in the Table A, give me the closest match of a song from Table B. It works beautifully and provides me a match score so I know how different the match was.

Trying to automate this using PowerShell has been challenging. I have an example here of a PowerShell which I wrote and pieced together. Some of the PowerShell functions I got from here, but not all. The rest of the script I wrote myself…

It does the following:

  • Runs through (2 radio stations as an example) on a continuous loop and captures the now playing song from their website. It has some smarts though for each time it’s run:
    • if its the same song, skip it
    • if the song has changed, capture it and do other things, keep reading…
  • The song just played is cleaned up a little bit using some regular expressions (RegEx). The clean up process is to help with a successful match.
  • The just played song is checked against a master library of my existing songs in Azure blob storage by using the various functions to do a Fuzzy Match.
  • Once a match is found, three things are written to Azure Table Storage:
    • the score of the match
    • the song captured from the radio station exactly – the just played song
    • the result of the song matched from my library

The screenshot below is taken from a list of songs in my Azure table storage account, the songs on the left are songs just played Vs songs on the right which are songs matched from my library. I have pointed to a few examples.

As you can see, it’s pretty accurate.

2016-10-17_1909

By the way, the tool used to access Azure storage accounts (including Azure Blob / Azure Tables / Azure Files) is the free Azure Storage Explorer.

Azure PowerShell

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: