Pickaxe - Screen scrape directly into SQL Server tables

by breeve 14. May 2016 11:45

Pickaxe uses SQL like syntax to scrape data from web sites. It is hosted on github and is open source.

Recently, I added functionality to insert scraped results directly into SQL Server. The example below screen scrapes Lake Travis water levels and inserts the results directly into SQL server. Visit the Pickaxe download page to run the code below. The attached youtube video shows how the code was developed.

create mssql levels(month string, year int, level float)
with(
    connectionstring = 'Server=localhost;Database=scrape;Trusted_Connection=True;',
    dbtable = 'LakeLevels'
)

insert overwrite levels
select
    pick 'td:nth-child(1) p.bold' match '(\d+)-(\w+)' replace '$2',
    pick 'td:nth-child(1) p.bold' match '(\d+)-(\w+)' replace '$1',
    pick 'td:nth-child(2) p' match '\d{3}\.\d{2}'
from download page (
    select
        'http://www.golaketravis.com/waterlevel/' + pick '' take attribute 'href'
    from download page 'http://www.golaketravis.com/waterlevel/'
    where nodes = 'table[width="600"] td[style="background-color: #62ABCC;"] p.white a'
    ) with (thread(10))
where nodes = 'table[width="600"] tr'

Tags:

Software

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About Me

I am a Principal Engineer with 13 years experience developing and releasing software products. I started developing in C/C++ then moved into .NET and C# and have tech lead multiple projects. I have developed products in Windows Forms, ASP.NET/MVC, Silverlight, and WPF. I currently reside in Austin, Texas.

Own Projects

Pickaxe - An easy to use web page scraper. If you know a little SQL and how basic CSS selectors work, no web scraping product will be easier to use.

Download Page

Source Code

Created ASP.NET MVC forum originally targeting home owner associations but now in use by an investor group.

http://vtss.brockreeve.com

A language for querying PGATour golf strokes.

http://pga.brockreeve.com/

Real time bidder for car ads demo

http://cargawk.com/

Simple front end tic tac toe

http://tictac.brockreeve.com/

Currently Reading