Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Seeding a Rails database with a CSV file

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"
@Jirka1111

This comment has been minimized.

Copy link

@Jirka1111 Jirka1111 commented Jan 17, 2016

Thank you for this. It really helped me a lot.

@jmac522

This comment has been minimized.

Copy link

@jmac522 jmac522 commented Mar 2, 2016

This was exactly what I've been looking for, saved me a lot of trouble. Thanks!

@jaykilleen

This comment has been minimized.

Copy link

@jaykilleen jaykilleen commented Mar 22, 2016

Cheers for this. It has worked really well in my latest project 👍

@mmuntaz

This comment has been minimized.

Copy link

@mmuntaz mmuntaz commented Jun 2, 2016

Can I allow a user to import his data into my app? I am working on Contact management app and I want to let a new user import all his contacts into the app.

@klinskyc

This comment has been minimized.

Copy link

@klinskyc klinskyc commented Jun 30, 2016

A user could import his contacts from csv using the same method but the code would not be in the seeds file

@peimelo

This comment has been minimized.

Copy link

@peimelo peimelo commented Jun 30, 2016

@KKirad

This comment has been minimized.

Copy link

@KKirad KKirad commented Nov 7, 2016

Many Thanks. It really helped alot..!!!

@ignaciosiri

This comment has been minimized.

Copy link

@ignaciosiri ignaciosiri commented Nov 13, 2016

Thanks! Helped me a lot! 👍 👍

@patkil

This comment has been minimized.

Copy link

@patkil patkil commented Jan 27, 2017

Super helpful & straightforward. Thanks!

@shivabhusal

This comment has been minimized.

Copy link

@shivabhusal shivabhusal commented Feb 9, 2017

my concern is, if the CSV file is worth 1GB then would it be efficient to parse the entire CSV file at once?

@ballPointPenguin

This comment has been minimized.

Copy link

@ballPointPenguin ballPointPenguin commented Feb 14, 2017

I get NameError: uninitialized constant Transaction

update: Ahahaha
Transaction should be replaced with your model name, in my case Member. Of course!

@TechBenders

This comment has been minimized.

Copy link

@TechBenders TechBenders commented Feb 27, 2017

THANK YOU!!!! This is exactly what is needed ;)

@paingha

This comment has been minimized.

Copy link

@paingha paingha commented Mar 1, 2017

Has anyone been able to make this work with devise. It says users saved but on rails console after running Users.all the changes aren't shown

@waruboy

This comment has been minimized.

Copy link

@waruboy waruboy commented Mar 6, 2017

@paingha are you trying to create a new User? You need to supply "password" and "password_confirmation" fields with your user password

@WilfredRuck

This comment has been minimized.

Copy link

@WilfredRuck WilfredRuck commented Mar 11, 2017

Thank you, this has helped me so much! Way easier and simpler than trying to use a gem.

@piratetone

This comment has been minimized.

Copy link

@piratetone piratetone commented Mar 15, 2017

I don't know why all other seeding rails with a csv tutorials are so weak, but your's is significantly better. Thank you for posting this!

@ctpelnar1988

This comment has been minimized.

Copy link

@ctpelnar1988 ctpelnar1988 commented Jun 14, 2017

Thank you so much, this is a great resource!

@RSijelmass

This comment has been minimized.

Copy link

@RSijelmass RSijelmass commented Jun 29, 2017

Incredibly helpful, thanks! 👍

@tlatldms

This comment has been minimized.

Copy link

@tlatldms tlatldms commented Aug 14, 2017

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

@quietwar

This comment has been minimized.

Copy link

@quietwar quietwar commented Aug 24, 2017

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

@somedayrainbows

This comment has been minimized.

Copy link

@somedayrainbows somedayrainbows commented Aug 31, 2017

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.

@cboutter

This comment has been minimized.

Copy link

@cboutter cboutter commented Oct 14, 2017

Great explanation and exactly what I wanted!

@mjdebeer

This comment has been minimized.

Copy link

@mjdebeer mjdebeer commented Oct 19, 2017

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"
@superhero2007

This comment has been minimized.

Copy link

@superhero2007 superhero2007 commented Nov 25, 2017

That's great.

@superhero2007

This comment has been minimized.

Copy link

@superhero2007 superhero2007 commented Nov 25, 2017

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

@narutoo9x

This comment has been minimized.

Copy link

@narutoo9x narutoo9x commented Mar 23, 2018

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

@lgallindo

This comment has been minimized.

Copy link

@lgallindo lgallindo commented Mar 25, 2018

Why the libs folder?

@oscarlaf03

This comment has been minimized.

Copy link

@oscarlaf03 oscarlaf03 commented May 15, 2018

Good bless you!!!!

@samjf

This comment has been minimized.

Copy link

@samjf samjf commented May 21, 2018

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.

@Asakab

This comment has been minimized.

Copy link

@Asakab Asakab commented Jun 7, 2018

GOD BLESS YOU <3

@msayen

This comment has been minimized.

Copy link

@msayen msayen commented Jul 6, 2018

Thank you! :-)

@philihp

This comment has been minimized.

Copy link

@philihp philihp commented Jul 7, 2018

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
@davidmukiibi

This comment has been minimized.

Copy link

@davidmukiibi davidmukiibi commented Jul 16, 2018

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?

@halleyrv

This comment has been minimized.

Copy link

@halleyrv halleyrv commented Nov 8, 2018

Awesome!!

@gsum

This comment has been minimized.

Copy link

@gsum gsum commented Dec 11, 2018

Exactly what I needed. Thank You

@escanxr

This comment has been minimized.

Copy link

@escanxr escanxr commented Jan 23, 2019

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"
@codetrane

This comment has been minimized.

Copy link

@codetrane codetrane commented Mar 12, 2019

<3

@tansaku

This comment has been minimized.

Copy link

@tansaku tansaku commented Nov 28, 2019

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"
@behindname

This comment has been minimized.

Copy link

@behindname behindname commented Jul 6, 2020

Thanks a lot!!! This really helps!

@Maxeeezy

This comment has been minimized.

Copy link

@Maxeeezy Maxeeezy commented Jul 14, 2020

Thank you!!

@elizeusdsantos

This comment has been minimized.

Copy link

@elizeusdsantos elizeusdsantos commented Aug 26, 2020

Thanks!

@euqueme

This comment has been minimized.

Copy link

@euqueme euqueme commented Nov 25, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment