Ruby on Rails Import CSV into MySQL

Question

Newbie question....

Trying to start a project in rails. I have different spreadsheets in csv format I'd like to import into the MySQL database to be able to manipulate the data.

After looking around on stackoverflow, Google, etc. I wrote a rake task requiring fastercsv to do the job. I keep getting errors so hopefully you can help.

... Ok so I changed the code to use 'csv' vs 'fastercsv'...still getting errors. See below

New Code for Rake File (take 3):

require 'csv'

desc "Import gac from csv file"
task :import => [:environment] do

  file = "gac.csv"

  CSV.foreach(file, :headers => true) do |row|
    Institution.create({
    :institution_name => row[0],
    :website => row[1],    
    :email => row[2],
    :category_1 => row[3],
    :category_2 => row[4],
    :category_3 => row[5],
    :category_4 => row[6],
    :category_5 => row[7],
    :category_6 => row[8],
    :category_7 => row[9],
    :category_8 => row[10],
    :category_9 => row[11],
    :category_10 => row[12],
    :category_11 => row[13],
    :institution_description => row[14]
    })
  end
end

Error Codes:

Daves-MacBook-Pro:vendor dave$ rake import --trace
** Invoke import (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute import
rake aborted!
invalid byte sequence in UTF-8
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/csv.rb:1855:in `sub!'
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/csv.rb:1855:in `block in shift'
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/csv.rb:1849:in `loop'
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/csv.rb:1849:in `shift'
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/csv.rb:1791:in `each'
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/csv.rb:1208:in `block in foreach'
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/csv.rb:1354:in `open'
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/csv.rb:1207:in `foreach'
/Users/dave/rails_projects/vendor/lib/tasks/import.rake:8:in `block in <top (required)>'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `call'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `block in execute'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `each'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `execute'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/Users/dave/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/task.rb:144:in `invoke'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:116:in `invoke_task'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `block (2 levels) in top_level'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `each'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `block in top_level'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:133:in `standard_exception_handling'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:88:in `top_level'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:66:in `block in run'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:133:in `standard_exception_handling'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/lib/rake/application.rb:63:in `run'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/gems/rake-0.9.2.2/bin/rake:33:in `<top (required)>'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/bin/rake:19:in `load'
/Users/dave/.rvm/gems/ruby-1.9.3-p0/bin/rake:19:in `<main>'
Tasks: TOP => import

And could you please tell me, why are you using FasterCSV gem instead of CSV inbuilt class of ruby?? Try: require 'csv' instead? — Surya
– Surya, Commented Jan 19, 2012 at 18:13
@Surya: Right, 1.9's standard csv is pretty much FasterCSV with improved UTF-8 support, there's no need for FasterCSV unless you're stuck with 1.8. — mu is too short
– mu is too short, Commented Jan 19, 2012 at 18:19
Wow..you guys are fast. Thanks. Yes, I do FasterCSV gem installed and I'm trying to modify the rake command now with the answer below using csv instead of fastercsv. — DaveG
– DaveG, Commented Jan 19, 2012 at 19:24
Is lib/tasks/import.rake really the first line of your script or is that just a piece of meta info telling us thats where your script lives on disk? — Cody Caughlan
– Cody Caughlan, Commented Jan 19, 2012 at 19:52

jeffbricco · Accepted Answer · 2012-01-19 20:52:58Z

3

lib/tasks/import.rake

require 'csv'

desc "Import gac from csv file"
task :import => [:environment] do

  file = "vender/gac.csv"

  CSV.foreach(file, :headers => true) do |row|
    Putthemodelnamehere.create ({
      :columnnamewhatever => row[1],
      :columnname => row[2],
      :columnname => row[4]
    })
  end
end

Then just run rake import or bundle exec rake import. Hope this helps (this isn't using fastercsv but this is the solution I'd recommend.)

edited Jan 19, 2012 at 20:52

answered Jan 19, 2012 at 18:27

jeffbricco

1349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DaveG Over a year ago

Jeff just tried your answer but no dice...any suggestions on the updated code I posted? Thanks a ton for the help.

DaveG Over a year ago

Jeff, I did miss the {} , so I put those in...still didn't run. I've updated my code above with errors. Thanks!

Michael Durrant Over a year ago

jeffbrico that's actual a model name, i.e. it's rails not SQL and in Rails Model Names Do Start With Upper Case :)

Michael Durrant Over a year ago

So Dave G please make sure that you have the model class defined in app/models and make sure that you have run the migrations to create the institutions table (which should be lowercase and plural as done by rails).

Michael Durrant · Accepted Answer · 2012-01-19 20:24:50Z

2

Make sure that you have the Institution model class defined in app/models and/or make sure that you have run the db migration(s) (rake db:migrate) to create the institutions table - assuming you used a generator (or scaffold) to create the model.

answered Jan 19, 2012 at 20:24

Michael Durrant

97.2k102 gold badges352 silver badges536 bronze badges

1 Comment

DaveG Over a year ago

I'm not sure what happened I got errors but the database was updated with the info...Thanks a ton. I've reposted the errors in case you know why I got those.

Jonathon Jones · Accepted Answer · 2012-03-06 15:39:07Z

2

The errors you are getting are probably because there is something in your csv that is not properly encoded for UTF-8.

One way of dealing with that would be to force the encoding. You could do the following:

For each association you have above, do this instead:

:category_1 => row[3].encode("UTF-8", replace: ' '),

Hopefully that will work for you.

answered Mar 6, 2012 at 15:39

Jonathon Jones

7416 silver badges8 bronze badges

Collectives™ on Stack Overflow

Ruby on Rails Import CSV into MySQL

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related