# Extending ActiveRecord for csv export... a better way?

**URL:** <https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842>\
**Category:** Ruby\
**Created:** [October 31, 2007, 3:30pm UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842 "2007-10-31T15:30:15Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [October 31, 2007, 3:30pm UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842/1 "2007-10-31T15:30:15Z")

</div>

I accidentally posted this to the RoR forum; my apologies to anyone who  
receives this twice.

## As a new Ruby user (JRuby, actually), I’ve picked up this language with a very specific purpose: to export data from a JDBC database into flat files. I’ve figured out how to get the data out of the DB and into a file (using ActiveRecord-JDBC), but it requires a bit too much configuration. My first stab implementation requires me to define an activerecord class for each table I want to export, and to define a method all\_columns in each class that lays out the columns in the correct order. I’ve also setup a module (CSVable) that extends ActiveRecord so I can call the export function directly on each class. Here is the code:

# Module definition

module CSVable  
extend ActiveRecord  
class Base \< ActiveRecord::Base  
def self.export\_table\_to\_csv(delimiter = “,”, options = nil)  
File.open("#{self.table\_name}.dat",  
File::WRONLY|File::TRUNC|File::CREAT) do |file|  
self.find(:all, options).each do |trow|  
file.puts trow.all\_columns.join(delimiter)  
end  
end  
end  
end  
end

# Class definitions

class USR\_USER\_ACCOUNT \< CSVable::Base  
set\_table\_name “USR.USER\_ACCOUNT”  
def all\_columns  
[ self.UID, self.USER\_NAME, self.USER\_INIT, self.ACTIVE\_FLAG,  
self.LAST\_MODFD\_TS ]  
end  
end

class GRP\_GRP \< CSVable::Base  
set\_table\_name “GRP.GRP”  
def all\_columns  
[ self.PARTY\_ROLE\_ASSIGN\_PID, self.GRP\_NBR\_ID,  
self.LAST\_MODIFIED\_TIMESTAMP ]  
end  
end  
…

# Usage for the module

## USR\_USER\_ACCOUNT.export\_table\_to\_csv("|")

The major problem with this approach is that I have to setup all the  
column info for each of the 36 tables. Is there an easier way to get an  
array of the columns while still retaining the original order?

I’ve tried using the attributes method, but since it returns a hash, the  
columns are not in order. My dabbles with the columns method have not  
been successful either. Any ideas?

I also have a feeling there is a much better way to implement the export  
function…

Any help or suggestions are most appreciated!

-Adam

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [November 1, 2007, 2:54am UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842/2 "2007-11-01T02:54:55Z")

</div>

On Oct 31, 9:30 am, Adam B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> that lays out the columns in the correct order. I’ve also setup a  
> self.find(:all, options).each do |trow|  
> def all\_columns  
> end
> 
> –  
> Posted viahttp://www.ruby-forum.com/.

If you’re always doing (essentially) “select \*”, won’t Ruby’s dbi  
work? I don’t know if it’s guaranteed, but the columns always seem to  
be returned in the order they are defined in the database.

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [November 1, 2007, 3:22am UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842/3 "2007-11-01T03:22:45Z")

</div>

removed\_email\_address@domain.invalid wrote:

> If you’re always doing (essentially) “select \*”, won’t Ruby’s dbi  
> work? I don’t know if it’s guaranteed, but the columns always seem to  
> be returned in the order they are defined in the database.

Hmm… unfortunately I’m using JRuby and ActiveRecord-JDBC because my  
database is only accessible via JDBC (I’m sure there are other ways to  
access a Caché database, but not many), so I can’t use DBI, unless  
someone has developed access to Caché with DBI!

I’ll look into it anyway!

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [November 1, 2007, 3:34am UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842/4 "2007-11-01T03:34:39Z")

</div>

> function directly on each class. Here is the code:  
> require ‘fastercsv’

def to\_csv(records, filename=nil, columns=nil)  
filename ||= “#{records.first.class.table\_name}.dat”  
columns ||= records.first.class.columns.map {|x| x.name }  
FasterCSV.open(filename, ‘w’) do |csv|  
records.each do |record|  
csv \<\< columns.map {|column| record.attributes[column] }  
end  
end  
end

class UsrUserAccount \< ActiveRecord::Base  
set\_table\_name “USR.USER\_ACCOUNT”  
end

to\_csv(UsrUserAccount.find(:all))

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [November 1, 2007, 5:31am UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842/5 "2007-11-01T05:31:00Z")

</div>

On Oct 31, 9:22 pm, Adam B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> ## I’ll look into it anyway!
> 
> Posted viahttp://www.ruby-forum.com/.

I see. I don’t know much about either JDBC or Caché, but this might  
help:

> **[Ruby-DBI and JDBC](http://ola-bini.blogspot.com/2007/09/ruby-dbi-and-jdbc.html)**
>
> Last week I decided it was time to get Ruby-DBI working with JDBC. I resolved to get on it since it would be highly useful and probably not ...

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [November 1, 2007, 3:06pm UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842/6 "2007-11-01T15:06:45Z")

</div>

Daniel S. wrote:

> require ‘fastercsv’
> 
> def to\_csv(records, filename=nil, columns=nil)  
> filename ||= “#{records.first.class.table\_name}.dat”  
> columns ||= records.first.class.columns.map {|x| x.name }  
> FasterCSV.open(filename, ‘w’) do |csv|  
> records.each do |record|  
> csv \<\< columns.map {|column| record.attributes[column] }  
> end  
> end  
> end

Daniel, thank you for your reply and effort. I tried this function out  
with no avail. For some reason the output file is always empty (I have  
confirmed the table is not empty). The file has the right number of  
lines, but no data. I have come to the same end with this code:

#within the class inheriting ActiveRecord::Base  
def self.export\_table\_to\_csv(delimiter = “,”, options = nil)  
file = File.open("#{self.table\_name}.dat",  
File::WRONLY|File::TRUNC|File::CREAT)  
rec = self.find(:all, options)  
for row in rec do  
line = columns.inject([]) { |arr, col| arr \<\< row[col]; arr}  
file.puts(line.join("|"))  
end  
end

I am beginning to suspect that there is a problem with the columns logic  
in AR as related to the AR-JDBC extension (or perhaps specifically with  
the driver I’m using), but that is purely speculation on my part. My  
Rubyism is not advanced enough to know how to debug that, but I’ll look  
at the code for AR-JDBC and see if anything jumps out at me.

The link from removed\_email\_address@domain.invalid looks promising for setting up JRuby DBI  
with JDBC. I’m going to try it out if I am unable to resolve this using  
AR-JDBC soon!

Thank you both for all your help!

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [November 1, 2007, 3:53pm UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842/7 "2007-11-01T15:53:50Z")

</div>

removed\_email\_address@domain.invalid wrote:

> I see. I don’t know much about either JDBC or Cach�, but this might  
> help:
> 
> [Ola Bini: Programming Language Synchronicity: Ruby-DBI and JDBC](http://ola-bini.blogspot.com/2007/09/ruby-dbi-and-jdbc.html)

I think the DBI solution suggested is going to work. Once I have some  
clean code to play with, I’ll post my solution. Thanks again.

---

<div class="post-metadata">

**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\
**Post date:** [November 1, 2007, 7:17pm UTC](https://www.ruby-forum.com/t/extending-activerecord-for-csv-export-a-better-way/118842/8 "2007-11-01T19:17:34Z")

</div>

> removed\_email\_address@domain.invalid wrote:  
> [Ola Bini: Programming Language Synchronicity: Ruby-DBI and JDBC](http://ola-bini.blogspot.com/2007/09/ruby-dbi-and-jdbc.html)

After following the instructions found at the URL above, I was able to  
finally do what I needed! Thanks for all your help! My full  
implementation follows:

#####################################################################

# Export\_ODS.rb

# jdbc drivers must be located in the CLASSPATH or in $JRUBY\_HOME/lib

#####################################################################  
require ‘rubygems’  
require ‘dbi’

# Load DB configuration - yml file in same directory

def connect()  
cfg = YAML::load(File.open(‘dbinfo.yml’))  
DBI.connect(‘DBI:’ + cfg[“url”], cfg[“username”], cfg[“password”],  
‘driver’=\>cfg[“driver”])  
end

# Connect to the database

dbh = connect

# Define the delimiter used in the CSV output files

delim = ‘|’

export\_tables = File.open(‘schema\_table\_list.dat’, ‘r’).readlines.each  
do |line|

# build an array from the schema/table file in the form schema.table

line.strip!.gsub!(delim, ‘.’)  
end

# for each table in the list…

export\_tables.each do |table|

# open a file named after the table and…

File.open(“#{table}.dat”, File::WRONLY|File::TRUNC|File::CREAT) do  
|file|  
# write out each row from the table to the aptly named file.  
dbh.select\_all(“select \* from #{table}”).each do |row|  
file.puts row.join(delim)  
end  
end  
end

# End Export.rb

#####################################################################

# dbinfo.yml

#####################################################################  
driver: com.intersys.jdbc.CacheDriver  
url: jdbc:Cache://cache-server:56773/cachedb  
username: \_system  
password: mypass

#####################################################################

# schema\_table\_list.dat

# Currently generated from Oracle, may write a ruby script to pull it

# out of the Cache side.

#####################################################################  
ENROLL|ENROLLMENT  
GRP|GRP  
MEMBER|MEMBER  
MEMBER|MEMBER\_DX  
ORGROLE|EMPLOYERGROUP  
…