ToCSV¶ ↑

ToCSV is a gem for converting arrays or scopes (Rails) to CSV by calling to_csv. These arrays can contain different data structures, as long as they are homogeneous, like the ones described below:

• A simple array of anything that responds to to_s: ['Date', Time.now].to_csv

• An array of hashes: [ {'Name' => 'Icaro', 'Age' => 23}, {'Name' => 'Gabriel', 'Age' => 16} ].to_csv

• A matrix: [['Name', 'Age'], ['Icaro', 23], ['Gabriel', 16]].to_csv

• A hash like array: [ [['Name', 'Icaro'], ['Age', 23]], [['Name', 'Gabriel'], ['Age', 16]] ].to_csv

• An array of ActiveRecord objects: @users.to_csv(:except => [:password, :phone], :timestamps => true)

• Scopes in Rails

ToCSV has been tested with Ruby 1.8.7-p299/1.9.1-p378/1.9.2-rc1.

If you are using Ruby 1.9 the only dependency to use the basic features is ActiveSupport. Otherwise you will need fastercsv. You will receive a warning if you don’t meet the requirements.

# If you're using RVM, start with:
$ sudo gem install to-csv

# Then, if you don't have Rails installed...
$ sudo gem install activesupport

# And if your Ruby version is lower than 1.9
$ sudo gem install fastercsv

Full compatibility with Rails 3 is on the way…as well as a new API, with new features.

If you want to use this gem with Rails, put the following requirement in your environment.rb:

config.gem 'to-csv', :lib => 'to_csv', :source => 'http://rubygems.org'

After that, if you need to globally configure the gem, just create a to_csv.rb file in initializers.

ToCSV.byte_order_marker = true
ToCSV.timestamps        = true
ToCSV.locale            = 'en-US'
ToCSV.primary_key       = false
ToCSV.csv_options[:col_sep] = ','
ToCSV.csv_options[:row_sep] = "\r\n"

Let’s start with the most simple example.

['Alfred Hitchcock', 'Robert Mitchum', 'Lucille Ball'].to_csv
#=> "Alfred Hitchcock;Robert Mitchum;Lucille Ball\n"

Or, if we have an array of arrays (i.e. a matrix) we can create tabular data.

[
  ['Name', 'Gender'],
  ['Alfred',  'M'],
  ['Robert',  'M'],
  ['Lucille', 'F']
].to_csv #=> "Name;Gender\nAlfred;M\nRobert;M\nLucille;F\n"

Almost always, when we generate CSV files, we want it to have appropriate headers, so a better approach might be to use an array of hashes.

[
  { 'Name' => 'Alfred',  'Gender' => 'M' },
  { 'Name' => 'Robert',  'Gender' => 'M' },
  { 'Name' => 'Lucille', 'Gender' => 'F' }
].to_csv #=> "Gender;Name\nM;Alfred\nM;Robert\nF;Lucille\n"

Look carefully to the above output. You can see that when we use hashes we can no longer be sure of the headers’ order (true for Ruby versions older than 1.9). When we are working with tabular data the headers’ order can be very important, thus we can use a somewhat similar data structure:

[
  [ ['Name', 'Alfred'],  ['Gender', 'M'] ],
  [ ['Name', 'Robert'],  ['Gender', 'M'] ],
  [ ['Name', 'Lucille'], ['Gender', 'F'] ]
].to_csv #=> "Name;Gender\nAlfred;M\nRobert;M\nLucille;F\n"

That’s a lot to type… The first example was much simpler…

There is the headers option. You can use it in all the examples above to enable/disable headers from the output. Default is to show (true).

users = [{ 'Name' => 'Alfred',  'Gender' => 'M' }]
users.to_csv(:headers => false)

When we’re building our data like the previous examples we have very few options compared to what can be passed when converting an array of AR objects. Again, the easiest way:

# Anywhere in your app.
# By default, all available model attributes (DB columns) are going to be used
# except timestamps and the primary key of the record
@users = User.all
File.open('path/to/file.csv', 'w') { |io| io.puts @users.to_csv }

You can control the order and the text of any header. You can accomplish that in various ways.

By default all attribute/method names will be sorted in alphabetical order. So imagine a person model have name, age and email as attributes, and you want to get the following output:

Name | E-mail | Age
...  | ...    | ..
...  | ...    | ..

You can tell to-csv to use a specific locale. If you don’t, it uses your app current locale. It will try to translate attributes to a more friendly text by using the scope activerecord.attributes.<model name>. If the translation doesn’t exist the header’s text is going to be humanized.

The order of columns can be changed with the option headers. The way this option works is very similar to the plugins method in your Rails environment.rb file.

• If you pass nil (default) then headers/columns will be in alphabetical order.

• If you pass an empty array or false, no headers will be shown.

• Instead, if you pass a non empty array, headers will be sorted in the order specified. :all can be used as a placeholder for all attributes not explicitly named.

So, in our example above, we can say:

@users.to_csv(:headers => [:name, :email, :age])

Or, using the placeholder all, which is not very useful here:

@users.to_csv(:headers => [:name, :email, :all])

If you want a completely different result you could, for instance, map all users to a hash. Example:

# This makes use of a hash to completely change the CSV output.
@users.map do |user|
  {
    'Name' => user.name,
    'Age'  => user.age,
    'Total Comments' => user.comments.count
  }
end.to_csv

Sometimes you may want to change just one value out of six for example. The best way to go is to pass a block so that you don’t have to repeat yourself writing five headers and it’s obvious values and also loosing I18n header translations.

# The block yields a new Struct for each object in the list. By calling
# methods on this struct you can change their default values.
@users.to_csv do |row, user|
  row.date_of_birth = user.date_of_birth.to_s(:long)
end

If you have an AR object with many relationships and you want to include these to CSV results, you can use :include option.

Examples:

# If you want to include a <tt>has_many</tt> relationship, you can do the following:
User.all.to_csv(:include => :posts)

# If you want to include a <tt>belongs_to</tt> relationship:
User.all.to_csv(:include => :organization)

# Or you can use both in the same time.
User.all.to_csv(:include => [:organization, :posts])

# users/index.html.haml
= link_to 'export (CSV)', users_url(:csv)

# UsersController#index
class UsersController < ApplicationController
  def index
    @users = User.most_active
    respond_to do |format|
      format.html
      format.csv do
        send_data User.csv(@users), :filename => 'users_report.csv'
      end
    end
  end
end

# User model
class User < ActiveRecord::Base
  def self.csv(users)
    users.csv(:headers => [:id, :all], :primary_key => true, :except => :password) do |row, user|
      row.id = "%04d" % user.id
      row.created_at = I18n.l(user.created_at, :format => :default)
    end
  end
end

# locales/en-US.yml
activerecord:
  attributes:
    user:
      id: Code

You can always customize the output if you wish by building arrays of hashes, arrays of arrays of bidimensional arrays etc :). Or you can obviously mix anything you want and even use FasterCSV directly.

@user.to_csv { :only => [:name, :email] }, :col_sep => ','

There are other options for you to customize the output. Take a look at the to_csv method documentation.

Special thanks to these people for their contributions and/or ideas:

• Kyle J. Ginavan

• Mauro Torres

• petRUShka

Copyright © 2010 Ícaro Leopoldino da Motta, released under the MIT license.