如何将数据从ASCII（ISO / IEC 8859-1）导入到Rails / PGSQL数据库？

Question

我试图使用来自USDA的数据，网址为：http
:
//www.ars.usda.gov/Services/docs.htm?
docid =23634

ASCII（8.6Mb）-此文件包含ASCII（ISO / IEC
8859-1）格式的SR26数据。这些表以关系格式组织，并且最好与关系数据库管理系统（RDBMS）一起使用，这将使您能够形成自己的数据库查询并生成自定义报告。

我是刚接触这种数据的新手，并且认为我想以CSV格式获取它，也许吗？但是，这样我可能会失去关系，所以也许我应该直接使用PGSQL。不知道如何解决这个问题。

寻求指导，谢谢。

Answer 1

压缩文件包含许多文件：

  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. USDA国家营养数据库供标准参考的文件之间的关系”。以下页面描述了文件格式，告诉您每列的含义。您可以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的“导入”选项加载每个表。

实际上是CSV，他们刚刚决定使用完全奇怪的定界符并引用char。通过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库并将其批量加载到模型中。