Skip to main content
27 Import and Export CSV Files:

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


Fallback

Is there some magic happening when you call @users.to_csv ?? As I understand @users is an ActiveRecord::Relation, how is the method 'to_csv' sended to the User class ??

Fallback

I believe that ActiveRecord::Relation remembers which model class it originated from, so when you call "to_csv" on a Relation it knows to delegate it to the User class. This is how scopes work too. It functions in pretty much the exact same way.

Fallback

Maybe is time to read some source code. I haven't thought of how scopes work.
Thanks for your answer and for the videos !!

Fallback

I am still confused. You did inside the controller action `User.all`, and then when you called `@users.to_csv`, then inside the `User` model also you did call `self.all`. Why the double call one inside the *model* and another inside the controller action ?

Fallback

Well so you want to do all your scopes as normal in the controller so it applies to both the regular HTML response and the csv response.

The "all" is in the method because that is how reference the current scope. It used to be called "scoped" but allows for you to take the relation from the controller and extend it with our CSV code.

Fallback

Good question! Actually to_csv is an instance method of Array. You can see how it is delegated to Array in the method_missing in this file: https://github.com/rails/ra...


Fallback

Awesome! I know RailsCast already did this a long time ago, but its great to see it redone with the updates to the syntax.


Fallback

Newbie question...I have two models a user model and a coupons model that are associated (coupons belong to user). How do I include the export of a parent model (like user.name field) in the CSV attributes?

Fallback

Since that's not an attribute, you can just swap this out:

csv << attributes.map{ |attr| user.send(attr) }

With something like this:

csv << [user.name, user.email, user.coupon.code]

That way you can just reference any attributes or methods you want when exporting and they don't have to be database attributes.

Fallback

Thanks Chris. Great videos and thanks for the support!

Fallback

Hi Chris -- another noob question, I am trying to do something like Nate (and export parent model), but my end format needs to be (using Nate's field examples) two columns with the name, email and coupon code combined in the first column (just a space separating each) and the 2nd column is the user ID. How would I do that using your example code? I tried using .join but that didn't work. Thanks in advance!

Fallback

Something like this? You'd basically create your own string to add to the CSV as the first column.

csv << ["#{user.name} #{user.email} #{user.coupon.code}", user.id]

Fallback

Oh man, thanks! I tried something similar, but noob me forgot the quotes! Thanks for the quick answer.

Fallback

Can you elaborate a little more? I tried the above and couldn't get it to work.


Fallback

Hey Chris, Thanks for an amazing video. I had trouble with Ryan Bate's course, but yours works perfectly on rails 4.2

I have a question. I have a table with Invoices (scoped by created desc & limit 10) which represents each row on the csv and a table footer which is an active record calculations for the sum of each attributes. I would like to also place them in the csv but haven't found any resource. Could you give me some hint on how I can implement it?

Thanks alot in advance. I really appreciate your contributions. It has made me learn a bunch!

Fallback

For that, basically all you need to do is shovel onto the CSV at the end after you loop through the invoices.

You can just say csv << ["custom", "footer"]


Fallback

Is it possible to generate csv without templates, i mean i have some field in page like github repository name and date it got created?

Fallback

Sure, just transform the params submitted and make the headers and content of the CSV dynamic then.

Fallback

Can u share snippet?

Fallback

Also have u tried exporting in spreadsheet?


Fallback

Really Awesome!!!


Fallback

Very well done.
Can you guide me in the direction of making multiple CSV exports on the same model/controller. I can't seem to figure out how to do more than one query-export...

Fallback

This one's a little tougher, but the basic idea is this: 1. Export your CSVs to temporary files 2. Zip them up 3. Send the zip file over as the download.

You'll have to do this because there can only be one file downloaded per requests, so hence the need for creating a zip file.

Fallback

Thank you for the lightening fast reply.
But what I mean is not simultaneous downloads but just single requests from multiple queries.
I have a dashboard on an index page breaking down the data into palatable user friendly chunks and want to have a download csv button for each one. I'm sure I'm way over complicating it but I literally can't conceptualize how to move beyond the one exactly like in your example above.
Thanks!

Fallback

Ah, sorry I misunderstood. :)

So you have a dashboard with say like 4 tables of data and you want the user to be able to download each one independently? In that case, I would probably create a handful of extra routes and actions in that controller that just run those individual queries and export to CSV. That way you can keep them organized together nicely and still allow their separation.

Fallback

Oh man! I'm almost getting it...
That's really helpful BUT I'm still lost on the model piece...

Service.rb
def self.to_csv
attributes = %w{SomeColumn Date}
CSV.generate(headers: true) do |csv|
csv << attributes
all.each do |service|
csv << [service.name, service.date]
end
end
end

I don't get how this gets repeated for new queries...
Thanks Again!

Fallback

Since it's a class method, you can add .to_csv on any call. Service.all.to_csv or Service.where(name: "whatever").to_csv. Both will work and will return CSV's that are only of the appropriate results.

Fallback

WOW!
Can't thank you enough Chris!
It's working :)
You Rock!

Fallback
Fallback

Fallback

Any reason why I would prefer CSV over an SQL database? :)


Fallback

Very helpful, Chris!

