CODEWITHSUNDEEP
mysqlrubywatirexport-to-excelexport-to-csv
mike
mike

Reputation: 77

export mysql results to file using Ruby

I would like to query my DB and take the results and export them into either a .txt, .cvs or .xls

The mysql database is not hosted on my local machine

here is my code to access the DB and return the results in on the screen:

 #!/usr/bin/ruby
 require 'mysql'
 require 'watir'
 require "win32ole"

 excel = WIN32OLE::new('excel.Application')
 workbook = excel.Workbooks.Add
 worksheet = workbook.Worksheets(1)

 mysql = Mysql.init()
 mysql.connect(host='hostname', user='username', passwd='pwd', db='DBname')

 results = mysql.query("Select * FROM table") 
 results.each{|row|; puts row;}
 mysql.close()

 worksheet.SaveAs("C:\\Scripts\\DB_Test\\Test.xlsx") 
 workbook.Close

Upvotes: 4

Views: 4650

Answers (2)

Steven Barragán
Steven Barragán

Reputation: 1244

This gem might help you a lot. You can export any complex query with a nice and flexible DSL.

https://github.com/stevenbarragan/julia_builder

Upvotes: 2

froderik
froderik

Reputation: 4808

csv is easy. I am not sure what you get from mysql.query but make sure that each row is an array with what you want to put in a csv line and then do something like this:

require 'csv'

CSV.generate do |csv|
  csv << ["headers","describing","the data"]
  mysql.query("SELECT * FROM table").each { |row| csv << row }
end

csv support is in the standard library so no need for any gems. Note that the standard library changed between 1.8 and 1.9 - the example should work with 1.9.

Upvotes: 6

Related Questions