I am trying to load seed data into my Rails application from a CSV file. I originally installed the fastcsv gem to find out that fastcsv is deprecated in favor of the CSV library with ruby 1.9. So I switched to CSV after getting a very useful error telling me to switch.
Now, however, I get a strange phenomenon when everything looks fine when loading my data, but I can’t request the string fields. The fields of the string are filled with what appears to be the correct string, but I cannot access them. I can query any of the number fields, and the results will return, but not the string fields. I tried playing with the quote separator, but to no avail. I even deleted all the quotes from my csv file, but still I could not request the string fields. Below is my code, as well as some sample queries and returns from the Rails console.
# seeds.rb # ================ require 'csv' directory = "db/init_data/" file_name = "players.seed" path_to_file = directory + file_name puts 'Loading Player records' # Pre-load All Player records n=0 CSV.foreach(path_to_file) do |row| Player.create! :first_name => row[1], :last_name => row[2], :position_id => row[5], :weight => row[6], :height => row[7], :year => row[8], :home_state => row[9], :home_town => row[10], :home_country => row[11], :high_school_id => row[12], :name => row[13] n=n+1 end
Here are the first two entries from my seed file.
# players.seed "1","Allerik","Freeman","2011-10-11 22:21:21.230247","2011-10-11 22:21:21.230247","2","210","76","2013","NC","Charlotte","USA","1","Allerik Freeman" "2","Kasey","Hill","2011-10-11 22:21:21.262409","2011-10-11 22:21:21.262409","1","170","73","2013","FL","Eustis","USA","2","Kasey Hill"
This is what I get when I enter the rails console. It works great if I want to request a number, like a year, for example.
ruby-1.9.2-p290 :002 > Player.find_all_by_year(2013) Player Load (0.7ms) SELECT "players".* FROM "players" WHERE "players"."year" = 2013 => [#<Player id: 1, first_name: "Allerik", last_name: "Freeman", created_at: "2011-10-12 20:52:16", updated_at: "2011-10-12 20:52:16", position_id: 2, weight: 210, height: 76, year: 2013, home_state: "NC", home_town: "Charlotte", home_country: "USA", high_school_id: 1, name: "Allerik Freeman">, #<Player id: 2, first_name: "Kasey", last_name: "Hill", created_at: "2011-10-12 20:52:16", updated_at: "2011-10-12 20:52:16", position_id: 1, weight: 170, height: 72, year: 2013, home_state: "FL", home_town: "Eustis", home_country: "USA", high_school_id: 2, name: "Kasey Hill">]
But if I try to request, say, a last name, I will not get anything, although this shows me that the last name is present in the previous request.
ruby-1.9.2-p290 :004 > Player.find_all_by_last_name("Freeman") Player Load (0.3ms) SELECT "players".* FROM "players" WHERE "players"."last_name" = 'Freeman' => []
The only way I could get it to work was to add an extra set of double quotes (escaped) using the hash variable notation that got all my string entries in the database in quotes, then I used delete to disable the quotes.
n=0 CSV.foreach(path_to_file) do |row| Player.create! :first_name => "\"#{row[1]}\"", :last_name => "\"#{row[2]}\"", :position_id => row[5], :weight => row[6], :height => row[7], :year => row[8], :home_state => "\"#{row[9]}\"", :home_town => "\"#{row[10]}\"", :home_country => "\"#{row[11]}\"", :high_school_id => row[12], :name => "\"#{row[13]}\"" n=n+1 end puts "There\ too many playas to hate, we just loaded #{n} of \'em" @players = Player.all @players.each do |player| fname = player.first_name player.first_name = fname.delete("\"") lname = player.last_name player.last_name = lname.delete("\"") pcity = player.home_town player.home_town = pcity.delete("\"") pst = player.home_state player.home_state = pst.delete("\"") pcountry = player.home_country player.home_country = pcountry.delete("\"") pname = player.name player.name = pname.delete("\"") player.save! end
Then I could request string data.
ruby-1.9.2-p290 :005 > Player.find_all_by_last_name("Freeman") Player Load (0.6ms) SELECT "players".* FROM "players" WHERE "players"."last_name" = 'Freeman' => [#<Player id: 1, first_name: "Allerik", last_name: "Freeman", created_at: "2011-10-12 20:52:16", updated_at: "2011-10-12 20:52:16", position_id: 2, weight: 210, height: 76, year: 2013, home_state: "NC", home_town: "Charlotte", home_country: "USA", high_school_id: 1, name: "Allerik Freeman">, #<Player id: 59, first_name: "Austin", last_name: "Freeman", created_at: "2011-10-12 20:55:16", updated_at: "2011-10-12 20:55:16", position_id: 2, weight: 210, height: 76, year: 2007, home_state: "MD", home_town: "Hyattsville", home_country: "USA", high_school_id: nil, name: "Austin Freeman">]
Obviously, this is not the preferred method, as it doubled my boot time, but I was honestly at my end.
Any help would be greatly appreciated.
As requested here, I added schema.rb
# schema.rb # =================== # encoding: UTF-8 # ... ActiveRecord::Schema.define(:version => 20111007214728) do #... create_table "players", :force => true do |t| t.string "first_name" t.string "last_name" t.datetime "created_at" t.datetime "updated_at" t.integer "position_id" t.integer "weight" t.integer "height" t.integer "year" t.string "home_state" t.string "home_town" t.string "home_country" t.integer "high_school_id" t.string "name" end # ... end
Here are the screenshots of the database that are being viewed by my SQLite Database Browser upon request.
There seems to be a similar problem here on the ruby forums, and that it probably has something to do with the encoding, but I need to do a lot more research on the encoding to figure this out.