Rails - Aug 2006 - How do I go about importing all of this data into a database?

Hello,

Forgive me, as I''m still a beginner.  I have a pretty big amount of  
data related to food (from USDA.gov) that I would like to add to a  
database.  A sample of the content looks like this:

~05088~^~CHICKEN,BROILERS OR FRYERS,NECK,MEAT  
ONLY,RAW~^71.17^154^17.55^8.78^0.69^0.00^0.0^^27^2.06^17^113^175^81^2.68 
^0.109^0.035^13.5^0.0^0.054^0.228^4.119^1.090^0.290^8^0^8^8^0.32^146^44^ 
44^^^^^^^^2.250^2.730^2.180^83^6.00^~1 unit~^20.00^~1 neck, bone and  
skin removed~^75

~01124~^~EGG,WHITE,RAW,FRSH~^87.57^52^10.90^0.17^0.63^0.73^0.0^0.71^7^0. 
08^11^15^163^166^0.03^0.023^0.011^20.0^0.0^0.004^0.439^0.105^0.190^0.005 
^4^0^4^4^0.09^0^0^0^0.00^0.0^0^0^0^0^0^0.000^0.000^0.000^0^243.00^~1  
cup~^33.40^~1 large~^0

I am wondering if someone count point me in the right direction as  
far as how to sort this data out and add it to a database, assuming I  
know which columns I need (such as name, nutrition info (calories,  
fat, carbs, protein), portion size, etc.  Do I use migrations or  
something?  How do I sort out all of the information from each line?   
I am still new to this so I am a little confused.

Any help would be appreicated,
Thanks.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

take a look at kettle:  http://www.kettle.be
this is a great (open source) data manipulation tool!


Robert Gabaree wrote:
> Hello,
> 
> Forgive me, as I''m still a beginner.  I have a pretty big amount
of
> data related to food (from USDA.gov) that I would like to add to a
> database.  A sample of the content looks like this:
> 
> ~05088~^~CHICKEN,BROILERS OR FRYERS,NECK,MEAT
> ONLY,RAW~^71.17^154^17.55^8.78^0.69^0.00^0.0^^27^2.06^17^113^175^81^2.68
> ^0.109^0.035^13.5^0.0^0.054^0.228^4.119^1.090^0.290^8^0^8^8^0.32^146^44^
> 44^^^^^^^^2.250^2.730^2.180^83^6.00^~1 unit~^20.00^~1 neck, bone and
> skin removed~^75
> 
> ~01124~^~EGG,WHITE,RAW,FRSH~^87.57^52^10.90^0.17^0.63^0.73^0.0^0.71^7^0.
> 08^11^15^163^166^0.03^0.023^0.011^20.0^0.0^0.004^0.439^0.105^0.190^0.005
> ^4^0^4^4^0.09^0^0^0^0.00^0.0^0^0^0^0^0^0.000^0.000^0.000^0^243.00^~1
> cup~^33.40^~1 large~^0
> 
> I am wondering if someone count point me in the right direction as
> far as how to sort this data out and add it to a database, assuming I
> know which columns I need (such as name, nutrition info (calories,
> fat, carbs, protein), portion size, etc.  Do I use migrations or
> something?  How do I sort out all of the information from each line?
> I am still new to this so I am a little confused.
> 
> Any help would be appreicated,
> Thanks.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

First you need to know the format of the data before you can do
anything.  Then design a schema for the data if you haven''t already.
Probably the easiest way to do it is to just parse the data using
something like String.split combined with whatever cleanup you need to
do on it before it goes into the database.  Then insert it one row at
a time using a ruby script with something like ruby-dbi.   If it''s a
very large amount of data you could also write out a file in a format
that your database can accept as an import file.

Chris

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On 9/1/06, Robert Gabaree <lists-n9Q/XG8udXg@public.gmane.org>
wrote:
>
> Hello,
>
> Forgive me, as I''m still a beginner.  I have a pretty big amount
of
> data related to food (from USDA.gov) that I would like to add to a
> database.  A sample of the content looks like this:
>
> ~05088~^~CHICKEN,BROILERS OR FRYERS,NECK,MEAT
> ONLY,RAW~^71.17^154^17.55^8.78^0.69^0.00^0.0^^27^2.06^17^113^175^81^2.68
> ^0.109^0.035^13.5^0.0^0.054^0.228^4.119^1.090^0.290^8^0^8^8^0.32^146^44^
> 44^^^^^^^^2.250^2.730^2.180^83^6.00^~1 unit~^20.00^~1 neck, bone and
> skin removed~^75
>
> ~01124~^~EGG,WHITE,RAW,FRSH~^87.57^52^10.90^0.17^0.63^0.73^0.0^0.71^7^0.
> 08^11^15^163^166^0.03^0.023^0.011^20.0^0.0^0.004^0.439^0.105^0.190^0.005
> ^4^0^4^4^0.09^0^0^0^0.00^0.0^0^0^0^0^0^0.000^0.000^0.000^0^243.00^~1
> cup~^33.40^~1 large~^0
>
Wow - that''s one ugly piece of ...format...

Anyway, look at String.unpack in the ruby core lib to extract the information.

Max

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On 8/31/06, Remco Hh <rails-mailing-list@andreas-s.net>
wrote:
>
> > I am wondering if someone count point me in the right direction as
> > far as how to sort this data out and add it to a database, assuming I
> > know which columns I need (such as name, nutrition info (calories,
> > fat, carbs, protein), portion size, etc.  Do I use migrations or
> > something?  How do I sort out all of the information from each line?
> > I am still new to this so I am a little confused.
>
I would use shell script that uses gawk/sed and try to filter that out...

-- 
Igor MILOVANOVIĆ
http://f13o.pletisan.rs.ba/blog/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---