Peter Marklund

Peter Marklund's Home

Wed August 16, 2006
Programming

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'],
                record['name'],
                ... more values here ...]
      end
    end

    filename = @list.name.downcase.gsub(/[^0-9a-z]/, "_") + ".csv"
    send_data(csv_string,
      :type => 'text/csv; charset=utf-8; header=present',
      :filename => filename)
  end

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

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

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

Comments

Matthew Bass said over 7 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 7 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 7 years ago:

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

--------------------------------------------------------------------------------

Flornet said over 7 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 7 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 7 years ago:

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?

--------------------------------------------------------------------------------

C. Bedard said over 6 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:

<code>
...
require 'iconv'
...
c = Iconv.new('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

--------------------------------------------------------------------------------

neharohan.chopra@gmail.com said over 6 years ago:

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

--------------------------------------------------------------------------------

lastobelus said over 6 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 5 years ago:

Great stuff Peter, that go me rolling very fast

--------------------------------------------------------------------------------

Leszek said over 5 years ago:

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

--------------------------------------------------------------------------------

Atul Agrawal said over 5 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.

--------------------------------------------------------------------------------