Importing CSV values into ActiveRecord tables

I have been using Google Docs lately to store some of my data, and then wanting to import it into my Rails application. I poked around the web for an easy way to load CSV data into an ActiveRecord table, but the only options were using the CSV library and hard coding table names and such. Why not just a simple rake task that takes an arbitrary CSV file (with a header!) and a model name, and imports?

So, I wrote it quickly:

#lib/tasks/import.rake
desc "Imports a CSV file into an ActiveRecord table"
task :csv_model_import, :filename, :model, :needs => :environment do |task,args|
  lines = File.new(args[:filename]).readlines
  header = lines.shift.strip
  keys = header.split(',')
  lines.each do |line|
    values = line.strip.split(',')
    attributes = Hash[keys.zip values]
    Module.const_get(args[:model]).create(attributes)
  end
end

You can then do…

rake csv_model_import[bunnies.csv,Bunny]

… to import your CSV file. Note that your CSV needs to have a header line with the attribute names:

name,age,favorite_food
tabitha,2,carrots
elijah,1,lettuce
beatrice,3,apples

Edit: Switched to using array.zip instead of doing a custom loop

Search keywords: activerecord import csv rows load database table model

18 Responses to “Importing CSV values into ActiveRecord tables”

  1. Qza Says:

    Excellent tip! Really nice intro into rake tasking

  2. Billy Says:

    Awesome!

    Thanks!

  3. preload and import a csv file into your rails 3 database – nSurface Says:

    [...] this code is a mashup of these two little code snippits: one two July 20, 2011 // posted in script // no [...]

  4. Ricardo Says:

    This works mostly form me. My data looks like this… (but with about 500 entries)

    name,origin,type,size,abv
    A. Le Coq Imperial Extra Double Stout,England,ImperialStout,12oz, 10.0
    Abita Amber,Louisiana,Amber / Red Lager,12oz, 4.4
    Abita Andygator,Louisiana,Doppel Bock,22oz, 8.0
    Abita Jockamo,Louisiana,IPA ,12oz, 6.5
    Abita Purple Haze,Louisiana,Wheat,12oz, 4.2
    Abita Turbodog,Louisiana,Brown Ale ,12oz, 5.6
    Ace PearCider,California,Cider,12oz, 5.0

    No errors are reported however the type field is not persisted.

    My schema is
    CREATE TABLE “beers” (”id” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, “name” varchar(255), “origin” varchar(255), “type” varchar(255), “size” varchar(255), “abv” decimal, “created_at” datetime, “updated_at” datetime);

    and my model is

    beer.rb

    class Beer < ActiveRecord::Base
    end

    Any ideas????

  5. Alwa Happy Says:

    Superb Code…simple and straight forward…

  6. erik Says:

    Alwa, you flatter me!

  7. RyeGuyHead Says:

    Excellent task, extremely useful.

    Makes populating a db with default data a breeze.

    Two quick notes, though:

    1) I’m using Rails 3.0.9 and I had to change

    :filename, :model, :needs => :environment

    TO

    [:filename, :model] => [:environment]

    in order for it to run.

    2) Your example rake requires the .csv to reside in the root directory of the app

  8. Gianluca Says:

    Hi there,

    thanks for the code. I had some trouble with the split when having columns containing a comma. I had a search around and saw that people recommend using the built-in CSV module. The following reads one line at the time so no risk of hitting memory limits with big files:

    require “csv”
    desc “Import CSV file into an Active Record table”
    task :csv_model_import, [:filename, :model] => :environment do |task,args|
    firstline=0
    keys = {}

    CSV.foreach(args[:filename]) do |row|
    if (firstline==0)
    keys = row
    firstline=1
    next
    end

    params = {}

    keys.each_with_index do |key,i|
    params[key] = row[i]
    end

    Module.const_get(args[:model]).create(params)
    end
    end

  9. ami Says:

    hi, i am getting an error :

    rake aborted!
    uninitialized constant Module::Name

    Tasks: TOP => csv_model_import
    (See full trace by running task with –trace)

  10. erik Says:

    Ami: What do you get when you run the task with –trace like it says?

  11. Sumit Says:

    hi Erik…thx for providing the code…it does not seem to work for me, am sure i am making an error….when i run the rake command, it seems to go through ok as i don’t see any error. however, i don’t see the database populated either :( can you please help?

  12. NDN Says:

    Nice, this code works like a charm. One must be careful to use the singular version of the model though! e.g. ‘Exercise’ not ‘exercises’

  13. TW Scannell Says:

    In Rails 3.1, I got the error: ” warning: can’t mass-assign protected attributes” repeatedly. It seemed to be related to the create method.
    I altered the rake task to create a model, populate it and call save.
    Save will alias the imputs like so:

    “insert into blah (”volts_1″, “volts_2″, “volts_3″, “waste_oil_tank_level”) VALUES ($1, $2, $3, $4)[ ["volts_1", 4194], ["volts_2", 4198], ["volts_3", 4195], ["waste_oil_tank_level", "full"]]”

    and avoid the mass assignment protection.

    #lib/tasks/import.rake
    desc “Imports a CSV file into an ActiveRecord table”
    task :csv_model_import, [:filename, :model] => [:environment] do |task,args|
    lines = File.new(args[:filename]).readlines
    header = lines.shift.strip
    keys = header.split(’,')
    lines.each do |line|
    myModel = Module.const_get(args[:model]).new
    values = line.strip.split(’,')
    keys.each_with_index do |key,i|
    myModel.send(”#{key.strip}=”, values[i].strip) unless values[i].nil?
    end
    myModel.save
    end
    end

  14. Jim Says:

    WARNING: ‘task :t, arg, :needs => [deps]‘ is deprecated. Please use ‘task :t, [args] => [deps]‘ instead.
    at ~/source/blog/lib/tasks/gene.rake:3:in `’
    rake aborted!
    Don’t know how to build task ‘csv_model_import[protein_coding_gene.csv,’

    (See full trace by running task with –trace)

    —Not so up to date

  15. Deepak Says:

    Worked perfectly..
    Thanks

  16. Pywackt Says:

    Bunnies!

  17. Justin Says:

    Love that impressively short script!

  18. Lauralee Says:

    I worked on this for hours and hours one day. I finally got it to work by doing the following:

    1. Added a header in the first row to my csv file that reflected the attr_accessible in my model. In my case my model was attr_accessible :intro, :name and in my csv file the first line read name, intro.
    2. Created a custom rake file. I named mine import.rake and placed it in the lib/tasks folder. Place this code in that file:

    > #lib/tasks/import.rake
    > require ‘csv’
    > desc “Imports a CSV file into an ActiveRecord table”
    > task :import, [:filename] => :environment do
    > CSV.foreach(’myfile.csv’, :headers => true) do |row|
    > MyModel.create!(row.to_hash)
    > end
    > end

    Then type `bundle exec rake import` into the command line.

    To get this to work I had quite SQLite Database Browser. I hope that helps someone!

Leave a Reply