Loading data from a CSV file into your database

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:

rails generate task slurp transactions

This will create a file called lib/tasks/slurp.rake that looks like this:

namespace :slurp do
desc "TODO"
task transactions: :environment do
end
end

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

require "csv"
csv_text = File.read(Rails.root.join("lib", "csvs", "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 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

require "csv"
csv_text = File.read(Rails.root.join("lib", "csvs", "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 that the first line in the file has column headings in it, not a row of data.

4. Looping through the parsed data

require "csv"
csv_text = File.read(Rails.root.join("lib", "csvs", "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", "csvs", "real_estate_transactions.csv"))
csv = CSV.parse(csv_text, :headers => true, :encoding => "ISO-8859-1")
csv.each do |row|
t = Transaction.new
t.street_address = row["street"]
t.city = row["city"]
t.zip = row["zip"]
t.state = row["state"]
t.bedrooms = row["beds"]
t.square_feet = row["sq_feet"]
t.category = row["type"]
t.sold_on = row["sale_date"]
t.price = row["price"]
t.lat = row["latitude"]
t.lng = row["longitude"]
t.save
puts "#{t.street_address}, #{t.zip} saved"
end
puts "There are now #{Transaction.count} rows in the transactions table"