All threads / Exporting Records To CSV Discussion

Ask A Question

Notifications

You’re not receiving notifications from this thread.

Exporting Records To CSV Discussion

Dicussion for Exporting Records To CSV
pel_daniel ·

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 ??

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.

pel_daniel ·

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

Arup Rakshit ·

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 ?

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.

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...

Reply

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

Reply

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?

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.

Thanks Chris. Great videos and thanks for the support!

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!

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]

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

Alex W Kale ·

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

Reply

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!

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"]

Reply

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?

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

Also have u tried exporting in spreadsheet?

Reply
Jai Kumar Rajput ·

Really Awesome!!!

Reply

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...

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.

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!

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.

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!

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.

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

Reply

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

Reply

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!

What a familiar face. 👋

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

Reply

Thank Chris, I found this useful.

Reply
Erick Zhou ·

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

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

Reply
Yarving Liu ·

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

Reply

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?

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.

Reply

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

Reply
Mike Goggin ·

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...

Reply
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

Reply
Alejandra Ledesma ·

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

Reply

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?

Reply

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

lupeng liu ·

you really helps me a lot! Thanks!

Reply

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

Reply
Anthony Candaele ·

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

Anthony Candaele ·

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

Reply

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...

Reply

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 :(

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

Reply

Thanks so much for this awesome post and episode. I found it really useful for setting up a CSV export for my Users model. I noticed a couple commenters were having a similiar issue when attempting to do an export with has_many relationships. I couldn't seem to get the suggestion that Chris gave Nathan to work for me, so after a couple hours of mucking around, I sorted it out and wrote something up quickly in case it helps anyone else out. So basically, I'm just showing how you can export Users and their potentially many Coupons in a single shot.

http://blog.blackninjadojo.com/ruby/rails/2019/02/06/exporting-rails-models-with-has-many-relationships-to-csv.html

Thanks again,
Shereen

Reply

i have a problem i cant solve.

i have a model and controller for cars, and can export the data i need to a csv, however i have images saved and stored by paperclip. cars has a one to many relationship with the images but i cannt for the life of me figure out how to get the urls for the images from the asset pipeline.

currently i have my csv code in the model for cars and i can get the db data from the images but cant get the url's.

i have no issues creating the csv data from the view or cars controller (Philosophically) other than i cant get it to work. lol

any help is greatly appreciated!

THANKS!
d

Reply

This just helped me 4 years later. Thanks, Chris!

Reply

Hey Chris. Love the tutorial. Used it with ease on Rails 5.2. Now with webpack and Rails 6.0 I seem to be getting some sort of weird caching issue in production. I made some changes to the attributes, and they don't update on server. I even added a record and it still didn't show up!

Any ideas as to what might be going on and how to fix it? I posted a Stack question with some more code here.
https://stackoverflow.com/questions/61150453/compile-in-rails-6-webpacker-doesnt-make-changes-to-send-data-with-methods-defi?noredirect=1#comment108188144_61150453

Your Uppy tutorial was amazing!

Reply
Join the discussion

Want to stay up-to-date with Ruby on Rails?

Join 38,311+ developers who get early access to new tutorials, screencasts, articles, and more.

    We care about the protection of your data. Read our Privacy Policy.

    logo Created with Sketch.

    Ruby on Rails tutorials, guides, and screencasts for web developers learning Ruby, Rails, Javascript, Turbolinks, Stimulus.js, Vue.js, and more. Icons by Icons8

    © 2020 GoRails, LLC. All rights reserved.