Twitter to SQL Server with Powershell

Had an idea the other day that it might be nice to be able to grab tweets based on a keyword and populate a SQL Server table. So I put together the following simple PowerShell script to do just that.

First I create “tweet” table under a database called TwitterDB:

[sql]
CREATE TABLE [dbo].[tweets](
[published] [varchar](50) NULL,
[title] [varchar](170) NULL,
[author] [varchar](100) NULL
) ON [PRIMARY]

GO
[/sql]

Then I create the PowerShell script:
[PowerShell]
Function Get-Tweets-ByHash {
#declare params
Param($sqlSource, $sqlDB, $sqlTable, $searchTerm, $startPage, $endPage)

#set variable to webclient
$page = new-object System.Net.WebClient

write “Getting tweets. Please wait…”
#get response object for twitter search
$response =[xml]($page.DownloadString(“http://search.twitter.com/search.atom?rpp=100&page=1&q=$searchTerm”))
#get rel where refresh
$linkrefresh = $response.feed.link | Where-Object {$_.rel -eq ‘refresh’}
#parse out max
$maximum = $linkrefresh.href.split(“=”)[-1]

#set start and end page for search
$startPage..$endPage | foreach { $myItem += ([xml]($page.DownloadString(“http://search.twitter.com/search.atom?rpp=100&max_id=$maximum;&page=$_;&q=$searchTerm”))).feed.entry}

#create connection object
$conn = new-object System.Data.SqlClient.SqlConnection(“Data Source=$sqlSource;Initial Catalog=$sqlDB;Integrated Security=SSPI;”)

#create command object
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn

#set command text to insert sql
$cmd.CommandText = “INSERT INTO $sqlTable (author, title, published) VALUES (@author, @title, @published)”

#add command parameters
$cmd.Parameters.Add(“@author”, [System.Data.SqlDbType]”varchar”,100)
$cmd.Parameters.Add(“@title”, [System.Data.SqlDbType]”varchar”,170)
$cmd.Parameters.Add(“@published”, [System.Data.SqlDbType]”varchar”,50)

#open connection
$conn.Open()

write “Inserting tweets to database.”

#iterate each item
foreach($thistitle in $myItem)
{
[string] $author = $thistitle.author.name
[string] $title = $thistitle.title
[string] $published = $thistitle.published

#check if title is empty
if($title -ne “”)
{
#replace strings
$title = $title.Replace(“‘”, “”)
$authorIndex = $author.indexOf(” (“)
$author = $author.substring(0,$authorIndex)

#set parameters for insert
$cmd.Parameters[“@author”].Value = “@”+$author
$cmd.Parameters[“@title”].Value = $title
$cmd.Parameters[“@published”].Value = $published

#execute insert
$cmd.ExecuteNonQuery();
}

}
#close connection
$conn.Close()
write “Completed”
}

#run function
Get-Tweets-ByHash ‘ENGLAND’ ‘TwitterDB’ ‘tweets’ ‘zero’ 1 16
[/PowerShell]
The last line calls the code with the following parameter order: Server Name, Database Name, Table Name, Search Term, Start Page, End Page.

My search returns last results for “zero” and deposits into the tweet table as shown here:

Substitute those options for your own search. Make sure the account running the script has privileges on the table as this will use Integrated Authentication.

Pretty basic example. Feel free to modify and re-distribute. Credit is always appreciated.