Rails - Sep 2006 - How do I parse ASCII delimited files to insert into a MySQL database?

Hi all,

I have a list of food and their nutritional values and am trying to  
figure out how to import their values into a MySQL database (the list  
can be found here: http://www.ars.usda.gov/Services/docs.htm? 
docid=13746).  A description of the file is:

"This file contains the SR19 data in ASCII, delimited files. These  
tables are organized in a relational format, and are best used with a  
relational database management system (RDBMS), which will allow you   
to form your own queries of the database and generate custom reports."

The format of each line looks like the following samples:

~02050~^~VANILLA  
EXTRACT~^52.58^288^0.06^0.06^0.26^12.65^0.0^12.65^11^0.12^12^6^148^9^0.1 
1^0.072^0.230^0.0^0.0^0.011^0.095^0.425^0.035^0.026^0^0^0^0^0.00^0^0^0^0 
.00^0.0^0^0^0^0^0^0.010^0.010^0.004^0^208.00^~1 cup~^13.00^~1 tbsp~^0

~07248~^~OSCAR MAYER,WIENERS LITTLE (PORK,TURKEY) 
~^55.00^311^10.90^28.70^3.10^2.30^0.0^1.60^13^1.02^13^97^159^1039^1.84^0 
.110^^^0.0^^^^^^^^^^^0^^^^^^^^^^11.150^14.330^2.670^55^9.00^~1  
piece~^57.00^~1 serving~^0

~12563~^~ALMONDS,DRY RSTD,W/ 
SALT~^2.60^597^22.09^52.83^3.20^19.29^11.8^4.90^266^4.51^286^489^746^339 
^3.54^1.170^2.620^2.8^0.0^0.074^0.859^3.850^0.229^0.126^33^0^33^33^0.00^ 
1^0^0^26.00^0.0^0^1^0^0^1^4.047^33.658^12.649^0^138.00^~1 cup, whole  
kernels~^28.35^~1 oz~^0

Could someone tell me how to go about tackling this and getting the  
data parsed and imported correctly?

Thanks for any help -- sorry, I''m still new to this.

Rob

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

Looks like a job for gsub and split.  Something like:

  string.gsub!(/~/, '''')       # Discard ''~''
noise.

  fields = string.split(''^'')  # Split string into fields.

I took the liberty of discarding the tildes, because I
assume that your code will "know" which fields to save
as strings, etc.  YMMV.

-r
-- 
http://www.cfcl.com/rdm            Rich Morin
http://www.cfcl.com/rdm/resume     rdm-go8te9J4rpw@public.gmane.org
http://www.cfcl.com/rdm/weblog     +1 650-873-7841

Technical editing and writing, programming, and web development

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