Peter Marklund

Peter Marklund's Home

Wed August 16, 2006

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.


Matthew Bass said over 8 years ago:

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!


ben said over 8 years ago:

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

Peter Marklund said over 8 years ago:

Thanks Ben, didn’t know about the IE headers…


Flornet said over 8 years ago:

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


nephish said over 8 years ago:

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

thanks again


Marston A. said over 8 years ago:

Great post, I'm just needing this as well.


Can I just paste that IE header stuff within the send_file method? Where exactly does it go?


C. Bedard said over 7 years ago:

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:

require 'iconv'
c ='ISO-8859-15','UTF-8')
:type => 'text/csv; charset=iso8859-1; header=present',
:filename => filename)

Note that it will not work if the UTF8 source contains characters that cannot be converted to iso8859-1

-------------------------------------------------------------------------------- said over 7 years ago:


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.



lastobelus said over 7 years ago:

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


Julian said over 6 years ago:

Great stuff Peter, that go me rolling very fast


Leszek said over 6 years ago:

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


Atul Agrawal said over 6 years ago:

Thanks for the example


Eric Pugh said over 5 years ago:

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