SQL Feather and Quill

SQL Saturdays and Twitter go together like peas and carrots. I’ve found the networking page of any SQL Saturday site is very valuable to not only get in contact with people about the event, but also to stay in contact and build long term relationships. Helping plan, organize and speak at SQLSaturday #99, I didn’t want to loose the conncections I had made, so I decided to check out all the twitter accounts of the people who had registered for for it.

What’s the easiest way to do this? POWERSHELL!!!

I stumbled a few times (one time I opened 83 tabs in Chrome and had to stop using twitter for an hour), but here is the script I came up with.

Notes on the script:

• This script will open a chrome tab for each twitter account from any (at least all I’ve tested so far) SQL Saturday Networking page.
• If Chome isn’t running, the script will open it for you, but it may open multiple instances of it.
• You can change the $url for a different SQL Saturday Networking Page
• You can set the number of tabs to open at a time – note it’s 0 relative, so 4 is actually 5
• This script creates 4 txt files in the “c:\temp” directory, but it doesn’t check for it’s existence.
• This script stores a count locally, so you will need to run it multiple times to get all the twitter users
  • i.e. if there are 83 users, the script is set to open 5 tabs, you will need to run this 17 times.
• To restart or to change the SQLSaturday URL delete the file SQLSatCounter.txt file
• Powershell Programming Examples:
  • Only get first few lines of array http://technet.microsoft.com/en-us/library/ee692806.aspx
  • Open chrome tabs…http://stackoverflow.com/questions/5668696/how-can-i-open-a-list-of-urls-on-windows
  • Add text to every line…http://stackoverflow.com/questions/4952535/add-text-to-every-line-in-text-file-using-powershell
  • Remove empty lines from a file …http://secretgeek.net/gc_blank.asp

#--------------------------------------------------------------------------------------
#Open a tab in Chrome for each twitter account on a SQLSaturday Networking Page
#Created by Andy Lohn - http://www.sqlfeatherandquill.com/
#--------------------------------------------------------------------------------------

$url = "http://www.sqlsaturday.com/99/networking.aspx" #Change this for any SQLSaturday Networking Page
[int]$StartCount = 0
[int]$PageCount = 4 #note this is 0 relative, so this plus 1 for number of tabs to be opened

$chrome = (gi ~\AppData\Local\Google\Chrome\Application\chrome.exe ).FullName
#This didn’t work on one of my machines, so I just set it to the full path of chrome and it worked.
$webclient = New-Object system.net.webclient
$file1="c:\temp\SQLSatDownload.txt"
$file2="c:\temp\SQLSatList.txt"
$file3="c:\temp\SQLSatListwithAddress.txt"
$TURL = "www.twitter.com/"
$file4="c:\temp\SQLSatCounter.txt"

#Create the files if they don't exist
If (!(Test-Path $file1) )
{New-Item $file1 -Type file}
If (!(Test-Path $file2) )
{New-Item $file2 -Type file}
If (!(Test-Path $file3) )
{New-Item $file3 -Type file}

#Set the counter to keep track for multiple runs - 0 if new
If (Test-Path $file4)
{$SCWork = (get-content $file4)
[int]$StartCount = $SCWork.ToString()}
Else
{New-Item $file4 -Type file
add-content $file4 1
[int]$StartCount = 0}

If ($StartCount -eq 0)  #Populate the local file.
{Clear-Content $file1
$webclient.DownloadFile($url,$file1)

$test = get-content $file1
Clear-Content $file2
Clear-Content $file3
$Tester = $test | Select-String -pattern "<a href=""<a href="http://www.twitter.com/"">http://www.twitter.com/"</a> |
% {$_ -replace "<span id=""ContentPlaceHolder1.*<a href="http://www.twitter.com/"">http://www.twitter.com/"</a>, ""} |
% {$_ -replace """ class=""noarrrow"">.*</span>", ""} |
% {$_ -replace "<a href="http://twitter.com/#!/"">http://twitter.com/#!/"</a>, ""} |
% {$_ -replace "                    ", ""} |
where {$_ -ne ""}

foreach ($T in $Tester)
{Add-content $file3 ($TURL + $T)}
}
$TestMethod = (Get-Content $file3)[$StartCount .. ($StartCount + $PageCount)]     $TestMethod | % { &amp; $chrome $_ }
Clear-Content $file4
Add-Content $file4 ($StartCount + $PageCount + 1)

#To restart or change url, run this:
#remove-item $file4

This has to be real easy to change for linkedin – I’m more a twitter guy at this point.  If you do alter it, to work for linkedin, let me know!