4

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
Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
DaveG
  • 1,203
  • 1
  • 25
  • 45
  • Do you have `FasterCSV` gem installed on your system? – Surya Jan 19 '12 at 18:07
  • 1
    And could you please tell me, why are you using `FasterCSV` **gem** instead of `CSV` **inbuilt class** of ruby?? Try: `require 'csv'` instead? – Surya Jan 19 '12 at 18:13
  • 1
    @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 Jan 19 '12 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 Jan 19 '12 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 Jan 19 '12 at 19:52
  • lib/tasks/import.rake was the first line I took it out, see the updated code and errors above. – DaveG Jan 19 '12 at 19:59
  • Why not use your database's import function? – Gareth Jan 19 '12 at 20:12
  • From the error it looks like it's an encoding issue - are you sure the spreadsheet is UTF-8 encoded? – Mando Escamilla Jan 19 '12 at 21:32

3 Answers3

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

jeffbricco
  • 134
  • 9
  • Jeff just tried your answer but no dice...any suggestions on the updated code I posted? Thanks a ton for the help. – DaveG Jan 19 '12 at 19:44
  • Jeff, I did miss the {} , so I put those in...still didn't run. I've updated my code above with errors. Thanks! – DaveG Jan 19 '12 at 20:11
  • 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 Jan 19 '12 at 20:22
  • 1
    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 Jan 19 '12 at 20:23
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.

Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
  • 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. – DaveG Jan 19 '12 at 21:32
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.

Jonathon Jones
  • 711
  • 6
  • 8