From: Michael Guterl <mguterl@...>
Date: 2006-11-10T02:59:10+09:00
Subject: Re: Flat CSV to Relational DB

On 11/8/06, James Edward Gray II <james@grayproductions.net> wrote:
> On Nov 8, 2006, at 12:52 PM, 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...
> >
> > FasterCSV.read("data.csv", :headers => true).each do |row|
> >  Product.create(
> >    :name => row["Product Name"],
> >    :category => Category.find_or_create_by_name(row["Product
> > Category"]),
> >    :shipping_category =>
> > ShippingCategory.find_or_create_by_weight(row["Weight"]),
> >    :color => row["Product Color"],
> >    :description => row["Product Description"]
> >  )
> > end
> >
> > My Product.create method call is HUGE, it keeps going and going...
> >
> > Any suggestions?
>
> I have the following example in FasterCSV's example directory:
>
> #!/usr/local/bin/ruby -w
>
> # csv_rails_import.task
> #
> #  Created by James Edward Gray II on 2006-11-05.
> #  Copyright 2006 Gray Productions. All rights reserved.
>
> namespace :my_app_name do
>    desc "Injects purchase.csv into the database."
>    task :load_purchase => [:environment] do
>      require "#{RAILS_ROOT}/vendor/faster_csv/lib/faster_csv"
>
>      purchase = Purchase.create!
>
>      FCSV.foreach( "#{RAILS_ROOT}/db/questions.csv",
>                    :headers           => true,
>                    :header_converters => :symbol ) do |line|
>        purchase.line_items.create!(line.to_hash)
>      end
>    end
> end
>
> You're needs are slightly more complex, but this makes a decent
> starting point:
>
> 1.  Use :header_converters where you can
> 2.  Hash the line
> 3.  Edit the Hash as needed to create associated objects
> 4.  Hand-off to create
>
> Hope that helps.
>
Thanks, I was unaware of :header_converters, those look like they
could be very useful.  I also was unaware of the ease at which I could
hash a row, very cool.  Thanks for this great library!

> James Edward Gray II
>
>
>

Michael Guterl