Hoopla!

I seem to have a limitless stream of excel files coming at me from clients. Most of them are of the same format: first line is the column name and the rest of the lines are data.

Ruby has excellent baked-in capabilities for handling files, data, and even CSV stuff. Still, it was pretty hard for me to figure out how to turn a CSV file into an array of hashes where each cell was named with the correct column name.

So here y’are folks: an easy way to turn CSV files into an array of hashes.

def csv_to_array(file_location)
  csv = CSV::parse(File.open(file_location, 'r') {|f| f.read })
  fields = csv.shift
  csv.collect { |record| Hash[*(0..(fields.length - 1)).collect {|index| [fields[index],record[index].to_s] }.flatten ] }
end

Earlier I posted a list of all US zipcodes by city and state. I needed it for one of my sites and now I’m finding it handy for another. Instead of having people fill City, State, and Zip individually (with a high probability that the data won’t be standardized enough to do comparisons between records), you can simply accept a zip code from them and populate the rest yourself.

I create a Zip model that has the following three fields: `code, city, state`. I import the data into it like so (dependant on mysql):

class CreateZips < ActiveRecord::Migration
  def self.up
    create_table :zips do |t|
      t.column  :city,      :string
      t.column  :state,     :string
    end
    # load the zipcodes from a csv file
    execute "load data infile '#{RAILS_ROOT}/db/migrate/zips.csv' into table zips fields terminated by ',' lines terminated by '\n'" 
  end

  def self.down
    drop_table :zips
  end
end

And I make sure my zips.csv file can be found in the ./db/migrate folder.

To associate this model with others, just add a `belongs_to :zip` and make sure that you’ve got a `zip_id` in the table schema. If you use the above migration the zipcode will be set as the table id and you can manually set zip_id to whatever zip code you want – handy for writing fixtures!

Now in my forms I only need to have a 5-char field that asks for a zipcode and (using lovely, lovely AJAX) displays the corresponding city and state automatically.