QzhbBDg9

1require 'csv'
2require 'mysql2'
3
4#connect to DB
5client = Mysql2::Client.new(:host => "localhost", :username => "root", :database => "test")
6
7#enter table name of your choice
8puts "Enter a table name..."
9table_name = gets.chomp
10
11client.query("DROP TABLE IF EXISTS #{table_name}")
12
13#create table in test db
14client.query("CREATE  TABLE `test`.`#{table_name}` (
15  `pkid` INT NOT NULL AUTO_INCREMENT ,
16  `first` VARCHAR(45) NULL ,
17  `last` VARCHAR(45) NULL ,
18  `lat` VARCHAR(45) NULL ,
19  `lng` VARCHAR(45) NULL ,
20  `feet_to_fiber` INT NULL ,
21  `address` VARCHAR(145) NULL ,
22  `city` VARCHAR(45) NULL ,
23  `state` VARCHAR(45) NULL ,
24  `state_desc` VARCHAR(45) NULL ,
25  `zip` VARCHAR(145) NULL ,
26  `company` VARCHAR(145) NULL ,
27  PRIMARY KEY (`pkid`) );")
28
29#read existing data
30file=CSV.read('output-500-to-1000.csv')
31
32#insert values into table
33file.each do |row|
34	values = row.inject([]){|k,v| k<<"'#{v}'";k}.join(',')
35  	client.query("insert into test.#{table_name} (first, last, lat, lng, feet_to_fiber, address, city, state, state_desc, zip, company) values(#{values})")
36end
37
38#create new csv with headers, iterate through results from a select statement and put those into csv_object
39CSV.open('output-500-to-1000-LATEST.csv','w', :write_headers=> true, :headers => ['First Name','Last Name','Latitude','Longitude','Feet To Fiber','Address','City','State','Zip','Company']) do |csv_object|
40	client.query("select first, last, lat, lng, min(feet_to_fiber) as feet_to_fiber, address, city, state, zip, company from test.#{table_name} group by address order by feet_to_fiber desc limit 60000").each do |row|
41    	data_out = [row["first"],row["last"],row["lat"],row["lng"],row["feet_to_fiber"],row["address"],row["city"],row["state"],row["zip"],row["company"]]
42    	csv_object << data_out
43	end
44end