Seeding a Rails database with a CSV file

gistfile1.md

How to seed a Rails database with a CSV file

1. Setup

First, Create a folder inside of lib called seeds

Put your CSV file example.csv into the lib/seeds folder. In the example below, the file is called real_estate_transactions.csv

Make sure you've created a resource with the appropriate columns to match your seed data. The names don't have to match up.

2. Read in a CSV file

Add the following lines to your seeds.rb file:

require 'csv'

csv_text = File.read(Rails.root.join('lib', 'seeds', 'real_estate_transactions.csv'))
puts csv_text

The first line requires the Ruby CSV library we need to properly parse the CSV data. The next line reads in the CSV file into a variable. The last line prints the contents of the variable. When you run rake db:seed you should see a wall of text representing your CSV data. It's a first step, but we've still got a lot of work to do.

We'll keep building off this code until we've created a working seeds file. You should be able to run rake db:seed at the end of each step

3. Parse the CSV

require 'csv'

csv_text = File.read(Rails.root.join('lib', 'seeds', 'real_estate_transactions.csv'))
csv = CSV.parse(csv_text, :headers => true, :encoding => 'ISO-8859-1')
puts csv

The new line converts the CSV file into a structure that Ruby can read. The :headers => true option tells the parser to ignore the first line of the CSV file.

4. Looping through the parsed data

require 'csv'

csv_text = File.read(Rails.root.join('lib', 'seeds', 'real_estate_transactions.csv'))
csv = CSV.parse(csv_text, :headers => true, :encoding => 'ISO-8859-1')
csv.each do |row|
  puts row.to_hash
end

This new addition loops through the entire CSV file and converts each row of the document into a hash. The headers of the CSV file will be used as keys for the hash because we added the :headers => true option in our previous step.

5. Create a database object from each row

require 'csv'

csv_text = File.read(Rails.root.join('lib', 'seeds', 'real_estate_transactions.csv'))
csv = CSV.parse(csv_text, :headers => true, :encoding => 'ISO-8859-1')
csv.each do |row|
  t = Transaction.new
  t.street = row['street']
  t.city = row['city']
  t.zip = row['zip']
  t.zip = row['zip']
  t.state = row['state']
  t.beds = row['beds']
  t.sq_feet = row['sq_feet']
  t.category = row['type']
  t.sale_date = row['sale_date']
  t.price = row['price']
  t.lat = row['latitude']
  t.lng = row['longitude']
  t.save
  puts "#{t.street}, #{t.city} saved"
end

puts "There are now #{Transaction.count} rows in the transactions table"

Thank you so much, this is a great resource!

Incredibly helpful, thanks! 👍

Really helpful! But how can I get association of my model values(has_many / belongs_to) using this code? I don't know how to get upper value's Id. T-T

Hi, i have tried this and my data saves but i can't find it anywhere! I am a newbie to rails and coding in general but I have to assume the data goes somewhere if it says "saved". ANy help would be greatly appreciated

In step 3, using Rails 5 and Ruby 2.4.1, I had to change this line from:

csv = CSV.parse(csv_text, :headers => true, :encoding => 'ISO-8859-1')

to

csv = CSV.parse(csv_text.scrub, headers: true)

to avoid a UTF-8 invalid byte sequence error.

Great explanation and exactly what I wanted!

Everything works as it should until the last step, I get only null values. It says x amount of records have been created, but they just show up as blank records. There are no errors even when running rake db:seed --trace.

Am I missing something?

My version of the last step:

require 'csv'

csv_text = File.read(Rails.root.join('lib', 'seeds', 'import.CSV'))
csv = csv = CSV.parse(csv_text.scrub, headers: true)
csv.each do |row|
  t = Import.new
  t.production = row['Production']
  t.prod_id = row['Prod ID']
  t.episode = row['Episode']
  t.pic_version = row['pic_version']
  t.supervisor = row['supervisor']
  t.studio = row['studio']
  t.char_name = row['char_name']
  t.char_id = row['char_id']
  t.actor_name = row['actor_name']
  t.rec_time = row['rec_time']
  t.cue_no = row['cue_no']
  t.cue_start = row['cue_start']
  t.cue_end = row['cue_end']
  t.line = row['line']
  t.note = row['note']
  t.scene_no = row['scene_no']
  t.scene_desc = row['scene_desc']
  t.priority = row['priority']
  t.reason = row['reason']
  t.tbw = row['tbw']
  t.tc_format = row['tc_format']
  t.save
  puts "#{t.production}, #{t.episode} saved"
end

puts "There are now #{Import.count} rows in the imports table"

That's great.

CSV.foreach('link/to/file.csv', headers: true) do |row|
Item.create(row.to_h)
end

how do I concat, merge multiple csv object after load form multiple files to one?

Why the libs folder?

Good bless you!!!!

For others, you can read the CSV data directly into rows/cols by changing:
csv_text = File.read(Rails.root.join('lib', 'seeds', 'real_estate_transactions.csv'))
to
csv_text = CSV.read(Rails.root.join('lib', 'seeds', 'real_estate_transactions.csv'))
Boom. That's all.

GOD BLESS YOU <3

Thank you! :-)

This can be abbreviated with mass-assignment, to simply

CSV.read(Rails.root.join('lib', 'seeds', 'import.csv'), headers: true).each do |row|
  t = Import.create(row.to_hash)
  puts "#{t.production}, #{t.episode} saved"
end

thanks for the ideas here guys... but when i run my seeds i get a "Net::ReadTimeout" error.. any ideas how i can solve this? please?

Awesome!!

Exactly what I needed. Thank You

Thanks for the reminder!
I would like to contribute with a little precision: it is possible to reduce the code with foreach method.

require 'csv'

CSV.foreach('lib/seeds/real_estate_transactions.csv', headers: true, encoding: 'ISO-8859-1', col_sep: ';') do |row|
  t = Transaction.new
  t.street = row['street']
  t.city = row['city']
  t.zip = row['zip']
  t.zip = row['zip']
  t.state = row['state']
  t.beds = row['beds']
  t.sq_feet = row['sq_feet']
  t.category = row['type']
  t.sale_date = row['sale_date']
  t.price = row['price']
  t.lat = row['latitude']
  t.lng = row['longitude']
  t.save
  puts "#{t.street}, #{t.city} saved"
end

puts "There are now #{Transaction.count} rows in the transactions table"

<3

could we go further and do:

CSV.foreach('lib/seeds/real_estate_transactions.csv', headers: true, encoding: 'ISO-8859-1', col_sep: ';') do |row|
  t = Transaction.create(row_.to_h)
  puts "#{t.street}, #{t.city} saved"
end

puts "There are now #{Transaction.count} rows in the transactions table"

Thanks a lot!!! This really helps!

Thank you!!

Thanks!

How do you execute it? is there a special command or just execute the seed.rb file?

Thank you so much! this was really helpful.

Amazing! Thank you for this.

thank you it's help me a lot

This is very helpful. Thanks a lot.