This is a guest post by Guy from nullamatix.com
With the exponential growth of the Internet, data harvesting has become increasingly popular in the last few years. Several web sites sell large databases of information relevant to lawyers, doctors, businesses, schools, just about anything imaginable.
After seeing all this content, I asked myself, “How is all this information compiled?” Surely some poor sap isn’t being paid to manually insert each record. With a little research, I was able to come up with a pretty simple solution using Coldfusion.
To keep things simple, we’re going to harvest data from articles-hub.com. First, open your favorite text editor and drop in the following code:
<cfhttp url="http://www.articles-hub.com/Article/700.html" method="GET">
<cfset sDoc = trim(cfhttp.fileContent)>
This tells Coldfusion to literally get the contents of the specified page, then store that content into a variable named sDoc.
The following bit of code is where the magic happens. If you’re unfamiliar with regular expressions, now is a great time to learn. Insert the following bit of code after the variable declaration mentioned above:
<cfset regExp = '<span class="article_display_title" >
([\s\S]*?)</span>[\s\S]*?<div align=[\s\S]*?
</div>
([\s\S]*?)
</div>
</div>'>
Without going into to much detail, this variable tells Coldfusion what to look for, and where. View the source code of the page defined above and goto line 1016. You’ll notice the span tag defined in regExp is on that line. When our application is executed, Coldfusion will begin searching sDoc for that tag. Once located, the data sitting in place of the first expression ([\s\S]*?) will be defined as $1, which is the article’s title. Coldfusion continues searching, and looks over everything between:
</span>[\s\S]*?<div align=[\s\S]*?</div>
until the next expression containing the actual article content is reached. Finally, our variable stops when the two consecutive </div> tags are reached.
This information should simplify the regular expression creation process. Any set of information you’re wanting to store for later, use ([\s\S]*?). If you’re wanting to skip over anything, use [\s\S]*?.
With our data sets defined, we can output the results into a nice, organized product. Drop in this code:
<cfset q_srch = queryNew("title, article")>
<cfset start = 1>
<cfloop condition="#start#">
<cfset stResult = REfindNoCase(regExp,sDoc,start,"Yes")>
<cfif stResult.pos[1]>
<cfset queryAddRow(q_srch)>
<cfset querySetCell(q_srch,"article",mid(sDoc,stResult.pos[3],stResult.len[3]))>
<cfset querySetCell(q_srch,"title",mid(sDoc,stResult.pos[2],stResult.len[2]))>
</cfif>
<cfset start = stResult.pos[1] + stResult.len[1]>
</cfloop>
The code above tells Coldfusion to create a virtual query with two columns: title and article. Next, a starting point to loop through the results is defined. The loop is then started and begins searching sDoc with the regular expression criteria defined above. Each matching result is parsed, stored in a virtual row with the respective column, and assigned unique ID. We’re now ready to test our primitive data mining application.
Here’s how our application should look as of now:
<cfhttp url="http://www.articles-hub.com/Article/700.html" method="GET">
<cfset sDoc = trim(cfhttp.fileContent)>
<cfset regExp = '<span class="article_display_title" >
([\s\S]*?)</span>[\s\S]*?<div align=[\s\S]*?
</div>
([\s\S]*?)
</div>
</div>'>
<cfset q_srch = queryNew("title, article")>
<cfset start = 1>
<cfloop condition="#start#">
<cfset stResult = REfindNoCase(regExp,sDoc,start,"Yes")>
<cfif stResult.pos[1]>
<cfset queryAddRow(q_srch)>
<cfset querySetCell(q_srch,"article",mid(sDoc,stResult.pos[3],stResult.len[3]))>
<cfset querySetCell(q_srch,"title",mid(sDoc,stResult.pos[2],stResult.len[2]))>
</cfif>
<cfset start = stResult.pos[1] + stResult.len[1]>
</cfloop>
<cfdump var="#q_srch#">
Go ahead and save the file as miner.cfm, or whatever you’d like, and browse to that file in your web browser. For example, http://192.168.230.239:80/miner.cfm. The article’s title and content are displayed in an organized table.
Here’s a screen shot of data harvested from a site containing US College information:
Ok, that’s nice, but this information is totally useless unless we can dump it into a database, so here’s what we need to do.
After the </cfloop> tag, drop in a modified version of this code:
<cfquery name="insert_data" datasource="localdev">
INSERT article_dump(title,content) VALUES('#q_srch.title#','#q_srch.article#')
</cfquery>
The value of datasource is completely independent to each system – that just so happens to be the name of my datasource. After defining the appropriate datasource, you can either create a table with 3 columns (id, title, content) called article_dump, or us an already existing table. Just make sure to change the code where necessary. If you refresh miner.cfm in your browser, the data is not only displayed, but inserted into our database, too.
Let’s take this a step further, and automate the entire process. Go back to the top of miner.cfm and add the following code as the first line:
<cfloop from="500" to="5000" index="LoopCount">
Now replace 700.html on the second line with:
#LoopCount#.html
Scroll to the bottom and add a the closing cfloop tag to the last line:
</cfloop>
We just told Coldfusion to visit 500.html, 501.html, 502.html, 503.html, etc, until 5000.html is reached and insert each set of results into the database before moving onto the next. With this short piece of code, I’ve created databases with over 20,000 records in less than an hour, and now you can, too.
Here’s the entire final product:
<cfloop from="500" to="5000" index="LoopCount">
<cfhttp url="http://www.articles-hub.com/Article/#loopcount#.html" method="GET">
<cfset sDoc = trim(cfhttp.fileContent)>
<cfset regExp = '<span class="article_display_title" >
([\s\S]*?)</span>[\s\S]*?<div align=[\s\S]*?
</div>
([\s\S]*?)
</div>
</div>'>
<cfset q_srch = queryNew("title, article")>
<cfset start = 1>
<cfloop condition="#start#">
<cfset stResult = REfindNoCase(regExp,sDoc,start,"Yes")>
<cfif stResult.pos[1]>
<cfset queryAddRow(q_srch)>
<cfset querySetCell(q_srch,"article",mid(sDoc,stResult.pos[3],stResult.len[3]))>
<cfset querySetCell(q_srch,"title",mid(sDoc,stResult.pos[2],stResult.len[2]))>
</cfif>
<cfset start = stResult.pos[1] + stResult.len[1]>
</cfloop>
<cfquery name="insert_data" datasource="localdev">
INSERT article_dump(title,content) VALUES('#q_srch.title#','#q_srch.article#')
</cfquery>
</cfloop>
Some systems are likely to have VAT hard coded into the ecommerce systems but even advanced programs will still need to be adjusted to reflect the new rates. 