我试图使用美国农业部在这里找到的数据:http://www.ars.usda.gov/Services/docs.htm?docid=23634
ASCII (8.6Mb) -该文件包含ASCII格式的SR26数据(ISO/IEC 8859-1),分隔文件。这些表以关系格式组织,并且最好与关系数据库管理系统(RDBMS)一起使用,它将允许您形成自己的数据库查询并生成自定义报告。
我是像这样操纵数据的新手,我想把它放在CSV中,也许?但这样我可能会失去关系所以我应该直接使用PGSQL。不知道该怎么处理。
寻求指导,谢谢。
zip文件包含以下文件:
inflating: DATA_SRC.txt
inflating: DATSRCLN.txt
inflating: DERIV_CD.txt
inflating: FD_GROUP.txt
inflating: FOOD_DES.txt
inflating: FOOTNOTE.txt
inflating: LANGDESC.txt
inflating: LANGUAL.txt
inflating: NUT_DATA.txt
inflating: NUTR_DEF.txt
inflating: sr26_doc.pdf
inflating: SRC_CD.txt
inflating: WEIGHT.txt
每一个似乎都是一个奇怪的几乎类似csv的格式,例如NUTR_DEF.txt
:
~287~^~g~^~GALS~^~Galactose~^~2~^~2100~
~291~^~g~^~FIBTG~^~Fiber, total dietary~^~1~^~1200~
加上sr26_doc.pdf
,文档
创建表定义
所以这里需要做的是为数据库创建SQL表定义——每个输入文件一个表。您需要CREATE TABLE
命令;查看PostgreSQL文档
PDF的第35页应该可以帮助您-"图1。美国农业部国家营养标准参考数据库文件之间的关系"。下面的页面描述了文件格式,告诉您每一列的含义。您可以根据此描述编写CREATE TABLE
语句。
这里有一个例子,对于FOOD_DES.txt
(食物描述),第一个条目。
CREATE TABLE food_des (
"NDB_No" varchar(5) NOT NULL PRIMARY KEY,
"FdGrp_Cd" varchar(4) NOT NULL,
"Long_Desc" varchar(200) NOT NULL,
"Shrt_Desc" varchar(60) NOT NULL,
"ComName" varchar(100),
"ManufacName" varchar(65),
"Survey" varchar(1),
"Ref_desc" varchar(135),
"Refuse" smallint,
"SciName" varchar(65),
"N_Factor" NUMERIC(4,2),
"Pro_Factor" NUMERIC(4,2),
"Fat_Factor" NUMERIC(4,2),
"CHO_Factor" NUMERIC(4,2)
);
这是描述的一个相当字面的副本。这不是我设计表格的方式
我使用NUMERIC
任意精度十进制浮点类型来保证非整数数值类型的精度。如果性能比精度更重要,可以使用float4
。
对于关系,您使用FOREIGN KEY
约束—仅colname coltype REFERENCES othertable(othercol)
就足以创建一个。
重要的:我将列名双引号,以保持与定义中的名称相同。这意味着当你引用它们时,你必须总是双引号,例如SELECT "NDB_No" FROM food_des;
。如果您不希望这样,只需去掉双引号-或者选择不同的名称。您不必坚持使用他们使用的笨拙的缩写列名,并且这样写是非常合理的:
CREATE TABLE food_description (
ndb_no varchar(5) NOT NULL PRIMARY KEY,
foodgroup_code varchar(4) NOT NULL,
long_description varchar(200) NOT NULL,
short_description varchar(60) NOT NULL,
common_name varchar(100),
manufacturer_name varchar(65),
等。类似地,如果您正在使用Rails,可以将表定义转换为遵循Rails的约定,特别是如果您打算通过Rails进行数据加载。
加载数据如果这些是合理的分隔文件,那么您可以使用psql
命令copy
或PgAdmin-III的"import"选项加载每个表。
它实际上是CSV,他们只是决定使用完全奇怪的分隔符和引号字符。通过psql
导入:
copy food_des FROM 'FOOD_DES.txt' (FORMAT CSV, DELIMITER '^', QUOTE '~');
或在任何与PostgreSQL对话的工具中使用等效的
结果是一个合理的表:
craig=> select * from food_des limit 2;
NDB_No | FdGrp_Cd | Long_Desc | Shrt_Desc | ComName | ManufacName | Survey | Ref_desc | Refuse | SciName | N_Factor | Pro_Factor | Fat_Factor | CHO_Factor
--------+----------+----------------------------+--------------------------+---------+-------------+--------+----------+--------+---------+----------+------------+------------+------------
01001 | 0100 | Butter, salted | BUTTER,WITH SALT | | | Y | | 0 | | 6.38 | 4.27 | 8.79 | 3.87
01002 | 0100 | Butter, whipped, with salt | BUTTER,WHIPPED,WITH SALT | | | Y | | 0 | | 6.38 | 4.27 | 8.79 | 3.87
(2 rows)
同样,如果使用Rails,您可以使用任何您想要的Rails CSV库并批量加载到模型中。
使用@craig-ringer的提示,我创建了一个脚本,将原始USDA营养数据库文件迁移到postgreSQL。
现在很粗糙,但应该有助于开始。
看这里:https://github.com/rlucha/usda-nutrition-database-migration
他们有一个微软访问的数据库版本,如果你有,你可以下载访问,然后使用access to MySQL转换为MySQL。