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’).
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
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/ .
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
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.
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.
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
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?
|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)
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
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.