3

Ruby on Rails 3

I have a table that is not shown on my application. I want to export the table to excel from the console.

Is there way to export a database table to an excel file from the console?

Thank you

Here is what I have so far:

require 'csv'

file = "#{Rails.root}/public/survey.csv"

userrefs = Survey.all

CSV.open( file, 'w' ) do |writer|
 userrefs.each do |ur|
   writer << ur.attributes.values_at(*column_names)
 end
end

When I enter require 'csv' it returns false. How do you make it true? Also, the *column_names is undefined.

DDDD
  • 3,790
  • 5
  • 33
  • 55
  • this may help http://stackoverflow.com/questions/2461503/rails-redirecting-console-output-to-a-file – Sam Dec 13 '13 at 16:40
  • @SamD that question does not help with the file format. I can easily get the table in a txt format. I would just copy and paste it if anything. – DDDD Dec 13 '13 at 16:44
  • 1
    instead of txt you can run format.xls on a partial with the table only. like here http://railscasts.com/episodes/362-exporting-csv-and-excel – Sam Dec 13 '13 at 16:47
  • This is in the application though. My table is not viewed anywhere in my application. I need to do it from console – DDDD Dec 13 '13 at 16:48
  • you can still run format.xls in console after instantiating the object that you want to export to excel – Sam Dec 13 '13 at 16:50
  • @Sam D Your way of running format.xls in console seems to be the most user friendly and long time solution. Please post as an answer. – DDDD Dec 16 '13 at 13:20
  • I'm glad it worked for you. I posted it as an answer – Sam Dec 16 '13 at 15:59

3 Answers3

2

As mentioned in the comments an easy approach is using format.xls function to render an excel file from the console. Ryan Bates video covers Excel outputs extensively.

Sam
  • 2,761
  • 3
  • 19
  • 30
1

You can connect to the database table using the existing driver or, if you prefer a more high-level API, you can create an ActiveRecord model or use the Sequel gem.

Once connected, simply use the Ruby CSV library (it's in the standard library, so no additional library is required) to dump the content into a CSV file.

CSV files can be easily read from Excel.

PS. Just to use the appropriate words, that is not a Rails table. It's a database table.

Simone Carletti
  • 173,507
  • 49
  • 363
  • 364
  • Not sure what you mean by connect to the database. Also, how do you use the library to dump the file? I see in the link it has a do loop. Is that run at the console? – DDDD Dec 13 '13 at 20:08
  • Yes, you can perform everything from a console. – Simone Carletti Dec 13 '13 at 20:35
  • The site uses all those titles that is very confusing. Which is all at once? Also, where in the cmd is the table specified? Could you place an example? – DDDD Dec 13 '13 at 22:02
  • Instead of having me writing the code for you, according to the spirit of StackOverflow, try to study and write a solution. Then come back and post what you did so that we can comment it and help you. – Simone Carletti Dec 13 '13 at 22:45
1

Another interesting approach could be using the activeadmin gem, it's easy to install and allow you to export your tables to csv.

http://www.activeadmin.info/

Gonzalo S
  • 896
  • 11
  • 19