Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Read From Tables

DZone's Guide to

Read From Tables

·
Free Resource
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
Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}