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: 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.11^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.


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.