in rails, how to return records as a csv file
I have a simple database table called "Entries":
class CreateEntries < ActiveRecord::Migration
def self.up
create_table :entries do |t|
t.string :firstName
t.string :lastName
#etc.
t.timestamps
end
end
def self.down
drop_table :entries
end
end
How do I write a handler that will return the contents of the Entries table as a CSV file (ideally in a way that it will automatically open in Excel)?
class EntriesController < ApplicationController
def getcsv
@entries = Entry.find( :all )
# ??? NOW WHAT ????
end
end
Solution 1:
FasterCSV is definitely the way to go, but if you want to serve it directly from your Rails app, you'll want to set up some response headers, too.
I keep a method around to set up the filename and necessary headers:
def render_csv(filename = nil)
filename ||= params[:action]
filename += '.csv'
if request.env['HTTP_USER_AGENT'] =~ /msie/i
headers['Pragma'] = 'public'
headers["Content-type"] = "text/plain"
headers['Cache-Control'] = 'no-cache, must-revalidate, post-check=0, pre-check=0'
headers['Content-Disposition'] = "attachment; filename=\"#{filename}\""
headers['Expires'] = "0"
else
headers["Content-Type"] ||= 'text/csv'
headers["Content-Disposition"] = "attachment; filename=\"#{filename}\""
end
render :layout => false
end
Using that makes it easy to have something like this in my controller:
respond_to do |wants|
wants.csv do
render_csv("users-#{Time.now.strftime("%Y%m%d")}")
end
end
And have a view that looks like this: (generate_csv
is from FasterCSV)
UserID,Email,Password,ActivationURL,Messages
<%= generate_csv do |csv|
@users.each do |user|
csv << [ user[:id], user[:email], user[:password], user[:url], user[:message] ]
end
end %>
Solution 2:
I accepted (and voted up!) @Brian's answer, for first pointing me to FasterCSV. Then when I googled to find the gem, I also found a fairly complete example at this wiki page. Putting them together, I settled on the following code.
By the way, the command to install the gem is: sudo gem install fastercsv (all lower case)
require 'fastercsv'
class EntriesController < ApplicationController
def getcsv
entries = Entry.find(:all)
csv_string = FasterCSV.generate do |csv|
csv << ["first","last"]
entries.each do |e|
csv << [e.firstName,e.lastName]
end
end
send_data csv_string, :type => "text/plain",
:filename=>"entries.csv",
:disposition => 'attachment'
end
end
Solution 3:
Another way to do this without using FasterCSV:
Require ruby's csv library in an initializer file like config/initializers/dependencies.rb
require "csv"
As some background the following code is based off of Ryan Bate's Advanced Search Form that creates a search resource. In my case the show method of the search resource will return the results of a previously saved search. It also responds to csv, and uses a view template to format the desired output.
def show
@advertiser_search = AdvertiserSearch.find(params[:id])
@advertisers = @advertiser_search.search(params[:page])
respond_to do |format|
format.html # show.html.erb
format.csv # show.csv.erb
end
end
The show.csv.erb file looks like the following:
<%- headers = ["Id", "Name", "Account Number", "Publisher", "Product Name", "Status"] -%>
<%= CSV.generate_line headers %>
<%- @advertiser_search.advertisers.each do |advertiser| -%>
<%- advertiser.subscriptions.each do |subscription| -%>
<%- row = [ advertiser.id,
advertiser.name,
advertiser.external_id,
advertiser.publisher.name,
publisher_product_name(subscription),
subscription.state ] -%>
<%= CSV.generate_line row %>
<%- end -%>
<%- end -%>
On the html version of the report page I have a link to export the report that the user is viewing. The following is the link_to that returns the csv version of the report:
<%= link_to "Export Report", formatted_advertiser_search_path(@advertiser_search, :csv) %>