Ian Sheridan's Homepage

Export data as CSV with Rails 3 and Ruby 1.9.2

So I had to do this. It's something that we do all the time. Exporting data for our end users. We give them a grid of information and they want to select rows and export them. Simple ... well not so much but there are gems that will help us.

I am working on this project with Rails 3.0.2. And I am working with a sales database of registrants for a campaign. So what we need to to is three things making sure that the model can send us the data in the expected format. make sure out controller can respond to the CSV requests. And finally make sure that our web page can do what the user wants.

Monkey Patching Array class

We need to add a to_csv method to the data that is returned in Rails we usually receive an Array of objects. So we will be monkey patching the Array object with the following:

require 'csv'
class Array
    def to_csv
        if self.first.class.superclass == ActiveRecord::Base
            first_row = self.first.attributes.collect{|c| c[0] }
            CSV.generate do |csv|
                csv << first_row
                self.each do |row|
                    csv << first_row.collect{ |attribute| row[attribute] }
                end
            end
        else
            CSV.generate_line(self, Hash.new)
        end
    end
end

What this does is to generate a CSV string from the returned recordset coming out of the database, makes sure that it's a ActiveRecord model object and inserts the column names as the first row in the exported file followed by all the data.

I put this monkey patching in a file called 'arraytocsv.rband throw it in thelib` directory of my application.

Export CSV action in your controller

There are two things that you do here. Create the action (along with a route) and add a new renderer. The controller action is easy:

# expects a cama delimited list of registrant GUIDs
def export
    @registrants = Registrant.find(params[:ids].split(","))
    respond_to do |format|
        format.csv { render :csv => @registrants } # with mime type CSV, will attempt to run `to_csv` on what you give to the :csv option
    end
end

By default Rails does not respond to the CSV format above. We have to add one.

ActionController::Renderers.add :csv do |csv, options|
    self.content_type ||= Mime::CSV
    self.response_body  = csv.respond_to?(:to_csv) ? csv.to_csv : csv
end

I put this in an initialization file here config/initializers/add_format_csv.rb. This make it available to all the controllers in the application.

Now the controller responds to /registrants/export.csv?ids=1,2,3,4,5,6 with a nice CSV file. BAM! We are so close!

Making the link on the page work

I use jQuery and so this turns out to be a simple task. I just set up to watch for te click event on the export link and when clicked we turn off the normal behavior for clicking a link and then we make the browser go to the location that the controller will respond to. Here is the code:

$("#link-registrant-export").click(function(e){
    e.preventDefault(); // stop the browser from following
    // find all the CHECKED rows in the table 
    selected_registrants = $("#registrant-list .data-export input:checked");
    found_ids = [];
    // populate the found_ids array
    selected_registrants.each(function(){
        found_ids.push($(this).val());
    });
    // if any IDs were found as checked then join then together then download the file
    if(found_ids.length > 0){
        window.location.href = '/registrants/export.csv?ids='+found_ids.join(",");
    }
});

OK, maybe not so simple but this is what we do. We make really complicated things simple for our users.

I would like to credit a couple of links I used to form this solution. As you have found this page so I have found others.

http://stackoverflow.com/questions/4207524/how-to-export-table-data-as-csv-file-in-ruby-1-9-2-rails3
http://stackoverflow.com/questions/5844033/rails-3-format-csv-gives-no-template-error-but-format-json-needs-no-template
http://stackoverflow.com/questions/1296085/download-file-using-jquery

© 2014 Ian Sheridan, all rights reserved.