One note on something I ran into... I found on a Rails 4.0 app with Ruby 2.0 that naming the method `to_csv` didn't work. The `to_csv` method from the Ruby CSV seemed to override my method and would just kick out a single row with each object in the set. Renaming the method to `export_csv` solved the problem for me.

Clearly something going on that is unusual in my app configuration, because as I understand it, this method should come "first".

Anyhow, working now!

Fallback

What a familiar face. 👋

Good catch, I hadn't run into that but I could see that happening somehow.


Fallback

Thank Chris, I found this useful.


Fallback

I am a little confused. Why can an array of User objects invoke a class method of User class?

Fallback

It's not an Array, it's an ActiveRecord::Relation object. They act like Arrays, but are very different.


Fallback

If the data I want not come from mysql records, how to do?


Fallback

The generation of CSV should not be done on the Model, it's part of how the data is displayed or represented, so it's part of the View. Dont you think so?

Fallback

For the sake of the example, the model is the easiest place to put it. To better organize the code I would probably move this code to its own class but since that's not the concept I want to focus on in this episode, I just placed the code in the model.


Fallback

Using the same code set, how would I go about toggling a boolean field, :exported, at the time of export to csv?


Fallback

I recently needed to implement something similar to this, but needed to be able to capture the scope passed to the model class and create the CSV based on that scope.

Instead of `all`, use `current_scope` to capture the scope (e.g. `User.where(active: true).limit(10).to_csv`)

https://gist.github.com/mgo...


Fallback
Mudedla Panduranga Rao -

How attributes will going to help when i need to export relations data as well? If some one any idea, please let me know


Fallback

Did you have the same `def self.to_csv` function on the model and the controller?


Fallback

transactions.rb

belongs_to :client
def self.to_csv
attributes = %w{id client_id from_date to_date records }

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

all.each do |transaction|
csv << transaction.attributes.values_at(*attributes)
end
end
end
Now I need to add client.name to be printed (since client is the parent )?
is there way to do it or any recommended gem to use?

Fallback

got it thanx chris


Fallback

Thanks for a great episode.
Wanted to give you an example of how to incorporate associated data. The model collection has a partner associated via collection.visit.partner. This csv was setup in the model of collections.
attributes1 is for collection
attributes 2 is for collection.visit.partner
Hope you can use it.

def self.to_csv
attributes1 = %w{quantity size content}
attributes2 = %w{name partner_number address_one postno city}
CSV.generate(headers: true) do |csv|
csv << attributes1 + attributes2
all.each do |collection|
csv << attributes1.map{ |attr| collection.send(attr)} + attributes2.map{ |attr| collection.visit.partner.send(attr)}
end
end
end

Fallback

you really helps me a lot! Thanks!


Fallback

HI. I am newbie with rails and trying to export records into a csv. I am able to do the simple download, thanks to this wonderful discussion. But my problem is that instead of all.each, I want to iterate over a subset of records. How can I achieve that? Any help would be appreciated. Thanks


Fallback

Hi, My .to_csv method looks like this:

def self.to_csv
attributes = %w{stamnummer naam datum vak studierichting }

CSV.generate(headers: true) do |csv|
csv << attributes
all.each do |absence|
csv << [absence.user.ugent_student_id, absence.user.fullname, absence.date.strftime("%d %b %Y"), absence.course.title, absence.study_program]
end
end

The problem I have with this is that when I open the .csv file in Excel, all attributes are placed in one cell, while I expected every attribute to be placed in it's own field.

Do you know ho to implement this?

thanks for your help,

Anthony

Fallback

I fixed it by using an xls.erb template file, as in the Railscast video (http://railscasts.com/episo... )


Fallback

This is a great episode, thanks! I am able to do all of this on the index action in the controller I am using to export a csv but when I try to do this on an action which I have called 'annual' I am getting a 404 error as it is trying to run the .to_csv on the #show controller... The action is

def annual
@year = params[:date][:year]
@surveys = Survey.where("year = ?", @year)

respond_to do |format|
format.html # index.html.erb
format.json { render json: @surveys }
format.csv { send_data @surveys.to_csv, filename: "survey-annual-#{Date.today}.csv"}
end
end

I'm at a loss...


Fallback

Hi Chris! Thanks for this Episode... I think would be great if all the screencast have the source code in github, because sometimes the video explanation is focused in some part of the code, and the other part you've previously wrote. I'll hope that in the most recent episodes the code will be up. For now I have and uninitialized constant User::Import error, and I don't know how to solve. I saw code without explanation in your user_controller.rband I think that my problem is in User::Import but I can't solve the problem :(

Fallback

Never mind! I solved this issue in the next episode :)


Fallback

Hi Chris (and community),

I like the solution in this episode ... but, when downloading hundreds of thousands of records in the same CSV we hit a timeout (yeah, our users want to be able to click on a button and download a gigantic CSV ...).

What would be your approach regarding this problem? I was thinking maybe creating a new model (RecordsList), and using ActiveStorage to attach the generated CSV file (containing a list of all records) to that model. This would be done in the background.

What do you think?

Fallback

Yep, that's pretty normal for very large files. Generate it, store it somewhere that you can link to, and then either send them an email or display it on the website with a link to download. That's pretty standard to what you'd see in other apps.


Login or create an account to join the conversation.