Exporting to CSV with Rails

Exporting data to CSV is an excellent way to move your data out of a database and into a spreadsheet. The ability to export data from a Rails application is especially useful when you want to use it in another application or process.

The benefits of exporting your data include:

Quickly load large amounts of data into databases and spreadsheets
Easy to import into other applications: If you want to use your exported CSV file on another program such as Google Apps or Salesforce, then this is possible as long as they both accept CSV files (which they do).
Smaller file sizes than XML or JSON

This post will discuss how to export data as a CSV from the Rails controller by using formats. First, set up the application and seed in some data.

rails new csv-export
bundle
rails db:create db:migrate
rails g scaffold Post title body:text author:string likes:integer comments:integer
rails db:migrate

Let’s put some data in:

# db/seeds.rb
Post.create(title: "Post 1", body: "This is the content of Post 1", author: "The first Author", likes: 1000, comments: 550)
Post.create(title: "Post 2", body: "This is the content of Post 2", author: "The second Author", likes: 2000, comments: 650)
Post.create(title: "Post 3", body: "This is the content of Post 3", author: "The third Author", likes: 3000, comments: 750)
Post.create(title: "Post 4", body: "This is the content of Post 4", author: "The forth Author", likes: 4000, comments: 850)
Post.create(title: "Post 5", body: "This is the content of Post 5", author: "The fifth Author", likes: 5000, comments: 950)
Post.create(title: "Post 6", body: "This is the content of Post 6", author: "The sixth Author", likes: 6000, comments: 1050)

Now, in post.rb, declare a method which will be responsible for generating data in CSV format. Depending upon your Rails version and the dependencies added, it’s possible you’ll need to add a require statement.

# app/models/post.rb

require 'csv'
def self.to_csv
 posts = all
 CSV.generate do |csv|
   csv << column_names
   posts.each do |post|
     csv << post.attributes.values_at(*column_names)
   end
 end
end

Next, update the index action in posts_controller.rb like below:

# app/controllers/posts_controller.rb

def index
 @posts = Post.all
 respond_to do |format|
   format.html
   format.csv { send_data Post.to_csv, filename: "posts-#{DateTime.now.strftime("%d%m%Y%H%M")}.csv"}
 end
end

Lastly, in posts/index.html.erb, add a button like this:

# app/views/posts/index.html.erb

<%= link_to "Export", posts_path(format: 'csv') %>

Now a CSV file will be generated and downloaded on the system. Happy exporting!