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.
1
2
3
4
5
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:
1
2
3
4
5
6
7
# 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.
1
2
3
4
5
6
7
8
9
10
11
12
# 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:
1
2
3
4
5
6
7
8
9
# 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:
1
2
3
# 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!