Wednesday, 29 July 2009

CSV views with FasterCSV and csv_builder

We were asked to add a "Download as CSV" link to the top of each of our reporting actions. We already had a "revenue_report" controller-action with it's own html view template... and just wanted to add something similar in CSV.

FasterCSV seemed to provide some great CSV-generation functionality... but it builds the csv and spits it out right there in the controller.


We shouldn't put view-generation code into the controllers - that splits up our nicely-decoupled MVC stack!

I've also seen some ideas about putting a to_csv method into an individual model. Now, if I'm leery about putting view-generation in the controller... I'm even less impressed by putting it into the model! But I can see the benefits of simplicity.

However - I'm still not sure it fits with our requirements. A @widget.to_csv call works just fine for the WidgetsController#show action... and probably even the WidgetsController#index action (where you can run &:to_csv or similar)... but most of our reports span multiple associations and the data is grouped, filtered, and summarised. Each of these sets needs headings explaining what it is and laying it out nicely in a way that makes sense to the user reading the CSV file. Putting that kind of visual-only functionality in your model is where it really starts to get ugly again. I am left with one big thought:

Why can't I just put my CSV-template into the views directory?

enter: csv_builder plugin

csv_builder comes with extremely minimal documentation... but it's not that hard to use. Here's a step-by-step with examples to follow:

  1. install fastercsv and csv_builder
  2. add a .csv option to the respond_to part of your action
  3. add your <actionname>.csv.csvbuilder template into your views directory
  4. Add a "Download as CSV" link for your user

Step 1: Install FasterCSV & csv_builder

sudo gem install fastercsv
./script/plugin install git://

Don't forget to restart your server!

csv_builder has already set up a format-extension handler for csv so you don't need to add it to the mime-types yourself, just start using it in your actions.

Step 2: In your controller action:

csv_builder will work out of the box even if you just add the simplest possible respond_to statement:

  respond_to |format| do
    format.html # revenue_report.html.erb
    format.xml  { render :xml => @payments }
    format.csv  # revenue_report.csv.csvbuilder

With nothing more than the above, csv_builder will grab out your template and render it in csv-format to the user.

You can tweak the FasterCSV options by setting some @-params in your action. The csv_builder gem README explains how to use those, but the most useful would be to set @filename to give your users a meaningful filename for their csv-file. You can also specify things like the file-encoding, but go look at the gem README to find out the details

Here's an example of a full controller action:

# GET /sites/1/revenue_report
# GET /sites/1/revenue_report.xml
# GET /sites/1/revenue_report.csv
def revenue_report
  @site = Site.find(params[:id])
  @payments = @site.revenue_report_payments
  respond_to do |format|
    format.html # revenue_report.html.erb
    format.xml  { render :xml => @payments }
    format.csv do
      # timestamping your files is a nice idea if the user runs this action more than once...
      timestamp ='%Y-%m-%d_%H:%M:%S')
      # passing a meaningful filename is a nice touch
      @filename = "revenue_report_for_site_#{@site.to_param}_#{timestamp}.csv"
    end # revenue_report.csv.csvbuilder

Step 3: add a template into your views directory

The final step is to add a .csv.csvbuilder template to your views directory. This is just like adding an html.erb template for your action, except that the file will have the extension .csv.csvbuilder. AFAIK csv_builder can only support template that have the same name as your action, so in my example the template would be called revenue_report.csv.csvbuilder

Your view template is where you can stash away all the FasterCSV guff that will build your csv file. This works the same way as any other FasterCSV-generated content - but just make sure you generate it into a magic array that is named 'csv'. Here's an example:

  # header row
  csv << ["Revenue report for site: #{}"]
  csv << [] # gap between header and data for visibility

  # header-row for the data
  csv << ["Date", "", "Amt (ex tax)", "Amt (inc tax)"]

  row_data = [] # for scoping
  @payments.each do |payment|
    row_data = [payment.created_at.to_s(:short)]     
    row_data << "" # column gap for visibility
    # note you can use the usual view-helpers
    row_data << number_to_currency(payment.amount_ex_tax)
    row_data << number_to_currency(payment.amount_inc_tax)

    # and don't forget to add the row to the csv
    csv << row_data.dup # it breaks if you don't dup
  end # each date in date-range
  csv << [] # gap for visbility

  # and the totals-row at the very bottom
  totals_data = ["Totals", ""] # don't forget the column-gap
  totals_data << @payments.sum &:amount_ex_tax
  totals_data << @payments.sum &:amount_inc_tax
  csv << totals_data

Step 4: add a CSV link

Something like the above code will generate a nice plain csv file and spit it out at the user in the correct encoding... but now we need something to actually let the user know that they can do this.

This is pretty simple - it just requires adding the format to your usual path-links. eg:

<%= link_to 'Download Report as CSV', site_revenue_report_path(:id => @site.to_param, :format => :csv) -%>


