ActiveRecord Exportable

Data portability is an extremely important feature for any software-as-a-service company. At Mobile Commons, we strongly believe that our customers’ data is their data and work hard to make sure that information is portable. Whether they want to run their own analytics or share data between different software applications, customers can easily export any Mobile Commons data to CSV.

Because this is such a common requirement, we developed a Rails plugin to automatically make any collection of items in our application (text messages, phone calls, donations, profiles, etc.) exportable in CSV format.

The main features are:

  • Transform an array of exportable records into a whole CSV file. (By default, FasterCSV transforms arrays into a singleCSV row.)
  • Export a whole table by calling .to_csv on the ActiveRecord subclass.
  • By default, export all of an ActiveRecord’s columns except for created_at and updated_at.
  • Allow simple customization of exportable columns by overriding the export_columns method in your ActiveRecord class.
  • Allow multiple CSV formats by conditionally branching inside the export_columns method depending on the format parameter.
  • Override the names of the columns that get exported (default is the database column names)
  • Allow complete customization of the export by overriding the to_row method.

The simplest use-case is just calling to_csv on an ActiveRecord model. This will export the contents of entire table to CSV format

 

1
 
Address.to_csv

Pastie #340751 linked directly from Pastie.

The more common scenario is calling it on a collection of ActiveRecord Models:

 

1
 
Address.find(:all, :conditions=>{:city=>'New York'}).to_csv

Pastie #340753 linked directly from Pastie.

 

You can customize which columns are included in the CSV by defining an export_columns function in your model class. The default is every columns except created_at and updated_at:

 

1
2
3
4
5
 
class Address < ActiveRecord::Base
  def export_columns(format = nil)
    %w[city state postal_code]
  end
end

Pastie #340730 linked directly from Pastie.

 

You can also define multiple output formats for a single model. Just modify your export_columns function to take a format parameter and return different values depending on its value.

 

1
2
3
4
5
6
7
8
9
10
11
12
 
class Address < ActiveRecord::Base

  def export_columns(format = nil)
    case format
    when :local
      %w[street1 street2 city state postal_code]
    else
      %w[city state postal_code]
    end
  end

end

Pastie #340733 linked directly from Pastie.

 

You can override any of the column names. This is useful if you use any technical terms and want to “translate” them before exporting for customers (e.g. a column named ‘guid’ might be exported as “Unique ID”).

 

1
 
Address.to_csv(:columns => [:city, :state, {:guid => :unique_id}])

Pastie #340734 linked directly from Pastie.

 

The code is quite small. You can simply save it as a file called exportable.rb in the lib folder of your Rails project or install it as a Rails Plugin from GitHub

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
require "fastercsv"

class ActiveRecord::Base
  def self.to_csv(*args)
    find(:all).to_csv(*args)
  end

  def export_columns(format = nil)
    self.class.content_columns.map(&:name) - ['created_at', 'updated_at']
  end

  def to_row(format = nil)
    export_columns(format).map { |c| self.send(c) }
  end
end

class Array
  def to_csv(options = {})
    if first.respond_to?(:to_row)
      if options[:columns]
        cols = []
        headers = []
        options[:columns].each do | column |
          if column.is_a?(Hash)
            column.each do | key, value|
              cols << value
              headers << key
            end
          else
            cols << column
            headers << column
          end
        end
      else
        headers = cols = first.export_columns(options[:format])
      end

      csv = FasterCSV.new("", options.block(:format, :columns))
      csv << headers

      each do |row|
        csv << cols.map do |col|
          row.send(col)
        end
      end

      csv.string
    else
      FasterCSV.generate_line(self, options.block(:format,:columns))
    end
  end
end

Pastie #340750 linked directly from Pastie.

 

Credit to Bryan Helmcamp and East Media, who wrote the original version for Mobile Commons.