Skip to main content

25 Exporting Records To CSV

Episode 45 · March 5, 2015

Learn how to export records to CSV files

CSV


class UsersController < ApplicationController
  def index
    @users = User.all

    respond_to do |format|
      format.html
      format.csv { send_data @users.to_csv, filename: "users-#{Date.today}.csv" }
    end
  end
class User < ActiveRecord::Base
  def self.to_csv
    attributes = %w{id email name}

    CSV.generate(headers: true) do |csv|
      csv << attributes

      all.each do |user|
        csv << attributes.map{ |attr| user.send(attr) }
      end
    end
  end

  def name
    "#{first_name} #{last_name}"
  end
end

Transcripts

In the last episode we talked about importing from CSV files to create records in your rails application, and in this episode we're going to talk about exporting those same records, we're going to use the same CSV libraries before, and you're going to want to make sure that you have a require 'csv' inside your application.rb, so put that right before require 'rails/all' require statement and restart your rails application. That will make sure that you have the csv library available in all cases. Once you have that, we're simply going to take our user's route, and we're going to accept the format of CSV, so we're going to create a url here called /users.csv, and this .csv file extension in the url is automatically parsed by rails as a format.

If we go to the users_controller.rb, first let's take a look at the routes, I have resources :users and that's going to be what sets up /users of course, and when we come to the controller, we have the index action and that will be what /users points into. In here we simply need to

users_controller.rb

def index
  @users = Users.all
  @import = User::Import.new 

  respond_to do |format|
    format.html
    format.csv { send_data @users.to_csv }
  end
end

app/models/user.rb

class User < ActiveRecord::Base
  def self.to_csv
    attributes = %w{id email name}

    CSV.generate(headers: true) do |csv|
      csv << attributes

      all.each do |user|
        csv << attributes.value_at(*attributes)
      end
    end
  end
end

What this allows us to do is say: Let's grab the last user, if we say User.last.attributes, this gives us a hash with string keys for each of those, and then when we do User.last.values_at("id") then it would give us the value in an array format, which is exactly what we want for our csv, and then, if we did something like "email" that's the next parameter, it would give us 25 and then [email protected]. I want to point out here that using the all scope on here is not going to override the scope that we have in the user's controller. If we use a different scope here, maybe public_users or not_deleted users, this users instance variable get set to that, and then when you pass it from the controller to the CSV method, this will be like appending the scope of all onto the previous scopes. This won't mess with your scopes at tall, it will continue keeping the same scopes, and in previous versions of rails, that would have been scoped, but they switched that to all and I think deprecated that old scoped method because this reads a little bit better.

That's as far as we need to go for handling CSV's and now when we actually execute this /users.csv we should get a downloaded file and if we open that up we get the attributes and all of the records there just like we expect. If we go and just modify one of these, let's delete the last name out of here, refresh the page and then download the file again. We should get this time the same file without these last name. Everything is working correctly and we can make adjustments as necessary. This is really nifty, and it allows us to pull out attributes exactly from the records in the database, so this is really cool. One gotcha to this is if you're using attributes, like maybe you have a name method here, and you don't want to use first and last name, if you want to do that, then you're going to have to set this up a little bit differently. See, this is not an attribute in the database, it's actually just a method on the instance, so in that case you would want to adjust this and say

app/models/user.rb

class User < ActiveRecord::Base
  def self.to_csv
    attributes = %w{id email name}

    CSV.generate(headers: true) do |csv|
      csv << attributes

      all.each do |user|
        csv << attributes.map{ |attr| user.send(attr) }
      end
    end
  end
end

What that will do is basically call a method by name, so if you have a user and we have the last user in the database, if we call send and we pass in name, it will call the method called name on the user, and we can do the same thing for things like id it's an attribute and that. We'll loop through all of those, ask the user to call that method, take the result, put it in a new array using a map, and then shovel that into a csv as a new row. That is a way of using things like these sort of fake attributes that wee've created, these helper methods around functionality like that. It allows us to do a little bit more complex things, and then if we grab users.csv and open that file, you'll see now that we have the name column and we've copied both the first and last name in there. That is a very simple introduction to importing and exporting from CSV's, I have a couple pro episodes to talk about uploading CSV's through forms if you're interested, this is very similar to pretty much exactly the same as what Ryan Bates covered in railscasts a long time ago, and the reason it hasn't really changed at all is because the CSV library is built into ruby now. It uses the exact same functionality, and that's really it. If you don't use a gem or anything like that, not much is going to change, the API is pretty stable, and all you're going to need to do is add a couple lines to your controller and one method to your model to set that up. This is a really simple way of doing that, you can also take a look at the send_data method, "send_data rails" is all you need to google for that, and it gives you some options if you want to control things like the filename, so let's grab data streaming send_data that we want to get, so you can do disposition is in line or attachment, this is really cool if you're doing PDF's because you can force the download using attachment or because browser's now render PDF's inside of them you can do a disposition is inline to render it in line. Not sure it that works for CSV's but let's try it. Disposition in line, save that and see what happens.

CSV's are going to get downloaded because they don't actually have the ability to render a CSV. It's just plain text, but that's kind of what you might expect. You can also pass in the option of filename, and imagine that you want the current date attached in there, so you might have "users-#{Date.today}" as your string, and now if you go to users.csv, the file you get downloaded will be that file name. You'll also want to make sure you get the .csv in there to control that. This is going to be useful so that you can create your own file name format so that users.csv, the filename in the url that you accessed isn't converted directly. As you saw before, I had users, and the browser was doing parenthesis one and two and so on, this allows you to kind of set this up on your own and follow the format that you would prefer. Something like dates and times is very common to do, but it's really up to you, if you're scoping things out and people are searching, you might want to put some of those search queries in the file name, but that's the basics of exporting CSV's. If you have any questions, please post a comment and I'll see you next episode.

Discussion