Scott's voterfile importing script

gistfile1.rb

#! /usr/bin/ruby

require 'rubygems'
require 'sqlite3'

files = Dir["/Volumes/BALLOT/extracted/*.txt"]


db = SQLite3::Database.new( "voterfile.db" )

columns = 'voterbase_id, tsmart_exact_track, tsmart_exact_address_track, tsmart_full_address, tsmart_city, tsmart_state, tsmart_zip, tsmart_zip4, tsmart_street_number, tsmart_pre_directional, tsmart_street_name, tsmart_street_suffix, tsmart_post_directional, tsmart_unit_designator, tsmart_secondary_number, tsmart_address_usps_address_code, tsmart_address_carrier_route, tsmart_address_dpv_confirm_code, tsmart_address_dpv_footnote, vf_source_state, vf_county_code, vf_county_name, vf_CD, vf_SD, vf_HD, vf_township, vf_ward, vf_precinct_id, vf_precinct_name, vf_county_council, vf_city_council, vf_municipal_district, vf_school_district, vf_judicial_district, tsmart_county_code, tsmart_county_name, tsmart_CD, tsmart_SD, tsmart_HD, tsmart_township, tsmart_ward, tsmart_precinct_id, tsmart_precinct_name, tsmart_county_council, tsmart_city_council, tsmart_municipal_district, tsmart_school_district, tsmart_judicial_district, tsmart_latitude, tsmart_longitude, tsmart_level, tsmart_census_id, tsmart_DMA, tsmart_DMA_Name, tsmart_place, tsmart_place_name, reg_latitude, reg_longitude, reg_levelreg_census_id, reg_dma, reg_dma_name, reg_place, reg_place_name, vf_voterfile_update_date, tsmart_address_deliverability_indicator, tsmart_address_improvement_type, tsmart_dwelling_type, vf_reg_address_1, vf_reg_address_2, vf_reg_city, vf_reg_state, vf_reg_zip, vf_reg_zip4, vf_reg_cass_address_full, vf_reg_cass_city, vf_reg_cass_state, vf_reg_cass_zip, vf_reg_cass_zip4, vf_reg_cass_street_num, vf_reg_cass_pre_directional, vf_reg_cass_street_name, vf_reg_cass_street_suffix, vf_reg_cass_post_directional, vf_reg_cass_unit_designator, vf_reg_cass_apt_num, reg_address_usps_address_code, reg_address_carrier_route, reg_address_dpv_confirm_code, reg_address_dpv_footnote, vf_mail_street, vf_mail_City, vf_mail_State, vf_mail_zip5, vf_mail_zip4, vf_mail_house_number, vf_mail_pre_direction, vf_mail_street_name, vf_mail_street_type, vf_mail_post_direction, vf_mail_apt_type, vf_mail_apt_num'.split(', ')

adds = 0



files.each do |file|

  openfile = File.new(file, "r")

  while (line = openfile.gets)
    split = line.split("\t")

	if split[0] != 'voterbase_id' # Makes sure not the first row - which contain labels
      begin
        split.pop
        data = split.map do |f| # Preparing the tab separated values for insertion into the DB
          if f[0] != '"'
            '"'+f+'"'
          else
            f
          end
        end
        data = data.join(',')
        sql = 'insert into voters ('+columns.join(', ')+') VALUES ('+data+');' #SQL for adding voters
        if db.execute( "select voterbase_id from voters WHERE voterbase_id = ?",split[0] ).count == 0 # Checks and make sure the voter isn't in the DB yet
            db.execute_batch( sql )
            puts 'Added '+split[0]
            adds += 1
        end
      rescue
        File.open('error.log', 'w') {|f| f.write(data) } # Error handling if something goes wrong
      end
    end

  end
  puts '---- Finished '+file.to_s
end