Loading data from a CSV file into your database

Search…
1. Setup
First, create a folder inside of lib called csvs
Put your CSV file into the lib/csvs folder. In the example below, the file is called real_estate_transactions.csv
You should already have an ActiveRecord model ready to go to store the data from the CSV; in our example below, we have one called Transaction. The column names in your model don't have to match up exactly with the headings in the CSV.
Let's suppose we ultimately want to run a command named rails slurp:transactions which will slurp the data from real_estate_transactions.csv and put it in our database.
First, let's create a custom rake task with this name:
1
rails generate task slurp transactions
Copied!
This will create a file called lib/tasks/slurp.rake that looks like this:
1
namespace :slurp do
2
  desc "TODO"
3
  task transactions: :environment do
4
  end
5
​
6
end
Copied!
You can now add the code described in the rest of this guide between the lines task transactions: :environment do and the first end:
2. Read in a CSV file
1
require "csv"
2
​
3
csv_text = File.read(Rails.root.join("lib", "csvs", "real_estate_transactions.csv"))
4
puts csv_text
Copied!
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 rails slurp:transactions 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 rails slurp:transactions at the end of each step
3. Parse the CSV
1
require "csv"
2
​
3
csv_text = File.read(Rails.root.join("lib", "csvs", "real_estate_transactions.csv"))
4
csv = CSV.parse(csv_text, :headers => true, :encoding => "ISO-8859-1")
5
puts csv
Copied!
The new line converts the CSV file into a structure that Ruby can read. The :headers => true option tells the parser that the first line in the file has column headings in it, not a row of data.
4. Looping through the parsed data
1
require "csv"
2
​
3
csv_text = File.read(Rails.root.join("lib", "csvs", "real_estate_transactions.csv"))
4
csv = CSV.parse(csv_text, :headers => true, :encoding => "ISO-8859-1")
5
csv.each do |row|
6
  puts row.to_hash
7
end
Copied!
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
1
require "csv"
2
​
3
csv_text = File.read(Rails.root.join("lib", "csvs", "real_estate_transactions.csv"))
4
csv = CSV.parse(csv_text, :headers => true, :encoding => "ISO-8859-1")
5
csv.each do |row|
6
  t = Transaction.new
7
  t.street_address = row["street"]
8
  t.city = row["city"]
9
  t.zip = row["zip"]
10
  t.state = row["state"]
11
  t.bedrooms = row["beds"]
12
  t.square_feet = row["sq_feet"]
13
  t.category = row["type"]
14
  t.sold_on = row["sale_date"]
15
  t.price = row["price"]
16
  t.lat = row["latitude"]
17
  t.lng = row["longitude"]
18
  t.save
19
  puts "#{t.street_address}, #{t.zip} saved"
20
end
21
​
22
puts "There are now #{Transaction.count} rows in the transactions table"
Copied!
CRUD with Ruby
Authentication with Devise
Last modified 4yr ago
Copy link
Contents
1. Setup
2. Read in a CSV file
3. Parse the CSV
4. Looping through the parsed data
5. Create a database object from each row