Follow Along as I Stumble on the Path to Learning RoR

Posts tagged “csv

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


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)


CSV Export

One gem to use to allow users to export table data, like reports, is FasterCSV.  The only thing is that there isn’t a lot of documentation on it.  Here is a great tutorial on it:

http://rubyglasses.blogspot.com/2009/07/csv-views-with-fastercsv-and-csvbuilder.html

The only minor issue I ran into was that I needed to add this line:
@output_encoding = ‘UTF-8’

under the format.csv part, like so:

format.csv do
@output_encoding = ‘UTF-8’
end