In rails, how to return records as a CSV file

Question

I have a simple database table called "Records":

class CreateEntries < ActiveRecord::Migration def self.up create_table :entries do |t| t.string :firstName t.string :lastName #etc. t.timestamps end end def self.down drop_table :entries end end 

How to write a handler that will return the contents of the record table as a CSV file (ideally, in such a way that it will be automatically opened in Excel)?

 class EntriesController < ApplicationController def getcsv @entries = Entry.find( :all ) # ??? NOW WHAT ???? end end 

Answer 1

There is a plugin called FasterCSV that does a great job of this.

Answer 2

FasterCSV is definitely the way to go, but if you want to serve it directly from your Rails application, you will also need to configure some response headers.

I use the method to configure the file name and required headers:

 def render_csv(filename = nil) filename ||= params[:action] filename += '.csv' if request.env['HTTP_USER_AGENT'] =~ /msie/i headers['Pragma'] = 'public' headers["Content-type"] = "text/plain" headers['Cache-Control'] = 'no-cache, must-revalidate, post-check=0, pre-check=0' headers['Content-Disposition'] = "attachment; filename=\"#{filename}\"" headers['Expires'] = "0" else headers["Content-Type"] ||= 'text/csv' headers["Content-Disposition"] = "attachment; filename=\"#{filename}\"" end render :layout => false end 

Using this, you can easily have something like this in my controller:

 respond_to do |wants| wants.csv do render_csv("users-#{Time.now.strftime("%Y%m%d")}") end end 

And have a view that looks like this: ( generate_csv is from FasterCSV)

 UserID,Email,Password,ActivationURL,Messages <%= generate_csv do |csv| @users.each do |user| csv << [ user[:id], user[:email], user[:password], user[:url], user[:message] ] end end %> 

Answer 3

I accepted (and voted!) @Brian's answer, first pointing me to FasterCSV. Then, when I googled to find the gem, I also found a pretty complete example on this wiki page . Combining them, I settled on the following code.

By the way, the gem install command: sudo gem install fastcsv (all in lower case)

 require 'fastercsv' class EntriesController < ApplicationController def getcsv entries = Entry.find(:all) csv_string = FasterCSV.generate do |csv| csv << ["first","last"] entries.each do |e| csv << [e.firstName,e.lastName] end end send_data csv_string, :type => "text/plain", :filename=>"entries.csv", :disposition => 'attachment' end end 

Answer 4

Another way to do this without using FasterCSV:

Require ruby ​​csv library in initializer file, e.g. config / initializers / dependencies.rb

 require "csv" 

Like some background, the following code is based on Ryan Bate’s advanced search form , which creates a search resource. In my case, the show method of a search resource returns the results of a previously saved search. It also responds to csv and uses a presentation template to format the desired output.

  def show @advertiser_search = AdvertiserSearch.find(params[:id]) @advertisers = @advertiser_search.search(params[:page]) respond_to do |format| format.html # show.html.erb format.csv # show.csv.erb end end 

The show.csv.erb file is as follows:

 <%- headers = ["Id", "Name", "Account Number", "Publisher", "Product Name", "Status"] -%> <%= CSV.generate_line headers %> <%- @advertiser_search.advertisers.each do |advertiser| -%> <%- advertiser.subscriptions.each do |subscription| -%> <%- row = [ advertiser.id, advertiser.name, advertiser.external_id, advertiser.publisher.name, publisher_product_name(subscription), subscription.state ] -%> <%= CSV.generate_line row %> <%- end -%> <%- end -%> 

In the html version of the report page, I have a link to export the report that the user is viewing. Below is the link_to link, which returns the csv report version:

 <%= link_to "Export Report", formatted_advertiser_search_path(@advertiser_search, :csv) %> 

Answer 5

Take a look at the FasterCSV gem.

If you need excel support, you can also look at creating xls directly. (See Table: Excel)

 gem install fastercsv gem install spreadsheet-excel 

I find these options good for opening a csv file in Windows Excel:

 FasterCSV.generate(:col_sep => ";", :row_sep => "\r\n") { |csv| ... } 

As for the ActiveRecord part, something like this:

 CSV_FIELDS = %w[ title created_at etc ] FasterCSV.generate do |csv| Entry.all.map { |r| CSV_FIELDS.map { |m| r.send m } }.each { |row| csv << row } end 

Answer 6

You need to set the Content-Type header in the response, and then send the data. Content_Type: application / vnd.ms-excel should do the trick.

You can also set the Content-Disposition header to look like an Excel document, and the browser selects a reasonable default file name; something like Content-Disposition: attachment; file name = "# {} suggested_name.xls"

I suggest using quickcsv ruby ​​gem to create a CSV, but there is also a built-in csv. The fastcsv sample code (from the gem documentation) is as follows:

 csv_string = FasterCSV.generate do |csv| csv << ["row", "of", "CSV", "data"] csv << ["another", "row"] # ... end 

Answer 7

The following steps worked well for my case and make the browser open the corresponding application for the CSV type after loading.

 def index respond_to do |format| format.csv { return index_csv } end end def index_csv send_data( method_that_returns_csv_data(...), :type => 'text/csv', :filename => 'export.csv', :disposition => 'attachment' ) end 

Answer 8

try a good stone to create CSV from Rails https://github.com/crafterm/comma

Answer 9

Take a look at the CSV Shaper gem.

https://github.com/paulspringett/csv_shaper

It has good DSL and works great with Rails models. It also processes response headers and allows you to customize the file name.

Answer 10

If you just want to get the csv database yourself from the console, you can do this in a few lines

 tags = [Model.column_names] rows = tags + Model.all.map(&:attributes).map(&:to_a).map { |m| m.inject([]) { |data, pair| data << pair.last } } File.open("ss.csv", "w") {|f| f.write(rows.inject([]) { |csv, row| csv << CSV.generate_line(row) }.join(""))}