Follow Along as I Stumble on the Path to Learning RoR

Database

Seeding DB with Paperclip Images with CSV

I needed to seed my app, hosted on Heroku, with new users.  Since I wanted to created a lot of the them, I wanted to CSV.  My users have avatars, so I wanted to upload those for the users as well.

Here’s how I did it:

1. I stored the original images I wanted to use for the avatars on AWS S3 (make sure that they are set with public permission to read them.

2. I created a CSV that had columns (in this order) for the users: username, email, password, password_confirmation, avatar.  In the avatar field, I put the URL of the S3 image.  I named this CSV file “user_data” and put it in the DB directory.

3. Using the answer to this Stack Overflow question, I created a file (I named user_seed.rake) in lib/tasks.  The code in the file I used was:

require ‘csv’

namespace :csv do

desc “Import CSV Data”
task :import_stuff => :environment do

csv_file_path = ‘db/user_data.csv’

CSV.foreach(csv_file_path) do |row|
User.create!({
:username => row[0],
:email => row[1],
:password => row[2],
:password_confirmation => row[3],
:avatar => URI.parse(row[4])
})
puts “Row added!”
end
end

end

4.  Run rake csv:import_stuff


Selecting Last Records from Table

I needed to select the last records in a table.  I could do a MySQL query with no limit on records, but that could be inefficient.  Instead you could and this to the end of your MySQL query:

order by id desc limit x

For example:

SELECT * FROM articles order by id desc limit 5

This will return the last five records in the table.


Importing CSV into MySQL table on Windows

After completing development of one of my apps, I had to load one of my MySQL tables with data.  After putting the data in CSV format, I wanted to upload it to my MySQL table.  I searched the web and found the syntax to use (I use MySQL workbench and from there I went into the database and created and executed a new query).  However, it took awhile for me to figure out how to make the file name work.

Here’s the syntax I used in MySQL Workbench for the query to execute (replace the text in colors with your data):

load data local infile ‘C://yourdirectory/yourdirectory2/filename.csv‘ into table your_table_name fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’
(field1, field2, field3, field4, field5)

(for some reason the characters don’t copy correctly from this post, so you can copy similar syntax from here)