nmaster / usdanl-sr28-mysql Goto Github PK

Suggestion:
replacing "dos2unix *.txt" with "recode cp1250.. *.txt" in sr28_import.sh

The data from the USDA has some faulty UTF-8 encodings that prevent it from loading. As a workaround, I added the following Perl one-liners:

perl -p -i -e 's{\xB5}{\xC2\xB5}' NUTR_DEF.txt
perl -p -i -e 's{\x94}{\xe2\x80\xB3}' FOOTNOTE.txt
perl -p -i -e 's{\xA0}{\x20}' FOOTNOTE.txt
perl -p -i -e 's{\xE9}{\xC3\xA9}' WEIGHT.txt
perl -p -i -e 's{\x91}{\xE2\x80\x98}' FOOD_DES.txt
perl -p -i -e 's{\x92}{\xE2\x80\x99}' FOOD_DES.txt

That's an ugly hack, but at least it tells you where the bad characters are. The ones in NUTR_DEF.txt are critical, because stripping them out would turn micrograms into grams, and you don't want that. The others are less important, but I picked the characters they seem to intend based on viewing their data online.

Some sort of patch in your script would be handy. If you didn't hit this, I suspect your settings were such that MySQL quietly threw away the characters it didn't recognize.

There's no wiki but I wanted to look at all of the data so I created a simple query:

SELECT *
FROM FOOD_DES
LEFT JOIN NUT_DATA USING(NDB_No)
LEFT JOIN WEIGHT USING(NDB_No)
LEFT JOIN LANGUAL USING(NDB_No)
LEFT JOIN FD_GROUP USING(FdGrp_Cd)
LEFT JOIN NUTR_DEF USING(Nutr_No)
LEFT JOIN LANGDESC USING(Factor_Code)
LEFT JOIN DERIV_CD USING(Deriv_Cd)
LEFT JOIN DATSRCLN USING(NDB_No)
LEFT JOIN DATA_SRC USING(DataSrc_ID)
LEFT JOIN SRC_CD USING(Src_Cd)
LEFT JOIN FOOTNOTE ON(FOOTNOTE.NDB_No = FOOD_DES.NDB_No
    AND NUTR_DEF.Nutr_No = FOOTNOTE.Nutr_No)

The data files seem to have moved, based on this transcript of my build session:

--2018-08-11 21:27:38-- https://www.ars.usda.gov/SP2UserFiles/Place/12354500/Data/SR/SR28/dnload/sr28asc.zip
Resolving www.ars.usda.gov (www.ars.usda.gov)... 199.133.10.189
Connecting to www.ars.usda.gov (www.ars.usda.gov)|199.133.10.189|:443...
connected.
HTTP request sent, awaiting response...
30
1 Moved Permanently
Location: /ARSUserFiles/80400525/Data/SR/SR28/dnload/sr28asc.zip [following]

Your script uses "LOAD DATA INFILE," which doesn't work if MySQL has the --secure-file-priv option set. It should use "LOAD DATA LOCAL INFILE," which always works, and is what you probably meant anyway.

I was wondering, since you created this script, if you could also provide some of the most common SQL queries built against this database to pull out the data for a specific food at a specific weight or measurement?

I realize this is a pretty old repo, but did you have SQL errors when trying to import from those .txt files in your script? ie)

ERROR 1366 (HY000) at line 1: Incorrect decimal value: '' for column 'Pro_Factor' at row 2