Testing for non-html formats still seems pretty crude. You have to manually insert the "accepts" header in the request before firing off the actual request - then manually check the response content_type. It'd be nice if we could just add :format => :csv to the request... anyway, here's a sample shoulda-style test case:

  context "CSV revenue_report" do
    setup do
      @request.accept = 'text/csv'
      get :revenue_report, :id => @site.to_param

    # variables common to all formats of revenue-report
    should_assign_to :site
    should_assign_to :payments

    should_respond_with :success
    should "respond with csv" do
      assert_equal 'text/csv', @response.content_type
  end # context - CSV for revenue report


Don't forget to add config.gem!"

I fond I needed config.gem 'fastercsv' and a require 'csv_builder' in my environment.rb. You may need a gem-listing for both (esp if you're using bundler)

Rails 3 compatitbility?

The original csv_builder plugin is not rails 3 compatible and is in fact no longer being supported. But it has officially changed hands, to the newer csv_builder gem. This is Rails-3 compatible and not backwards compatible - though he maintains a 2.3.X branch for people to submit bugs.

csvbuilder or csv_builder

While the plugin is named csv_builder, be careful to name your files with the extension: csv.csvbuilder or you'll spend hours pulling your hair out about a Missing template error while it fruitlessly searches for your csv.csvbuilder file!

All your report data must be generated in the controller!

This makes you more MVC-compliant anyway, but if you had any collections being fetched or formatted in the view... now's the time to move them into the controller action as your CSV-view will need them too.

duplicate inserted arrays

You'll notice that the example template has a set of data being added to the csv array... you'll also notice that each row is generated on the fly - then I save a *duplicated* version into the csv array. If you don't duplicate it... you may do funky things with overwriting the row each time. In my case I also needed to declare the temporary array outside the loop in order to preserve scope. YMMV - I'd appreciate any Ruby-guru answer as to why this doesn't work without that.

don't reset csv

Don't do this: csv = [] it breaks everything!

Rendering as html

I've noticed there's a pathalogical condition where everything is going well - and it's even getting o the template... but it's *still8 rendering html.

I eventually figured out that it's actually rendering the layout around the csv... and it occurs when you've explicitly stated a layout (eg layout 'admin') somewhere at the top of your controller. To fix it, you just need an empty render without layout false eg

  respond_to do |format|
    format.csv do
      timestamp ='%Y-%m-%d_%H:%M:%S')
      @filename = "revenue_report_for_site_#{@site.to_param}_#{timestamp}.csv"
      render :layout => false  # add this to override a specifically-stated layout
    end # revenue_report.csv.csvbuilder


francois.beausoleil said...

There's another way too, which doesn't require a plugin / gem. Simply generate the row from the model, or in the view.

I blogged about this a while back: Returning CSV data to the browser revisited.


Taryn said...

Hi francois,
I mentioned this in the article.

Firstly, I really don't like putting view code in the model - that breaks the MVC architecture.

Secondly - any non-trivial report is comprised of more than just one kind of model.

But mainly - I wanted to be able to layout the CSV in a report-like way for the user... thus a proper view is the only reasonable way to do this and still maintain MVC.

Oochie Wally said...

I tried the plug-in and it works, but...

The @csv_options and @output_encoding as mentioned in the documentation aren't working. I've looked in the plug-in's code and I don't see those variables used.

Am I wrong or can anybody confirm this?

efc said...

I've tried the plugin, but instead of rendering the CSV file all I can get it to do is render the source of my template as text to the browser. It does clearly find the template, so csv_builder is doing part of it's job, but that ".csvbuilder" file is not being executed, it is just being shown.

I must be missing something pretty basic. Any ideas?

efc said...

Just figured out what was wrong. Turned out to be the fact that the environment.rb file in Rails needed a couple new lines before I restarted the server. I had to add...

config.gem "fastercsv"
config.gem "csv_builder" the environment.rb file and then restart.

Taryn said...

@efc - glad to be of service ;)

Seriously though - I'm glad you figured it out, and thanks for leaving your solution here in case anyone else hits that too.

Taryn said...

@oochie wally - I'm not sure about that. The plugin may have moved on and the README may be out of date. Have you tried calling the same options but without the '@'? That would be my first guess.

Evan said...

This is great ... thank you. Much slicker than my old solution with FasterCSV and CSV generation code cluttering the controller!

An error to point out: though it requires .csv.csvbuilder extensions on the view files (as you mention near the bottom of the post), you explicitly state that it should be .csv.csv_builder at least two places in the post ... and this won't work. A quick edit is probably worthwhile.

Taryn said...

@Evan - nice catch! I've edited all the spots where I think it's meant to be the other way around.


Anonymous said...

Thank you, thank you, thank you.

Taryn said...

You're very welcome :)

Can I ask: what was most helpful to you?