# Flat CSV to Relational DB

**URL:** <https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278>\
**Category:** Ruby\
**Created:** [November 8, 2006, 7:54pm UTC](https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278 "2006-11-08T19:54:11Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [November 8, 2006, 7:54pm UTC](https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278/1 "2006-11-08T19:54:11Z")

</div>

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?

TIA,  
Michael G.

---

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [November 8, 2006, 11:06pm UTC](https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278/2 "2006-11-08T23:06:43Z")

</div>

On Nov 8, 2006, at 12:52 PM, Michael G. wrote:

> :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 G. 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.

James Edward G. II

---

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [November 9, 2006, 6:59pm UTC](https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278/3 "2006-11-09T18:59:50Z")

</div>

On 11/8/06, James Edward G. II [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > :category =\> Category.find\_or\_create\_by\_name(row["Product  
> > Any suggestions?  
> > namespace :my\_app\_name do  
> > end
> 
> 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 G. II

Michael G.

---

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [November 9, 2006, 7:09pm UTC](https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278/4 "2006-11-09T19:09:53Z")

</div>

On 11/8/06, Jeremy H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 

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.

> 

Thanks,  
Michael G.

---

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [November 9, 2006, 1:05am UTC](https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278/5 "2006-11-09T01:05:06Z")

</div>

On Thu, Nov 09, 2006 at 03:52:07AM +0900, Michael G. 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.

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.

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.

Just my opinion though :-).

enjoy,

-jeremy

---

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [November 10, 2006, 6:36pm UTC](https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278/6 "2006-11-10T18:36:59Z")

</div>

On Fri, Nov 10, 2006 at 03:08:11AM +0900, Michael G. wrote:

> On 11/8/06, Jeremy H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

[…]

> > 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.

Glad to help. Let us know how it turns out.

enjo,

-jeremy

---

<div class="post-metadata">

**Author:** ![mgreenly](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85f322/32.png) [@mgreenly](https://www.ruby-forum.com/u/mgreenly)\
**Post date:** [May 16, 2009, 2:25am UTC](https://www.ruby-forum.com/t/flat-csv-to-relational-db/75278/7 "2009-05-16T02:25:15Z")

</div>

Jeremy H. wrote:

> On Fri, Nov 10, 2006 at 03:08:11AM +0900, Michael G. wrote:
> 
> > On 11/8/06, Jeremy H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> […]
> 
> > > 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.
> 
> Glad to help. Let us know how it turns out.
> 
> enjo,
> 
> -jeremy

Hi Jeremy:  
Can you please elaborate on how you call natively load data infile from  
a ruby file ? I really need good performance and I cannot get it as you  
said by using active records or DBI.  
The piece of code that is slow is this:  
`
FCSV.foreach(ARGV[0], :headers => true) do |row|
table.insert(:call_date => row[0],
:caller_Id => row[1],
:phone_no=> row[2],
:destination => row[3],
:duration => row[4],
:call_cost => row[5])`

`
`