It turns out PowerShell is pretty fantastic.

@Kendra_Little asked: “Is there a way to search the archives of #sqlhelp? Trying to remember answer to a question i know i saw a couple months ago.”

@anonythemouse suggested she use PowerShell to search Twitter and archive the results.

I was just beginning to get interested in PowerShell so I decided to see if I could do this. I figured learning how to do this would help me learn PowerShell even if this particular script didn’t have much of a shelf life. But it might end up being very useful in itself. There might be other searches that I’d want to archive or I might decide that I don’t like some aspect of the GUI to whatever tool I’m using to interact with Twitter or I might want to pipe Twitter data into some application I’m writing.

Well, how easy is it to get Twitter search data using PowerShell? Actually, it’s incredibly easy.

I lifted this code from Windows PowerShell in Action
by Bruce Payette.

([xml](new-object net.webclient).DownloadString(
"http://blogs.msdn.com/powershell/rss.aspx"
)).rss.channel.item | format-table title,link

I had to modify it slightly to switch from RSS to Atom and I removed the formatting because I want to play around with things a bit.

([xml](new-object net.webclient).DownloadString(
"http://search.twitter.com/search.atom?q=%23sqlhelp&show_user=1&rpp=100"
)).feed.entry

So that is how to get a search of Twitter into PowerShell almost no code. (Note, you can get the URL for the Atom feed of a Twitter search by searching here and then looking for “Feed for this query” link on the results page. Other options you might want to put in your URL are described here)

Anyway, we’re not quite where I want to be. I’d like to get rid of some of the meta-data I don’t want. So we’ll step through the collection. We can use the ForEach-Object to do this. I’m going to use an alias of the ForEach-Object – %. It’s just a quick way of writing ForEach-Object. And I’ll be using a special variable $_. $_ basically refers to the current object.

([xml](new-object net.webclient).DownloadString(
"http://search.twitter.com/search.atom?q=%23sqlhelp&show_user=1&rpp=100"
)).feed.entry |
%{
	$_.title
	$_.published
	"

	"
}

I’m adding a | which means direct the output of this command into the next command. The next command is the ForEach-Object alias and then we apply the code between the {} to each object in the Atom feed we got from Twitter. I then output the title attribute which is essentially the tweet and the published attribute which tells me when the tweet was made. (The blank lines between the double quotes are just to make the output easier to read.)

Now, to get it into my database I could invoke sqlcmd or export this data to a file. In the long run I think storing it in a database would be a good idea but I don’t want to muddy the waters with that just yet. Instead I’ll export it to a file.

$file = ([xml](new-object net.webclient).DownloadString(
"http://search.twitter.com/search.atom?q=%23sqlhelp&show_user=1&rpp=100"
)).feed.entry |
%{
	
	$title = $_.title -replace ("`t"," ")
	$published = $_.published -replace ("`t"," ")
	$title + "`t" + $published
	
} 

$file  | out-file -filepath sqlhelp_twitter.txt -encoding Ascii

There you have it. A tab-delimited file of a Twitter search. It only gets the last 100 Tweets but the Twitter Search API shows you how to page through the results.

I hope to expand on this example a bit and maybe do some more interesting things with the Twitter API and the Twitter Search API. But the point is that PowerShell lets you do some pretty neat stuff in a fairly small amount of code. Reminds me of my Perl days. In fact, PowerShell seems as exciting to me as when I was first learning Perl.