CODEWITHSUNDEEP
rubyruby-on-rails-3excelexport-to-excel
tomekfranek
tomekfranek

Reputation: 7109

How to export table from heroku production database locally to excel from console using Ruby?

I know how to export table in Rails to formated Excel file: http://railscasts.com/episodes/362-exporting-csv-and-excel

But how to do that from console.

Upvotes: 11

Views: 9690

Answers (3)

stevec
stevec

Reputation: 52967

Here's what I use (it's based off @algometrix's answer), it will escape quotes contained in strings:

  1. Open postgres console in heroku with heroku pg:psql

  2. This will ensure any quotes contained in strings are escaped accordingly

\copy (SELECT * FROM users) TO dump.csv WITH (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *)

Upvotes: 1

algometrix
algometrix

Reputation: 3992

Connect to your Heroku database by using 

heroku pg:psql

Then run the sql command to get the csv file e.g. 

\copy (SELECT * FROM users) TO dump.csv CSV DELIMITER ','

Use \q to exit.

After executing the commands dump.csv would have been created in your local environment

Upvotes: 41

Neil Middleton
Neil Middleton

Reputation: 22240

There's a couple of option here.

Firstly, you could wrap up some ruby code into a rake task that creates the Excel spreadsheet and then spits it onto S3 for you to pick up later. This would be run via the CLI:

heroku run rake export_data

OR

You could spin up a Postgres console heroku pg:psql and export your data to a CSV locally directly with a query such as those discussed here: http://ru05team.blogspot.co.uk/2011/03/export-postgresql-into-csv.html

Note: pg:psql gives you a full interactive PSQL session with your production database as if it were local. Be careful when messing direct with a production database.

Upvotes: 3

Related Questions