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!
Comments
I am new to Rails, so forgive if the question is dumb.
How does ActiveRecord know which table to use? In your example, should I understand that there is a table called RatingsData?
.
Hey Mat. Not dumb at all. In Rails when you generate a scaffold (which I generally do for most classes) using, in my example, the command “script/generate scaffold Rating” Rails auto-generates a whole heap of stuff for you. One of these is the ActiveRecord Rating (the ‘M’ in “MVC”) and another is a migration called ##_create_ratings.rb (where ## is some number).
The migration is what actually builds the table in your database. You define your database in the file config/database.yml.
So finally, the line “Rating.create” in my example creates an instance of the class Rating that I created with “script/generate scaffold” and saves it to the ratings table created by the create_ratings migration (also generated by the scaffold) that is located in the database defined by config/database.yml.
Short answer to your question, and maybe what you’re actually asking, is Rails associates tables and models by looking for tables named after the plural of the Model name. So with a
‘Rating’ Model (a.k.a. ActiveRecord) it looks for the ‘ratings’ table. With ‘Book’ it looks for ‘books’, and so on. Rails magic 
Phew. Hope that helps. If any of that is confusing, please follow-up. Ciao!
Hi Dary,
Your posting was the best I have seen on this. Thanks for the clarification. I was now able to complete the task, and I am on my way with my little project. Thanks for sharing!!!
Also a noob here. When I try to run your script I get:
unterminated string meets end of file
Any suggestions?
I’d say make sure each opening ” has a closing ” especially around these parts: “#{RAILS_ROOT}/db/migrate/fixtures/ratings.csv” and “\r”
Thanks, I don’t get error messages but I don’t get any data in as well…
require ‘rubygems’
require ‘fastercsv’
require ‘activerecord’
class CreateCatalogs “\r”) do |row| catalog_number, tech_segment, discount_schedule, price_family, price_group, catalog_number_compressed, list_price, = row
Catalog.create(:catalog_number => catalog_number, :tech_segment => tech_segment, :discount_schedule => discount_schedule, :price_family => price_family, :price_group => price_group, :catalog_number_compressed => catalog_number_compressed, :list_price => list_price)
end
end
def self.down
end
end
Does it matter if I have additional columns in my table?
Is that the actual code or did it get screwed up when you pasted it? If that’s your code there’s a bunch of syntax errors…
Designed By :
