Ruby on Rails
HowtoExportDataAsCSV

The CSV text-file format is a common choice for both import and export when performing data migrations. The Ruby FasterCSV library (gem install fastercsv) does a great job working with this format.

What if you want to generate CSV files for download within a Rails application? FasterCSV provides a way to stream output to a file as you feed individual data rows to the csv object using <<. You could generate a file on your server and then return it to the client using send_file. However, looking at the implementation of send_file, I discovered a way to have FasterCSV send its formatted output directly to the HTTP response. See the “stream_csv” method below, which nicely encapsulates all the setup code for reuse by multiple actions that export data this way.

require 'fastercsv'

class ReportController

  # example action to return the contents
  # of a table in CSV format
  def export_users
    users = User.find(:all)
    stream_csv do |csv|
      csv << ["first","last","id","email"]
      users.each do |u|
        csv << [u.first,u.last,u.id,u.email]
      end
    end
  end

  private
    def stream_csv
       filename = params[:action] + ".csv"    
	
       #this is required if you want this to work with IE		
       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 :text => Proc.new { |response, output|
        csv = FasterCSV.new(output, :row_sep => "\r\n") 
        yield csv
      }
    end

end

Functional Test

Here’s a simple test for the above controller. It tests to make sure that the csv request is a success and that the response matches ‘csv_data’.

class ReportControllerTest < Test::Unit::TestCase
  fixtures :users
    
  def setup
    @controller       = ReportController.new
    @request          = ActionController::TestRequest.new
    @response         = ActionController::TestResponse.new
  end
  
  def test_export_users
    get :export_users
    
    assert_response :success
    assert_kind_of Proc, @response.body
    
    require 'stringio'
    output = StringIO.new
    assert_nothing_raised { @response.body.call(@response, output) }
    assert_equal csv_data, output.string
  end
  
  private 
    def csv_data
      "first,last,id,email\r\nQuentin,Smith,1,<a href="mailto:quentin@example.com">quentin@example.com</a>\r\nAaron,Jones,2,<a href="mailto:aaron@example.com">aaron@example.com</a>"
    end
end

Alternative Cache-Control

If the above doesn’t work for you, try setting Cache-Control to private when IE:

headers['Cache-Control'] = 'private'

UTF8 Encoding Issues

Ms Excel does not properly handle UTF8-encoded CSV files. If you’re dealing with Latin-based languages only, you can convert the csv stream to ISO-8859-1 using Iconv:

require 'fastercsv'

require 'iconv'

class ReportController
   ....

  private
    def stream_csv
      .....
      .....
      # replace the render call with this:
       output = StringIO.new
       csv = FasterCSV.new(output, :row_sep => "\r\n") 
       yield csv
       c = Iconv.new('ISO-8859-15','UTF-8')  
       render :text => c.iconv(output.string) 
    end

CSV Builder Plugin

Alternatively you can install the CSV Builder Plugin
end allowing you to write simple CSV templates using fastercsv.

Send data to the browser using send_data

You can directly send data to the browser using ActionController::Streaming send_data instead of the method above. This is tested on Rails 2.1.

In the controller, use the snippet below to get it done.

require 'fastercsv'

class ReportController

  # example action to return the contents
  # of a table in CSV format
  def export_users
    users = User.find(:all)
    csv_string = FasterCSV.generate do |csv| 
      csv << ["first","last","id","email"]
      users.each do |u|
        csv << [u.first,u.last,u.id,u.email]
      end
    end
    send_data csv_string, :type => "text/plain", 
     :filename=>"export_#{session.model.id}.csv", 
     :disposition => 'attachment'
  end