how do I load csv data into a new Rails app through the controller/model

Hi all,

I have a need to preload 900 user names into a new Rails app.

I have used the console to load the initial admin user and passwoard
as in the
Pragmatic Programmers book "Active Development ..." Ed 2 page 162.

Now I need to load 900 users extracted out of a legacy db.

I have the appropriate details in a csv file, including the new
initial passwords generated
by apg.

How can I script a bulk load of this through the site User.create
function?
I have tried with runner and got a bunch of undefined errors.

If there is a simple script out there that correctly links to the
production environment
and opens a csv file, read in the datalines and then executes the
controller method,
please point me at it.

I am still getting up to speed on Rails, this is first project, for a
small internal company
database (its my Rails-Capistrano 'production-in-a-sandpit').

cheers
mjt

#script/generate some_script_model

require 'csv'
class SomeScriptModel < ActiveRecord::Base
def run
    CSV::Reader.parse(File.open(users.csv, 'rb'), '|') do |row|
      User.create(:name => row[0], :username => row[1], :password =>
row[2])
    end
  end
end

#script/runner "SomeScriptModel.run"

-Fredrik

should probably be

def self.run instead of def run

Even better is FasterCSV (http://fastercsv.rubyforge.org/)

And if you need it to go faster you can do bulk insert with Zach
Dennis' ActiveRecord::Extensions
(http://www.continuousthinking.com/tags/arext)

Hmm... I dont know if I am being particularly dumb tonight but ...
I get this.

script/generate some_script_model
Couldn't find 'some_script_model' generator

Does the cvs code fragment given go in an existing model (i.e. in my
case app/models/contact.rb) My table is contacts - not users.

sorry guys -- I know you know what you are talking about but I am
really struggling.
mjt

Hmm... I dont know if I am being particularly dumb tonight but ...
I get this.

script/generate some_script_model
Couldn't find 'some_script_model' generator

It should be:

   script/generate model some_script_model

Does the cvs code fragment given go in an existing model (i.e. in my
case app/models/contact.rb) My table is contacts - not users.

I would put in a separate file that you run with script/runner, unless
you want to use this functionality elsewhere in the application. So I
would do Fredrik's script something like:

load_contacts.rb:

   require 'csv'
   CSV::Reader.parse(File.open('contacts.csv', 'rb'), '|') do |row|
     Contact.create(:name => row[0], :username => row[1], :password => row[2])
   end

Then, to run it, just do:

   script/runner load_contacts.rb

HTH

mjt,

this post is of interest to me. I am currently working on a RoR
Inventory app that will synch data (weekly) from SAGE Accounts

Got the bones working in a couple of days. FasterCSV is great.

Firstly though, I actually cheated to get started. A quick and easy
way I have found to move data between Data Bases is to use Open Office
Base. Purists may frown on this, but OO will connect to a fairly wide
range of DBs. First create an OO DB connected to the Source Tables
(eg MSAccess). Then create a new DB in the DB system you are going to
use (eg. MySQL). Then simply right click on the table name of the
source DB and copy and the paste into the table area of the Target
DB. If you only have a CSV file, like I had from Sage, then OO also
has a CSV import option. Depending on the target DB you may need to
add a connector. There is a Java connector for MySQL, it is bit of a
pain, but once installed, you can forget about it.

Using OO like this was a great way to give me something quick to work
with (you need to change th ID column name to id. You can then build
a Rails scaffold, and look more carefully at the field types that OO
has created. (I believe I also spotted somewhere a CSV import
utility for MySQL itself)

Having said that, once I got started using FasterCSV, I wondered if
it would have been just as easy to use that at the outset. Although I
would have had to work out the initial table structure and migration.

The Active Record Extension giving bulk import looks like it will be
really useful for the csv synch routine.

I realize this is not a direct Rails solution, just ignore if it is of
no help.

tonypm

Hmm still not running....

firstly to tonypm, I cant do a database export/import as the old
system uses
an entirely different password system, so I have to generate new
passwords, and
encrypt them on the way into the MySQL database.

secondly, this works at the script/console (skipping a couple of
optional fields)

taylorm@wstaylorm (~/work/license)ttyp6 > script/console
Loading development environment.

Contact.create(:name => 'phred', :first_name => 'Phrodo', :last_name => 'Hred', :password => 'blert', :password_confirmation => 'blert')

=> #<Contact:0x9b1d768 @password="blert",
@attributes={"department"=>nil, "name"=>"phred",
"salt"=>"813249800.406365791080659",
"hashed_password"=>"8f24b8d8c3dba4f0e900441469ae44b22d8d7e6d",
"id"=>15, "job_type"=>nil, "contact_type"=>nil,
"first_name"=>"Phrodo", "position"=>nil, "last_name"=>"Hred",
"active"=>"Y", "location"=>nil}, @new_record_before_save=true,
@errors=#<ActiveRecord::Errors:0x9b1c304 @errors={}, @base=#<Contact:
0x9b1d768 ...>>, @password_confirmation="blert", @new_record=false>

thirdly this is my script based on Bob Showalters' example

# /script/import/user_import.rb

mjt wrote:

Hmm still not running....

firstly to tonypm, I cant do a database export/import as the old
system uses
an entirely different password system, so I have to generate new
passwords, and
encrypt them on the way into the MySQL database.

secondly, this works at the script/console (skipping a couple of
optional fields)

taylorm@wstaylorm (~/work/license)ttyp6 > script/console
Loading development environment.
  

Contact.create(:name => 'phred', :first_name => 'Phrodo', :last_name => 'Hred', :password => 'blert', :password_confirmation => 'blert')
      

=> #<Contact:0x9b1d768 @password="blert",
@attributes={"department"=>nil, "name"=>"phred",
"salt"=>"813249800.406365791080659",
"hashed_password"=>"8f24b8d8c3dba4f0e900441469ae44b22d8d7e6d",
"id"=>15, "job_type"=>nil, "contact_type"=>nil,
"first_name"=>"Phrodo", "position"=>nil, "last_name"=>"Hred",
"active"=>"Y", "location"=>nil}, @new_record_before_save=true,
@errors=#<ActiveRecord::Errors:0x9b1c304 @errors={}, @base=#<Contact:
0x9b1d768 ...>>, @password_confirmation="blert", @new_record=false>
  
thirdly this is my script based on Bob Showalters' example

# /script/import/user_import.rb
#
require File.dirname(__FILE__) + '/../../config/environment'
require 'csv'

CSV::Reader.parse(File.open('./script/import/users.csv', 'rb'), '|')
do |row|
    Contact.create(:name => row[0], :first_name => row[1],
    :last_name => row [2], :location => row[3], :position => row[4],
    :job_type => row[5], :contact_type => 'User',
    :password => row[6], :password_confirmation => row[6])
end

and I get this

ttyp6 > ruby script/import/user_import.rb
./script/import/../../config/../app/models/contact.rb:69:in
`encrypted_password': You have a nil object when you didn't expect it!
(NoMethodError)
You might have expected an instance of Array.
The error occurred while evaluating nil.+ from ./script/
import/../../config/../app/models/contact.rb:63:in `password='
        from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1672:in `send'
        from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1672:in `attributes='
        from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1671:in `each'
        from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1671:in `attributes='
        from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1505:in `initialize_without_callbacks'
        from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/callbacks.rb:225:in `initialize'
        from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:449:in `new'
        from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:449:in `create'
        from script/import/user_import.rb:9
        from /usr/local/lib/ruby/1.8/csv.rb:532:in `parse'
        from /usr/local/lib/ruby/1.8/csv.rb:560:in `each'
        from /usr/local/lib/ruby/1.8/csv.rb:531:in `parse'
        from script/import/user_import.rb:8

ttyp6 >

Sorry about the longish post... I hope this can help you all get me to
the bottom of this!
mjt
  
It looks like one of your rows doesn't contain a password, or somewhere
a field contains your delimiter '|'.

I'd run a quick integrity check on your csv file...

# /script/import/check_all_passwords_exist.rb

Have you tried using FasterCSV in a migration to parse your data to
MySQL?

There's a quick tutorial here:
http://blog.wetonrails.com/2006/11/15/import-flatfile-data-from-a-csv-file-through-migrations

It worked for me getting 300 contacts imported in about 2.0 seconds.

TaDa !!!

And The Clue Was -- delimeter ! (Thanks Gustav for the clue in your
script comments)

Being a newbie, and not knowing all the syntax etc of other packages
the script
provided by Bob had this line

CSV::Reader.parse(File.open('./script/import/users.csv', 'r'), '|') do

row>

when, for my datafile, it should have been

CSV::Reader.parse(File.open('./script/import/users.csv', 'r'), ',') do

row>

with a comma at the end of the parse parameters instead of the bar.

(I have learned a bit more about using ri now too..)

Followup question - Are there any changes I need to make to define/
select the production environment
when I run this 'for real' ?? Or will the mere fact of running this on
the production box be enough?

Thank you all
mjt

P.S. The comments about FasterCSV are taken on board, but this is
currently only a one-time
initial data load, so speed is not critical.

mjt wrote:

TaDa !!!

And The Clue Was -- delimeter ! (Thanks Gustav for the clue in your
script comments)

Being a newbie, and not knowing all the syntax etc of other packages
the script
provided by Bob had this line

CSV::Reader.parse(File.open('./script/import/users.csv', 'r'), '|') do
>row>

when, for my datafile, it should have been

CSV::Reader.parse(File.open('./script/import/users.csv', 'r'), ',') do
>row>

with a comma at the end of the parse parameters instead of the bar.

(I have learned a bit more about using ri now too..)

Followup question - Are there any changes I need to make to define/
select the production environment
when I run this 'for real' ?? Or will the mere fact of running this on
the production box be enough?

Thank you all
mjt

P.S. The comments about FasterCSV are taken on board, but this is
currently only a one-time
initial data load, so speed is not critical.

If you're using lighttpd on your production box, the config file entry
for your site will usually have something like the following included
(stripped down for brevity)

  fastcgi.server = (
    ".fcgi" => (
      "somesite" => (
        "bin-environment" => ("RAILS_ENV" => "production")
      )
    )

In which case you need not change anything in your app. (AFAIK if you're
using mongrel_clusters, their config file has a similar option.)
You can force your environment to production by uncommenting the

# ENV['RAILS_ENV'] ||= 'production'

line in your config/environment.rb file.

Cheers,
Gustav Paul