Export Records to CSV with Ruby on Rails

so 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 the same records so we're going to use the same CSV library as before and you're going to want to make sure that you have a require CSV inside your application Darby so put that right before rails /aulsz require statement and restart your rails application that will make sure that you have the CSV library available in all cases so once you have that we're simply going to take our users route and we're gonna accept the format of CSV so we're gonna create a URL here called slash users dot CSV and this dot CSV file extension in the year the URL is automatically parsed by rails as a format so if we go to the users controller well first let's take a look at the routes I have resources users and that's gonna be what sets up slash users of course and when we come to the controller we have the index action and that will be what slash the users points you to and in here we simply need to respond to a new format so by default this will look for HTML or JavaScript templates or XML or JSON inside your views folder but because we don't actually need a view for this we're going to set up a respond to block and we're gonna say let's continue responding to HTML but let's also respond to the CSV format and if we pass a block in here it allows us to render a file and all you need to do to render a file is send data and this is simply going to send data back to the browser so this sets up the response in such a way that it will download the CSV file and since we've already queried for users we have the @ user variable and we just need to convert this to CSV format now what we're gonna do is we're going to make a method called to CSV on the users class so that model is gonna have a class method that handles creating a CSV so if we save this we can open up the users model and scroll down here and create a to self dot to CSV method now we can have some options here or whatever to determine if you want to add headers or not so I'm always gonna go with headers I think headers are an important thing to use if especially you want to keep this consistent between your exports and your uploads so if your imports require headers make sure your exports also include the headers if they don't then don't worry about it so keep those consistent so that you have the same format both directions now to actually create the CSV we use the CSV dot generate method and this is just going to be passed in a block and it will pass in the CSV as an argument so we'll be building this in memory and we'll return it back to the controller which sends that to the browser so one thing we want to do is this generate method takes the same options as the CSV import does so if you are importing a CSV you can set headers as true and we're gonna want to do the same thing with CSV is generate so here we can say CSV and then we want to create a row for those headers so let's actually make a variable called attributes and then we'll set this equal to one of the special array creation mechanisms in Ruby if you use percent W and then any sort of punctuation here like curly braces or you could use parentheses here square brackets this allows you to type words separated by spaces and it will generate an array for you so we can do that in the rails console real quick so if we do % W square brackets ABC will get a string with an array of strings a B and C and if we do % W parenthesis ABCDEF then we'll get the same thing so it's just a shortcut for creating arrays of strings which I find really useful in a situation like this so it makes this easy for us to say ID we want the email we want to export the first name and let's also grab the last name so we'll have these attributes here and we can pass them in as the first row so if you do CSV and shovel on an array then this will tell the CSV to generate a new line with those columns so it knows how to take an array like this and then convert that to one long string separated by commas so what that's really just gonna simply do is if we want to take an array like that and we say we have a B and C as an array so imagine our attributes variable is that what the CSV shovel operator does here is simply take those attributes and then joins by your column separator which is normally a comma and that's it that's all it does to generate a row and then probably it's gonna append a newline character at the end and then just build out your CSV format like that so CSVs are really simple but this library makes for a lot of shortcuts for us so now that we've got that we can use The Awl scope and this will allow us to say for each of these users then let's shovel on that record and for the user we want to grab their attributes and then we want to get the values at attributes and we use the splat operator here to make each of these items in the array a single argument two values at so what this allows us to do is say let's grab the last user if we say last user dot attributes this gives us a hash with string keys for each of those and then when we do values at if we said 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 as the next parameter it would give us 25 and then example at user com now 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 users controller so if you use a different scope here say maybe public users or not deleted users this users instance variable will get set to that and then when you pass it in to the controller or from the controller to the to CSV method this will be like appending the scope of all on to the previous scopes so this won't mess with your scopes at all it will continue keeping the same scope and in previous versions of rails that would have been scoped but they switch that to all and I think deprecated that old scoped method because this reads a little bit better so that's as far as we need to go for handling CSVs and now when we actually execute this slash users dot 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 so 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 names so everything's working correctly and we can make adjustments as necessary so 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 that if you are using attributes like maybe you have a name method here and and you don't want to use first and last name if you want to do that then you're gonna have to set this up a little bit differently you 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 name here instead and rather than doing attributes we're going to replace this with a loop around the attributes and we're going to map that and create a new array and this time we're going to take the attribute name and then we're gonna call user dot send and pass in that attribute name so 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 if it's an attribute and that so 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 the CSV as a new row so that is a way of using things like these sort of fake attributes that we've created these helper methods around functionality like that so it allows us to do a little bit more complex things and then if we grab users at 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 into there so that is a very simple introduction to importing and exporting from CSVs I have a couple Pro episodes to talk about uploading CSVs through forums if you're interested this is very similar to pretty much exactly the same as what ryan bates covered in rails cast long time ago and the reason that 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 if you don't use a gem or anything like that not much is gonna change the API is pretty stable and all you're gonna need to do is add a couple lines to your controller and one method to your model to set that up so 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 file name so let's grab data streaming send data that we want to get and so you can do disposition is in line or attachment this is really cool if you're doing PDFs because you can force the download using attachment or yeah because browsers now render PDFs inside of them you can do a disposition is in line to render it in line not sure if that works for CSVs but let's try it disposition in line save that and see what happens so CSVs are going to get downloaded because they don't actually have the ability to really render a CSV it's just plain text but that's that's kind of what you might expect so you can also pass in like the option a file name and imagine that you want the current date attached in there so you might have users - date dot today as your string now if you go to users that CSV the file you get downloaded will be that filename so you'll also want to make sure you get the dot CSV in there to control that but this is going to be useful so that you can create your own file name format so that users dot CSV the file name in the URL that you accessed isn't converted directly so 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 the format that you would prefer so 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 yeah that's the basics of exporting CSV s so if you have any questions please post a comment and I'll see you next episode

Channel: GoRails

Views: 26,554

Keywords: Ruby On Rails (Software), ruby, Comma-Separated Values (File Format)

Id: H3pjCJSLCgc

Length: 14min 25sec (865 seconds)

Published: Mon May 18 2015