Как я могу импортировать данные из ASCII (ISO/IEC 8859-1) в мою базу данных Rails/PGSQL?

Я пытаюсь использовать данные из USDA, найденные здесь: http://www.ars.usda.gov/Services/docs.htm?docid=23634

ASCII (8,6 Мб) - этот файл содержит данные SR26 в формате ASCII (ISO/IEC 8859-1), разделенные файлами. Эти таблицы организованы в реляционном формате, и их лучше всего использовать с системой управления реляционными базами данных (RDBMS), которая позволит вам формировать собственные запросы к базе данных и генерировать пользовательские отчеты.

Я новичок в манипулировании такими данными и думаю, что хотел бы получить их в CSV, может быть? Но тогда я могу потерять отношения, поэтому, возможно, мне следует перейти прямо к PGSQL. Не уверен, как подойти к этому.

Ищу руководства, спасибо.

Источник

3 ответа

Решение

Почтовый индекс содержит несколько файлов:

  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.

Страница 35 из PDF должна помочь вам - "Рисунок 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, они просто решили использовать совершенно странный разделитель и кавычки. Импорт через 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 Nutrition в postgreSQL.

Это очень грубо в данный момент, но должно помочь начать.

Проверьте это здесь: https://github.com/rlucha/usda-nutrition-database-migration

Источник

У них есть версия этой базы данных для доступа Microsoft, если у вас есть ее, вы можете загрузить ее, а затем конвертировать в MySQL с помощью Access To MySQL.

Источник
Другие вопросы по тегам