Peter Marklund

Peter Marklund's Home


Rails Recipe: CSV Export

A common requirement from customers is the ability to export tabular data to a CSV file that can be imported into Excel. Ruby on Rails uses the standard Ruby CSV library to import test fixtures that are in CSV format. However, there is a runner up CSV library called FasterCSV which as the name suggests is faster and also has a much cleaner API. I was hesitant to use FasterCSV in production since it is still in Beta but after finding the discussion about including FasterCSV in the Ruby standard library I decided to give it a try.

The first step was to download the latest FasterCSV tgz file and extract into RAILS_HOME/vendor/fastercsv. I then added the following line to config/environment.rb:

require 'fastercsv/lib/faster_csv'

The action that exports the CSV data looks roughly like the following:

  def csv
    @list = @group.lists.find(params[:id])

    csv_string = FasterCSV.generate do |csv|
      csv << ["Type", "Name", ... more attribute names here ... ]

      @list.records.each do |record|
        csv << [record['type'],
                ... more values here ...]

    filename =[^0-9a-z]/, "_") + ".csv"
      :type => 'text/csv; charset=utf-8; header=present',
      :filename => filename)

In the corresponding controller test I check that the CSV response can be parsed with the other CSV parser, that there is a header row with expected attributes, that the number of data rows is correct, and finally that one of the rows has the correct values:

  def test_csv
    get_success :csv, :id => lists(:peterlist).id

    reader = CSV::Reader.create(@response.body)

    header = reader.shift
    ['Type', 'Name', ... more attributes here ...].each do |attribute|
      assert header.include?(attribute)

    rows = []
    reader.each do |row|
      rows << row
    assert_equal rows.size, lists(:peterlist).size
    item = lists(:peterlist).items.first
    item_row = { |row| row[1] == }.first
    assert_equal item_row[0], 'Contact'
    assert_equal item_row[1],
    ... assertions for the other attribute values here ...

To see what CSV export from a controller looks like with the Ruby CSV library - check out this blog post.

15 comment(s)


Eric Pugh said 2009-08-08 20:28:

Great example, helped me out perfectly. Love the "Receipe" approach when my needs overlap heavily with what the recipe says.

Atul Agrawal said 2009-07-01 20:06:

Thanks for the example

Leszek said 2009-06-24 12:15:

Great post. I was able to use it in my application right away. Many thanks!

Julian said 2009-05-01 09:46:

Great stuff Peter, that go me rolling very fast

lastobelus said 2008-05-20 13:07:

Re: You need to add some header garbage in for IE for this type of thing to work… ... headers[“Content-type”] = “text/plain” um, actually that does NOT work (i think it used to, was considered a bug, and has been hotfixed) text/csv works, but probably better is to use application/octet-stream
-------------------------------------------------------------------------------- said 2007-12-03 09:40:

Hi, Is there a way to get the data from database and export it to CSV directly and avoid the time taken by the ruby processor to process the data. Neha

C. Bedard said 2007-11-20 10:51:

For those who have run into MS Excel's UTF8 encoding problems with CSV, you can do the following, if you will deal only with latin-based languages: <code> ... require 'iconv' ... c ='ISO-8859-15','UTF-8') send_data(c.iconv(csv_string), :type => 'text/csv; charset=iso8859-1; header=present', :filename => filename) </code> Note that it will not work if the UTF8 source contains characters that cannot be converted to iso8859-1

Marston A. said 2007-07-03 15:07:

Great post, I'm just needing this as well. Question: Can I just paste that IE header stuff within the send_file method? Where exactly does it go?

nephish said 2007-05-21 15:17:

Hey there, thanks for this a lot ! i know this is months after it was written, but just only needed it now. thanks again

Flornet said 2006-12-30 22:05:

Yep, fast to setup, and working... just nice! I just made a tweak, I added an option on the FasterCSV.generate function : csv_string = FasterCSV.generate*(:col_sep => ";")* do |csv| Microsoft Excel seems to understand it better than ",". Thanks a lot

Peter Marklund said 2006-11-21 10:03:

Thanks Ben, didn't know about the IE headers...

ben said 2006-11-21 09:05:

You need to add some header garbage in for IE for this type of thing to work... if you're interested... 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

Matthew Bass said 2006-10-04 08:58:

Peter, thanks for the code examples. I have a client who needed a CSV export done and Ruby's CSV library was acting strangely on their production box versus my local system. Turns out the version of Ruby on the server was about a year older than my local version. I think that's why the code I wrote locally didn't work once it was deployed. Switching to FasterCSV solved my problems, though the export is still slower than I'd like. At least it's working now!