Not able to import csv to MySQL db in Ruby on Rails 3

Question

I am working on importing data from a CSV file into MySQL db through Ruby on Rails 3. The customer model has already been created. Also, the script below will produce puts row[2] and puts row[3] correctly. When I add the assignments for the database fields of customers.warranty_part_no and warranty_part_desc it produces the error below.

csv = CSV.read(file, col_sep: ",", headers: false)

c = Customer.new  
csv.each do |row|
        c.warranty_part_no = row[2],
        c.warranty_part_desc = row[3]
end

Here is the error I get.

uninitialized constant Customer (NameError)

After some testing I think this problem is because I am running this script from command line so the customer.rb model is not being executed with the larger rails app so the Customer class never gets created. How can I run this script from command line and take advantage of ActiveRecord or activerecord-import? If that is not possible, how can I create a route for it or call it from a view in the app?

I am on Ruby 1.9.2 and Rails 3.2.2. Thanks in advance for any advice.

why not use MySQL LOAD DATA INFILE ... ? SEE: http://dev.mysql.com/doc/refman/5.1/en/load-data.html — Mike S., Jul 16 '12 at 17:22
yes, I'm using that for several other tasks. I'm only keeping a few of the fields from this file. Also, this will become a form on screen as well. — analyticsPierce, Jul 16 '12 at 17:27
Have you tried loading the app before running your import (`require 'config/environment'`)? Ugly, but should get your models fired up... — rjz, Jul 18 '12 at 17:27

score 3 · Answer 1 · answered Jul 19 '12 at 01:53

Here are two ways you could go about this

 #myscripy.rb
 # add following in your script
 require 'rubygems'
 require 'active_record'
 require YOUR_DB_ADAPTER_GEM #'mysql2', 'pg'
 require PATH_TO_YOUR_MODEL

 csv = CSV.read(file, col_sep: ",", headers: false)

 c = Customer.new  
 csv.each do |row|
    c.warranty_part_no = row[2],
    c.warranty_part_desc = row[3]
 end
 c.save

else you can simply write rake task (preferably in same RailsApp)

require 'csv'
 namespace :import do
 task :csv_file => :environment do
  csv = CSV.read(file, col_sep: ",", headers: false)

   c = Customer.new  
   csv.each do |row|
    c.warranty_part_no = row[2],
    c.warranty_part_desc = row[3]
   end
   c.save
  end
end

and from your command-line when you are in RailsApp can call rake import:csv_file

comment me if you have any doubt on this.

score 2 · Accepted Answer · answered Jul 19 '12 at 01:35

The problem, as you have figured out, is that your Rails environment isn't loading. You can do this in a standalone script by including your Customer model, active_record, and establishing a connection using your database.yml.

However, there's an easier way. Create a rake task like this:

namespace :data do
  desc "Import data from CSV"
  task :import => :environment do
    #Your script here
  end
end

The => :environment tells rake to load Rails so your database connections and all your models will just be there for you to use.

Invoke it with rake data:import, adding RAILS_ENV=production if you want production environment.

score 1 · Answer 3 · answered Jul 18 '12 at 17:29

I believe what you're looking for is the rails runner Rails Command Line Docs which will execute your ruby script in the environment of your rails application.

You could also use the rails console if you just want to access your rails objects in an interactive command shell. via rails console

score 1 · Answer 4 · answered Jul 19 '12 at 15:04

1

You can also just require your config/environment.rb to get access to your models.

answered Jul 19 '12 at 15:04

codatory

686
4
5

score 1 · Answer 5 · edited May 23 '17 at 12:13

Fix to your Problem

Your problem seems to be that your Rails environment is not loaded on startup of your script.

To correctly initialize the Rails environment, you need to either:

create a Rake task and make it dependent on 'environment' See:
- http://railscasts.com/episodes/66-custom-rake-tasks
start your script via Rails runner. See:
or you could add the required files 'by hand' to your script, but I would not recommend that, as it might break once you upgrade your Rails version

Side-Note on easier CSV importing

I always felt that dealing with CSV files as Arrays of Arrays is pretty clumpsy.

So I wrote a small method "process_csv" which returns each row in a CSV file as a Hash - much more suitable for creating ActiveRecord or Mongoid records.

You could use the following code, and put it under ./config/initializers/process_csv.rb , and pass a block to process_csv to create a Consumer record for each row in the CSV file.

You can use the :key_mapping option to rename CSV columns to the correct attribute names for your ActiveRecord model, and also to remove CSV-keys from the result hash.

Source code with detailed comments here:

http://www.unixgods.org/~tilo/Ruby/process_csv_as_hashes.html

e.g.:

require 'process_csv'

File.process_csv( file ) do | hash |
   Consumer.create( hash )
end

enjoy!

Not able to import csv to MySQL db in Ruby on Rails 3

5 Answers5