I tried to use Ruby's built-in Active Record fixtures module to import an Excel-exported CSV file. No dice. Kept getting cryptic errors about improper formatting. Whoops. So then I installed this wicked cool gem named FasterCSV and piggy-backed off of this migration import script and everything went great. So for all those people with ancient (and kinda useless) Excel data files, now there's a relatively quick and painless way to import them into SQL dbs. Here are the steps I went through:
Install the gem:
sudo gem install fastercsv
Now I have several tables in my College Football spreadsheet and for this example I'll use the one with ratings data for College Football games. Each entry has a game_id, region_id (for the various regions where ratings data comes from, e.g., Atlanta and Austin), a rating, and a duration in quarter hours (since some games are only cut-ins, e.g., Georgia Tech-Georgia ends early and the Atlanta region picks up the last half hour of the Texas-Oklahoma game). So here's the script that imports my gross Excel spreadsheet into a useable SQL table - db-agnostic by the way (How do I love thee Rails? Let me count the ways.)
require 'fastercsv'
class LoadRatingsData < ActiveRecord::Migration
def self.up
FasterCSV.foreach("#{RAILS_ROOT}/db/migrate/fixtures/ratings.csv", :row_sep => "\r") do |row|
id,game_id, region_id, rating, duration = row
Rating.create(:id => id, :game_id => game_id, :region_id => region_id, :rating => rating, :duration => duration)
end
end
def self.down
end
end
And it's just that easy. Another cool thing, that I haven't done yet, is that you could take that one bulky Excel sheet that has way too much information in it, break it into useful models, and form actual relationships between the data. What a novel idea!
In Conclusion,
This rules!
Designed By :
