homefairparser

gistfile1.txt

require 'nokogiri'
require 'open-uri'
require 'sqlite3'



def createcolumns(categories)

  categories.each do |field|	
	db.execute ("ALTER TABLE hf ADD COLUMN #{field} {FLOAT};")
	end
end


#creates the columns
def columns(zip)

	db = SQLite3::Database.open( "data.db" )

	url = "http://www.homefair.com/real-estate/city-profile/details.asp?format=popup&Zip=10128&SectionID=2&SectionName=Population#Population"	

	data = Nokogiri::HTML(open(url))
	
	#gets the category titles
	categories = data.xpath("//table/tr[position() > 1]/td[1]").map{ |node| node.text.gsub(/\s+/, "_").gsub("-", "_").gsub("+", "plus").gsub(":", ""); }
	puts categories	
	categories.each do |field|	
	
		
		unless field == ""
			begin
				if db.execute ("SELECT #{field} from hf;")
				puts "#{field} already exists"
				end
			rescue 
			db.execute ("ALTER TABLE hf ADD COLUMN #{field};")
			puts "#{field} Succesfully added"
			end
		end
	end
	#gets the data of the Zipcode only, no headers
	#rows = data.xpath("//table/tr[position() > 1]/td[2]").text
 
end


zip = '10128'
columns(zip)