On 11/8/06, Jeremy Hinegardner <jeremy / hinegardner.org> wrote:
> On Thu, Nov 09, 2006 at 03:52:07AM +0900, Michael Guterl wrote:
> > Anyone have any suggestions on pushing a flat CSV file into a
> > relational db?  I am currently thinking of a solution working with
> > FasterCSV and ActiveRecord.  I picture something like the following,
> > although I'm sure someone can make a better suggestion...
>
> Depending on the quantity of data you may want to pick something other
> than ruby to do this.  Most databases (at least I know sqlite,
> postgresql, mysql and db2 do) have a generic IMPORT or LOAD statement to
> import from a delimited file; the most common of which being a CSV.
>
In this particular case I do not have a large quantity of data, though
I do appreciate the suggestions.

> Many times I've wanted use ruby to parse and load CSV files into
> databases, but for me there wass a data volume threshold.   Many times
> I've had to load hundreds of thousands to hundreds of millions of rows
> into a db, and these days it is (for me) much easier to just have the
> ruby script invoke the appropriate IMPORT or LOAD or whatever SQL
> statement from the DB interface and pass it the CSV file to load.
>
Yeah, my particular liking of AR is that I can easily move pieces of
the data to the correct tables, setup all the relationships,
validations, and manipulations in Ruby.

> Sometimes I've had to have ruby reprocess the csv file to get into the
> quoted format that the db import command format, but in the overall
> time, it was actually faster to process the CSV file and have the db
> load it natively than to have ruby process the CSV file and do the
> inserts via ActiveRecord or the DBI interface.
>
I do like this suggestion, do all the CSV manipulations, then pass off
to the appropriate DB command.  I will have to investigate these
options further.

> Just my opinion though :-).
>
> enjoy,
>
> -jeremy
>
> --
> ========================================================================
>  Jeremy Hinegardner                              jeremy / hinegardner.org
>
>
>
Thanks,
Michael Guterl