I have a csv of zip code and time zone like so.
array_zipcodes =
‘ZIPCode,ZIPType,CityName,CityType,CountyName,CountyFIPS,StateName,StateAbbr,StateFIPS,MSACode,AreaCode,TimeZone,UTC,DST,Latitude,Longitude
00501,U,Holtsville,D,Suffolk,36103,New York,NY,
36,5380,631,Eastern,-5.0,Y, 40.813078, -73.046388
00501,U,I R S Service Center,N,Suffolk,36103,New York,NY,
36,5380,631,Eastern,-5.0,Y, 40.813078, -73.046388
00544,U,Holtsville,D,Suffolk,36103,New York,NY,
36,5380,631,Eastern,-5.0,Y, 40.813223, -73.049288
00544,U,IRS Service Center,N,Suffolk,36103,New York,NY,
36,5380,631,Eastern,-5.0,Y, 40.813223, -73.049288’
I want a hash like
zip_to_UTC = [
00501 => -5,
00544 => -5,]
Then I am interacting with salesforce API to grab customer data and
figure out all their zip codes and put them through this to generate
SQL update statement.
for your pleasure if you want to know how to grab salesforce data. I
made my own hash for State to UTC offset, but I need it to be zip to
UTC as that is more accurate. Any help very much appreciated.
require ‘rubygems’
require ‘activerecord’
require ‘activesalesforce’
require ‘fastercsv’ ## this is what I have so far.
FIELDS = [ “ZIPCode”,“UTC”]
csv_data = FasterCSV.read(“5-digit Commercial.csv”)
class Account < ActiveRecord::Base
end
module DiiTimeZone
module SF
class TZLookup
def initialize(username, password)
ActiveRecord::Base.establish_connection({:adapter =>
‘activesalesforce’, :url => ‘https://www.salesforce.com/services/Soap/
u/7.0’,
:username =>
username, :password => password})
@connection = ActiveRecord::Base.connection
end
def get_address(jobid)
result = nil
state_to_UTC = {
“AL” => “-06”,
“AK” => “-09”,
“AS” => “-11”,
“AZ” => “-07”,
“AR” => “-06”,
“CA” => “-08”,
“CO” => “-07”,
“CT” => “-05”,
“DE” => “-05”,
“FL” => “-05”,
“GA” => “-05”,
“GU” => “+10”,
“HI” => “-10”,
“ID” => “-07”,
“IL” => “-06”,
“IN” => “-05”,
“IA” => “-06”,
“KS” => “-06”,
“KY” => “-05”,
“LA” => “-06”,
“ME” => “-05”,
“MD” => “-05”,
“MA” => “-05”,
“MI” => “-05”,
“MN” => “-06”,
“MS” => “-06”,
“MO” => “-06”,
“MT” => “-07”,
“NE” => “-06”,
“NV” => “-08”,
“NH” => “-05”,
“NJ” => “-05”,
“NM” => “-07”,
“NY” => “-05”,
“NC” => “-05”,
“ND” => “-06”,
“MP” => “+10”,
“OH” => “-05”,
“OK” => “-06”,
“OR” => “-08”,
“PA” => “-05”,
“PR” => “-04”,
“RI” => “-05”,
“SC” => “-05”,
“SD” => “-06”,
“TN” => “-06”,
“UT” => “-07”,
“VT” => “-05”,
“VA” => “-05”,
“VI” => “-04”,
“WA” => “-08”,
“DC” => “-05”,
“WV” => “-05”,
“WI” => “-06”,
“WY” => “-07”
}
if (sfaccount = Account.find_by_dii_jobid__c(jobid.to_s))
result = {:jobid => sfaccount[:dii_jobid__c], :utcoffset
=> state_to_UTC[sfaccount[:billing_state]]}
if(result[:utcoffset]!=nil && result[:jobid]!=nil)
@@updatestatement += "UPDATE JOB SET
TIMEZONE_OFFSET_FROM_UTC = " + “‘#{result[:utcoffset]}’”+ " WHERE
JOBID = " + “‘#{result[:jobid]}’; \n”
end
end
result
end
end
end
end
jobids = [
‘49241’,
‘49242’,
‘80745’,
‘80752’,] ## this is actually about a thousand entries larger
@@updatestatement = “”
sf = DiiTimeZone::SF::TZLookup.new(‘[email protected]’,
blankedout’)