in rails, how to return records as a csv file

Question

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

At least in the more recent versions of Rails, you can also use `Entry.all` instead. — nonopolarity, Sep 29 '10 at 00:47

score 91 · Answer 1 · answered Sep 18 '08 at 17:21

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 %>

Hmm, I thought I was done until I saw your answer! Thanks for the header details, I'll remember those in case I get into trouble with what I'm using so far. — Eric, Sep 18 '08 at 17:27
As mentioned above, FasterCSV is just CSV in ruby 1.9 and up. The gererate_csv method is now CSV.generate. — Robin Clowers, Aug 10 '11 at 01:24
Adding .html_safe on the end of the generate_csv/CSV.generate block will make it so that any commas in the data are properly handled. Without this call my csv file had a bunch of "" in it. — stcorbett, Oct 10 '11 at 20:07
I've found myself referring to this answer over and over, so I finally bit the bullet and made a Rails 3 gem that provides a slightly modified version of the render_csv method provided here: https://github.com/spindance/rendered_csv — David van Geest, Dec 11 '12 at 22:25

score 28 · Answer 2 · answered Sep 18 '08 at 17:24

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

I would suggest moving the CSV generation into a view: that's more presentation logic than you'd normally want in a controller. — Clinton Dreisbach, Sep 18 '08 at 17:28
You may want to set the type to 'text/csv'. Otherwise, safari will save it as 'entries.csv.txt' — silasjmatson, Jan 16 '13 at 20:03

score 26 · Answer 3 · answered Oct 21 '08 at 17:16

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) %>

Dont forget to use `<%= CSV.generate_line(row).html_safe %>` if you are using rails 3, to avoid escaping characters. — dombesz, Apr 13 '11 at 14:54
This solution works fine but I had to change the CSV.generate_line calls setting :row_sep to nil. This change removes unwanted blank lines from the response. Code: `<%= CSV.generate_line row, {:row_sep => nil} %>` — Wilson Freitas, Dec 07 '11 at 17:59
To add to Wilson's comment, Heroku (cedar stack -beta) will insert blank lines into csv files unless you explicitly tell it not to with `:row_sep => nil`. — nslocum, Jan 13 '12 at 20:57
I also had to add a '-' at the end of the generate_line_headers. Otherwise there was a blank line after the header line. — drudru, Jul 10 '13 at 22:56

score 23 · Accepted Answer · answered Sep 18 '08 at 17:07

23

There is a plugin called FasterCSV that handles this wonderfully.

answered Sep 18 '08 at 17:07

Brian

4,931
3
32
55

30

Note that ruby >= 1.9, FasterCSV is now the standard CSV library, and is just called CSV. – kdt Jan 25 '11 at 17:23
the key being `require 'csv'`, see https://ruby-doc.org/stdlib/libdoc/csv/rdoc/CSV.html – mb21 Feb 24 '17 at 08:46

kch · Answer 5 · 2008-09-18T17:15:04.447

Take a look into the FasterCSV gem.

If all you need is excel support, you might also look into generating a xls directly. (See Spreadsheet::Excel)

gem install fastercsv
gem install spreadsheet-excel

I find these options good for opening the csv file in Windows Excel:

FasterCSV.generate(:col_sep => ";", :row_sep => "\r\n") { |csv| ... }

As for the ActiveRecord part, something like this would do:

CSV_FIELDS = %w[ title created_at etc ]
FasterCSV.generate do |csv|
  Entry.all.map { |r| CSV_FIELDS.map { |m| r.send m }  }.each { |row| csv << row }
end

score 2 · Answer 6 · answered Jun 22 '12 at 00:02

The following approached worked well for my case and causes the browser to open the appropriate application for the CSV type after downloading.

def index
  respond_to do |format|
    format.csv { return index_csv }
  end
end

def index_csv
  send_data(
    method_that_returns_csv_data(...),
    :type => 'text/csv',
    :filename => 'export.csv',
    :disposition => 'attachment'
  )
end

score 2 · Answer 7 · answered Sep 18 '08 at 17:16

You need to set the Content-Type header in your response, then send the data. Content_Type: application/vnd.ms-excel should do the trick.

You may also want to set the Content-Disposition header so that it looks like an Excel document, and the browser picks a reasonable default file name; that's something like Content-Disposition: attachment; filename="#{suggested_name}.xls"

I suggest using the fastercsv ruby gem to generate your CSV, but there's also a builtin csv. The fastercsv sample code (from the gem's documentation) looks like this:

csv_string = FasterCSV.generate do |csv|
  csv << ["row", "of", "CSV", "data"]
  csv << ["another", "row"]
# ...
end

Thanks for that obscure Content-Type! I'm not actually sure if it's going to be Excel in the end, but that's good to know. — Eric, Sep 18 '08 at 17:28

score 1 · Answer 8 · answered Jul 17 '12 at 07:00

1

try a nice gem to generate CSV from Rails https://github.com/crafterm/comma

answered Jul 17 '12 at 07:00

Henry Jacob

101
1
2

1

Good answer but to make it great, perhaps you could explain why this gem (as opposed to others) – Taryn East Oct 10 '12 at 23:54

score 0 · Answer 9 · answered Jul 25 '12 at 19:15

0

Take a look at the CSV Shaper gem.

https://github.com/paulspringett/csv_shaper

It has a nice DSL and works really well with Rails models. It also handles the response headers and allows filename customisation.

answered Jul 25 '12 at 19:15

paul

307
2
4

boulder_ruby · Answer 10 · 2013-07-19T01:34:51.950

0

If you're simply wanting to get the csv database yourself from the console you can do so in a few lines

tags = [Model.column_names]
rows = tags + Model.all.map(&:attributes).map(&:to_a).map { |m| m.inject([]) { |data, pair| data << pair.last } }
File.open("ss.csv", "w") {|f| f.write(rows.inject([]) { |csv, row|  csv << CSV.generate_line(row) }.join(""))}

edited Jul 19 '13 at 01:34

answered Jul 19 '13 at 01:25

boulder_ruby

38,457
9
79
100

in rails, how to return records as a csv file

10 Answers10

Linked