Twitter to SQL Server with Powershell

By | February 15, 2011

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:

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


Then I create the PowerShell script:
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(“$searchTerm”))
#get rel where refresh
$linkrefresh = $ | 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(“$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

write “Inserting tweets to database.”

#iterate each item
foreach($thistitle in $myItem)
[string] $author = $
[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

#close connection
write “Completed”

#run function
Get-Tweets-ByHash ‘ENGLAND’ ‘TwitterDB’ ‘tweets’ ‘zero’ 1 16
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.

One thought on “Twitter to SQL Server with Powershell

  1. Pingback: Tweets that mention Twitter to SQL Server with Powershell | Zero1 --

Leave a Reply

Your email address will not be published. Required fields are marked *