Comment on page
Loading data from a CSV file into your database
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
: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 steprequire "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.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.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"
Last modified 5yr ago