How do I go about importing all of this data into a database?

rubyonrails-talk
1

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.

2

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

3

Wow - that's one ugly piece of ...format...

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

Max

4

I would use shell script that uses gawk/sed and try to filter that out…