mysql - 如何将数据从 ASCII (ISO/IEC 8859-1) 导入我的 Rails/PGSQL 数据库？

Question

我正在尝试使用来自美国农业部的数据:http://www.ars.usda.gov/Services/docs.htm?docid=23634

ASCII (8.6Mb) - This file contains the SR26 data in ASCII (ISO/IEC 8859-1), delimited files. These tables are organized in a relational format, and are best used with a relational database management system (RDBMS), which will allow you to form your own queries of the database and generate custom reports.

我不熟悉处理这样的数据，我想我想用 CSV 格式获取它，也许吧？但是，那么我可能会失去关系，所以也许我应该直接使用 PGSQL。不知道如何解决这个问题。

寻求指导，谢谢。

Answer 1

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)
);

这是描述的一个非常直白的副本。这不是我设计 table 的方式

我使用了 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，他们刚刚决定使用完全奇怪的分隔符和引号字符。通过 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 库并批量加载到模型中。