How do I load csv data into a new Rails app through the cont

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

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)

On 6/5/07, Fredrik [email protected] wrote:

def run

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


Gabe da Silveira
http://darwinweb.net

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

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.

Yes, you could put it there. But if this is a one time import of user
data, perhaps it doesn’t belong in the model ? You could put it as a
script in for example /script/import/ .

Something like

/script/import/user_import.rb

require File.dirname(FILE) + ‘/…/…/config/environment’
require ‘csv’

CSV::Reader.parse(File.open(users.csv, ‘rb’), ‘|’) do |row|
User.create(:name => row[0], :username => row[1], :password =>
row[2])
end

Then you run with ruby script/import/user_import.rb .

If you use the script/runner command above, I think it loads the app’s
rails environment for you (so you don’t need the first line above).

Regards,

Chris

On 6/6/07, mjt [email protected] wrote:

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

should probably be

def self.run instead of def run

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)

[email protected] (~/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 S.s’ 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:inpassword=’
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:inattributes=’
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:inattributes=’
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:ininitialize’
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:increate’
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:ineach’
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

mjt wrote:

“first_name”=>“Phrodo”, “position”=>nil, “last_name”=>“Hred”,
require File.dirname(FILE) + ‘/…/…/config/environment’

active_record/base.rb:1672:in `send’
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/

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

require ‘csv’

i = 0
CSV::Reader.parse(File.open(’./script/import/users.csv’, ‘r’), ‘|’) do
|row|
i += 1
#row[6].to_s.strip == “” just checks that the password field isn’t
empty/only spaces, and row.size > 7
#assumes you’ve 7 fields (row[0…6]) and checks that none of your
data
#fields contain the delimiter (which can kinda mess up your routine
:] )

puts "Faulty line: #{i}: #{row.join('|')} " if (row[6].to_s.strip == 

“”) or (row.size > 7)
end

Will run through your rows and make sure every entry has a password.

If this isn’t the case, you could post your Contact model and we can try
and figure out what’s going on?

GL
Gustav P.

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.

mjt wrote:

|row|
Followup question - Are there any changes I need to make to define/

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 P.

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.

On Jun 8, 9:01 am, “[email protected]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs