How can I get the USDA's nutrition data into excel?
January 5, 2008 10:25 PM Subscribe
How can I get data from the USDA's food database into excel?
I need to get data from the USDA's food database, but it has to include amino acids and fatty acids. They give you this information on their web interface tool (http://www.nal.usda.gov/fnic/foodcomp/search/), and it's seems to be sort of in their downloadable database (which is in ms access format). I know I can export a table from ms access into excel, but it looks like
I'd have to mine out the amino acids, fatty acids etc., they are not in the regular, main data table. You can see one guy tried to do this already : http://www.vegsource.com/harris/food_comp.htm but he didn't get things quite right - if you check the amino and fatty acid values, you can see they are not even close to those returned by the usda's web interface utility.
I need to get data from the USDA's food database, but it has to include amino acids and fatty acids. They give you this information on their web interface tool (http://www.nal.usda.gov/fnic/foodcomp/search/), and it's seems to be sort of in their downloadable database (which is in ms access format). I know I can export a table from ms access into excel, but it looks like
I'd have to mine out the amino acids, fatty acids etc., they are not in the regular, main data table. You can see one guy tried to do this already : http://www.vegsource.com/harris/food_comp.htm but he didn't get things quite right - if you check the amino and fatty acid values, you can see they are not even close to those returned by the usda's web interface utility.
You're correct. You can download all of that, but it's in a tough format to chew (ha.).
The tables are pretty confusing, you'd need to link the correct serving to the food to make it accurate. Give me a few minutes and I'll see what I can come up with for you.
posted by cdmwebs at 12:01 AM on January 6, 2008
The tables are pretty confusing, you'd need to link the correct serving to the food to make it accurate. Give me a few minutes and I'll see what I can come up with for you.
posted by cdmwebs at 12:01 AM on January 6, 2008
Ok, I assume by "downloadable database", you mean the "full version" Access database in their "download files" section. This database contains all the same data that you can access in the "search" section that you linked.
If you download and open the database (which is called sr20) in Access, you get two windows - one is a "welcome" screen, and one is a window containing all the tables with the data, as well as some sample queries. The problem here is that the database, while it contains all the data you need, doesn't seem to have an existing query to pull out the data in the format you want, with the type of detail you want. What you are going to have to do is to construct your own query using the query builder, or modify one of the existing queries (which you do in "design view" for a query). Once that query is built, and the resulting data table contains the info you need, you can then export it directly to Excel to continue working with the data.
We can try to help you build the query, if you can explain exactly what you are looking for. As an example, say that you use the "search" function on the site to look for carrot. On the results page, you choose the first item Babyfood, carrots and beef, strained. On the next page, what are you looking for? The nutrition value for a jar of babyfood, or the nutrition value of this item per 100 grams/10oz or whatever? Say that you choose 500g as the base value you want to use. On the next page, which of these nutritional values do you need? All of them? Also, do you need this data for ALL the foods that are in the database, or only a subset of them?
If you explain further what you want, we can help you build the right query from the database. It's all in there, it's just a matter of extracting it in a form that will be useful to you.
posted by gemmy at 12:04 AM on January 6, 2008
If you download and open the database (which is called sr20) in Access, you get two windows - one is a "welcome" screen, and one is a window containing all the tables with the data, as well as some sample queries. The problem here is that the database, while it contains all the data you need, doesn't seem to have an existing query to pull out the data in the format you want, with the type of detail you want. What you are going to have to do is to construct your own query using the query builder, or modify one of the existing queries (which you do in "design view" for a query). Once that query is built, and the resulting data table contains the info you need, you can then export it directly to Excel to continue working with the data.
We can try to help you build the query, if you can explain exactly what you are looking for. As an example, say that you use the "search" function on the site to look for carrot. On the results page, you choose the first item Babyfood, carrots and beef, strained. On the next page, what are you looking for? The nutrition value for a jar of babyfood, or the nutrition value of this item per 100 grams/10oz or whatever? Say that you choose 500g as the base value you want to use. On the next page, which of these nutritional values do you need? All of them? Also, do you need this data for ALL the foods that are in the database, or only a subset of them?
If you explain further what you want, we can help you build the right query from the database. It's all in there, it's just a matter of extracting it in a form that will be useful to you.
posted by gemmy at 12:04 AM on January 6, 2008
If you download the pdf, gemmy, you'll see the table layout and data definitions. I can't remember, but I think you have to link through a serving table to get the right amounts for each food.
posted by cdmwebs at 12:07 AM on January 6, 2008
posted by cdmwebs at 12:07 AM on January 6, 2008
Best answer: I'm by no means certain this data is correct, but here you go. All data is for 100 grams per food item. I checked a few records with the USDA search, but thats not saying much! To see the units of measure, you'll have to go to the search and see what they're using. I could have included it in the field names, but I wanted to get this back to you first to see if it's what you're after.
To re-create this in Access (in 10 easy steps):
posted by cdmwebs at 2:34 AM on January 6, 2008
To re-create this in Access (in 10 easy steps):
- Download the Access version.
- Enable any stupid security warnings (or turn it off for this task).
- Create a new query in Design View.
- Add these tables:
- NUTR_DEF
- NUT_DATA
- FOOD_DES
- Select the following fields:
- FOOD_DES.Shrt_Desc
- NUTR_DEF.NutrDesc
- NUT_DATA.Nutr_Val
- Make the query a Crosstab.
- In the query design grid, set the fields to:
- Shrt_Desc to Row Heading
- NutrDesc to Column Heading
- Nutr_Val to Value, and make it Sum under Total
- Click View or Run
- Export to Excel
- Profit!
posted by cdmwebs at 2:34 AM on January 6, 2008
Ha! I'm just now getting back to this, and see that cdmwebs has already done what I was thinking. I'm glad I checked back here before looking a the DB again. Nice work!
posted by gemmy at 9:57 AM on January 6, 2008
posted by gemmy at 9:57 AM on January 6, 2008
Response by poster: You guys are da bomb! I'll be back and post again when I have some more time to verify things etc., but from the sound of it cdmwebs has nailed it. I'll also check the accuracy of the data and post the file when I'm done, should anyone want it. To clarify, I basically wanted all the data in excel format so I can cut and paste it later with ease. Like the file at the vegsource site, but with accurate data.
posted by Nish ton at 11:24 AM on January 6, 2008
posted by Nish ton at 11:24 AM on January 6, 2008
Response by poster: Okay, here it is : http://myfreefilehosting.com/f/4bd5206289_3.63MB
posted by Nish ton at 12:55 AM on January 9, 2008
posted by Nish ton at 12:55 AM on January 9, 2008
This thread is closed to new comments.
posted by AmbroseChapel at 10:45 PM on January 5, 2008