DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Read From Tables

01.26.2011
| 3724 views |
  • submit to reddit
        from table to CSV and from csv to excel
# from table to CSV
require 'odbc'
require 'dbi'
filename = "./file.csv"
writeFile = File.open(filename,"w")
dbh = DBI.connect('DBI:ODBC:data_source_name', 'username', 'password')
sth = dbh.prepare("SELECT * FROM table WITH UR")
sth.execute
writeFile.puts sth.column_names.join(" ; ") #puts names on the top of each row
while row=sth.fetch do
	writeFile.puts row.join(" ; ") #puts lines from table
end
sth.finish
dbh.disconnect
writeFile.close

# from csv to excel
require 'win32ole'
xl = WIN32OLE.new('excel.application')
xl.workbooks.open(File.expand_path(filename))
xl.visible = true

from table to ASCII
require 'odbc'
require 'dbi'
STDOUT = File.open("./table.txt","w")
dbh = DBI.connect('DBI:ODBC:data_source_name', 'username', 'password')
sth = dbh.prepare("SELECT * FROM table WITH UR")
   rows = sth.fetch_all
   col_names = sth.column_names
   sth.finish
   DBI::Utils::TableFormatter.ascii(col_names, rows, col_names_orient=:left, rows_orient=:left, indent=2, cellspace=1, pagebreak_after=nil, output=STDOUT)
STDOUT.close
dbh.disconnect

from table to XML
require 'odbc'
require 'dbi'
STDOUT = File.open("./table.xml", "w")
dbh = DBI.connect('DBI:ODBC:data_source_name', 'username', 'password')
DBI::Utils::XMLFormatter.table(dbh.select_all("SELECT * FROM table WITH UR"), "#{table}", 'ROW')
STDOUT.close
dbh.disconnect