CSVファイルをMySQLのデータベースにインポートしたい

度々お世話になっております。 CSVファイルの中身をデータベースに入れたいのですが、Administrateの管理画面に反映されなくて困っております。アドバイスをいただけますと幸いです。なお、administrateにcsvインポート機能をつけるのが難しすぎて断念したため、Activerecord-Importというgemを使用しています。

drug_1.csv データ形式：CSV（コンマ区切り、UTF-8）

テーブルのアソシエーションの状態はこのような形です。

class Drug < ApplicationRecord
  extend Enumerize
  has_many :drug_ingredients,dependent: :destroy
  has_many :ingredients,through: :drug_ingredients
  has_many :drug_symptoms, dependent: :destroy
  has_many :symptoms, through: :drug_symptoms
  belongs_to :maker_name
~
end

class MakerName < ApplicationRecord
  has_many :drug, dependent: :destroy
end

  create_table "drugs", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
    t.string "name", null: false, comment: "商品名"
    t.string "effect_text", comment: " 効能効果"
    t.string "usage", comment: "用法用量"
    t.string "document_url", comment: "添付文書URL"
    t.integer "formulation", comment: "剤型 -- tablet(錠剤): 0, powder(粉): 1, capsule(カプセル): 2, liquid(液剤): 3, nose(点鼻): 4"
    t.integer "division", comment: "リスク区分 -- to_guide(要指導医薬品): 0, one_kind(一類医薬品): 1, two_kind(二類医薬品): 2, three_kind(三類医薬品): 3, two_designate(指定二類医薬品): 4"
    t.boolean "taxation", default: false, null: false, comment: "セルフメディケーション税制"
    t.integer "for_days", comment: "何日ぶん"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer "maker_name_id", comment: "製薬企業id"
    t.text "formula", comment: "成分分量"
    t.text "otc_text", comment: "製品の特徴"
    t.text "caution", comment: "使用上の注意"
  end

  create_table "maker_names", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
    t.string "name", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

Rails7だと使用しなくても入れることができるようですが、やり方がわからなかったのでひとまずこの方法で試してみました。ここのサイトの手順に従っています。

lib/import_csv.rb
require "csv"

class ImportCsv
  def self.execute(model:, file_name: nil)
    model_name = model.to_s.classify
    table_name = model_name.tableize
    file_name ||= table_name.singularize
    path = Rails.root.join("db/csv_data/#{file_name}.csv")

    list = []
    CSV.foreach(path, headers: true) do |row|
      list << row.to_h
    end
    # 与えられたモデルに CSVデータを投入
    model_name.constantize.import!(list, on_duplicate_key_update: :all)
    # 次に振る id を正常化
    ActiveRecord::Base.connection.execute("select setval('#{table_name}_id_seq',(select max(id) from #{table_name}))")
  end
end

irb(main):001:0> require "import_csv"
=> true
irb(main):002:0> ImportCsv.execute(mod
el: Drug, file_name: "drug_1" )
/Users/flare_home/workspace/runteq/応用編/drug_app5/vendor/bundle/ruby/3.0.0/gems/activemodel-7.0.6/lib/active_model/attribute.rb:211:in `with_value_from_database': can't write unknown attribute `id` (ActiveModel::MissingAttributeError)

Excelではidの列を作っているのに、idがないと言われてしまいました。 drugモデルのidなのか、maker_nameモデルのidなのかわかりません。ご助言をお願いいたします。

情報が少ないため精度が低い回答になることをご了承ください。

drug, maker_nameと複数のテーブルに情報を入れようとされているようですが、個別(drugならdrugに、maker_nameならmaker_nameに)データをインポートする方向で試したりしてみてはいかがでしょうか。

参考になるかは分かりませんが、Qiitaの記事を共有しておきますね。

https://qiita.com/makicamel/items/b6d4f3d2661fc66103ed

コメントありがとうございます。一つ質問させていただきたいのですが、やはりこのやり方だと別テーブルへの入力は厳しいでしょうか。今回データにしたいものが500件以上あるため、手作業で紐付けするのはちょっと厳しい状況です。

下のURLが最初にやろうとしていたことです。データベースにはなにも反映されませんでしたが…。

https://rihu-do.hatenablog.com/entry/2020/12/06/082822

１つ１つ問題を切り出していくのが良いかと思います。個別にインポートができるようであれば、インポートのやり方は問題ないことが確定するため、１つずつ問題を切り分けて進めてみることをオススメします。

かしこまりました。ひとまずこの方法でCSVをインポートができるか確認します。

maker_nameを消し、カラム名をid,name,formulation,division,effect_text,formula,caution,otc_text,document_url,usageに変更いたしました。すると、このようなエラー文が表示されました。

StandardError
An error has occurred, all later migrations canceled:
Mysql2::Error: Multiple primary key defined

schema.rbにてカラムの確認を行いましたが、プライマリーキーの設定がされていないことが原因かなと思いました。 https://blog.kujira-station.com/201508241815

  create_table "drugs", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
    t.string "name", null: false, comment: "商品名"
    t.string "effect_text", comment: " 効能効果"
    t.string "usage", comment: "用法用量"
    t.string "document_url", comment: "添付文書URL"
    t.integer "formulation", comment: "剤型 -- tablet(錠剤): 0, powder(粉): 1, capsule(カプセル): 2, liquid(液剤): 3, nose(点鼻): 4"
    t.integer "division", comment: "リスク区分 -- to_guide(要指導医薬品): 0, one_kind(一類医薬品): 1, two_kind(二類医薬品): 2, three_kind(三類医薬品): 3, two_designate(指定二類医薬品): 4"
    t.boolean "taxation", default: false, null: false, comment: "セルフメディケーション税制"
    t.integer "for_days", comment: "何日ぶん"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer "maker_name_id", comment: "製薬企業id"
    t.text "formula", comment: "成分分量"
    t.text "otc_text", comment: "製品の特徴"
    t.text "caution", comment: "使用上の注意"
  end

そこで、プライマリーキーの設定を行おうとしました。

class AddDrugTo2 < ActiveRecord::Migration[7.0]
  def change
    change_column :drugs, :id, :primary_key
  end
end

この後マイグレーションを行いましたが、エラーんは同じくMysql2::Error: Multiple primary key definedでした。この時にマイグレーションファイルにて既存のテーブルの主キーのカラムを、プライマリーキーに変更するやり方が分かりませんでしたので、それを教えていただけると助かります。

もしかしたら主キーが自動的に設定されてしまうと、CSVデータから追加でidを手動で入力できなくなるのかと思い、composite_primary_keysというgemを入れ、複合キーの設定をしました。

class Drug < ApplicationRecord
  self.primary_keys = :drug_id, :maker_name
  extend Enumerize
  has_many :drug_ingredients,dependent: :destroy
  has_many :ingredients,through: :drug_ingredients
  has_many :drug_symptoms, dependent: :destroy
  has_many :symptoms, through: :drug_symptoms
  belongs_to :maker_name

class MakerName < ApplicationRecord
  self.primary_key = :maker_name_id
  has_many :drugs, dependent: :destroy, foreign_key: :maker_name_id
end

そして、railsを立ち上げましたが、うまくいきませんでした。

$ rails c         
Loading development environment (Rails 7.0.6)
irb(main):001:0> ImportCsv.execute(mod
el: Drug, file_name: "drug_nomaker" )
(irb):1:in `<main>': uninitialized constant ImportCsv (NameError)
Did you mean?  Importmap
irb(main):002:0> require "import_csv"
=> true
irb(main):003:0> ImportCsv.execute(mod
el: Drug, file_name: "drug_nomaker" )
/Users/flare_home/workspace/runteq/応用編/drug_app5/vendor/bundle/ruby/3.0.0/gems/activemodel-7.0.6/lib/active_model/attribute.rb:211:in `with_value_from_database': can't write unknown attribute `id` (ActiveModel::MissingAttributeError)

ご助言いただけましたら幸いです。

共有されたログと記事から、「原因はプライマリキーが２つ登録しようとしいることが原因です」（記事より）に当たるのではないかと推測しています。

StandardError
An error has occurred, all later migrations canceled:
Mysql2::Error: Multiple primary key defined

カラム名をid,name,formulation,division,effect_text,formula,caution,otc_text,document_url,usage

上記の記載と記事を鑑みると、idのところが不要なのかと推測しました。一度検証等を行ってみてください。

試しにidの項目を消してみました。すると、今度はnameについて言われてしまいました。

$ rails c      
Loading development environment (Rails 7.0.6)
irb(main):001:0> require "import_csv"
=> true
irb(main):002:0> ImportCsv.execute(model: Drug, file_name: "drug_nomaker" )
/Users/flare_home/workspace/runteq/応用編/drug_app5/vendor/bundle/ruby/3.0.0/gems/activemodel-7.0.6/lib/active_model/attribute.rb:211:in `with_value_from_database': can't write unknown attribute `name` (ActiveModel::MissingAttributeError)

ちなみに、現在のMySQL内のテーブルの中身はこちらです。

$ mysql -u root    
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 8.1.0 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| drug_app5_development |
| drug_app5_test        |
| drug_app_5_production |
| drug_app_5_test       |
| information_schema    |
| mysql                 |
| performance_schema    |
| sys                   |
+-----------------------+
8 rows in set (0.00 sec)

mysql> show tables from drug_app5_development;
+---------------------------------+
| Tables_in_drug_app5_development |
+---------------------------------+
| ar_internal_metadata            |
| drug_ingredients                |
| drug_symptoms                   |
| drugs                           |
| ingredients                     |
| maker_names                     |
| schema_migrations               |
| symptoms                        |
| users                           |
+---------------------------------+
9 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM drugs FROM drug_app5_development;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | bigint       | NO   | PRI | NULL    | auto_increment |
| name          | varchar(255) | NO   |     | NULL    |                |
| effect_text   | varchar(255) | YES  |     | NULL    |                |
| usage         | varchar(255) | YES  |     | NULL    |                |
| document_url  | varchar(255) | YES  |     | NULL    |                |
| formulation   | int          | YES  |     | NULL    |                |
| division      | int          | YES  |     | NULL    |                |
| taxation      | tinyint(1)   | NO   |     | 0       |                |
| formula       | text         | YES  |     | NULL    |                |
| otc_text      | text         | YES  |     | NULL    |                |
| caution       | text         | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

can't write unknown attribute 'name' (ActiveModel::MissingAttributeError)

こちらのエラーログから確認したいのですが、Drug.newした際にname属性があるインスタンス変数が返ってきているか確認＆共有をお願いできますでしょうか。

もしインポートが難しい場合は、Rubyのプログラムでcsvファイルを開いて、各行に対してレコードを作成するようなコードを書く方法なども別方法としてはあり得る方法かと思ったので共有まで。

関係あるか分かりませんがMysql５系と8系で少し変更があったようなのですが、お使いのMysqlは５系でしょうか？

参考：https://qiita.com/yes_dog/items/57c1a8c03aff8bb177de

Drug.newしてみましたが、name属性はありますね。

irb(main):003:0> drug = Drug.new
=> 
#<Drug:0x00000001220afb68
...
irb(main):004:0> drug
=> 
#<Drug:0x00000001220afb68
 id: nil,
 name: nil,
 effect_text: nil,
 usage: nil,
 document_url: nil,
 formulation: nil,
 division: nil,
 taxation: false,
 for_days: nil,
 created_at: nil,
 updated_at: nil,
 maker_name_id: nil,
 formula: nil,
 otc_text: nil,
 caution: nil>
irb(main):005:0>

現在使っているのはMysql8.1です

Server version: 8.1.0 Homebrew

CSVで取り込む方法は他にもあるようなので、調べてそうしたもので試してみるのも検討しましょう。

参考：現場Railsの7章 CSVデータを入力(インポート)するetc 参考：https://qiita.com/michikun06/items/287fff238737653866ec 参考：https://qiita.com/d0ne1s/items/d49423796dd1c801afd3 参考：https://docs.ruby-lang.org/ja/latest/library/csv.html

https://qiita.com/yes_dog/items/57c1a8c03aff8bb177de この教えていただいたやり方でCSVデータが取り込めましたが、Excelの行ごとにインポートできていない状態です。なお、usage = @usageはSQL内で使用するとエラーが生じるため、インポートの見込みが立った時にカラム名を変えてインポートしようと考えています。

mysql> use drug_app5_development;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> mysql>  load data local infile "/Users/flare_home/workspace/runteq/応用編/drug_app5/db/csv_data/drug_nomaker.csv" into table drugs fields terminated by ',' optionally enclosed by '"' ignore 1 lines (@id,@name,@formulation,@division,@effect_text,@formula,@caution,@otc_text,@document_url) set id = @id, name = @name, formulation = @formulation, division = @division, effect_text = @effect_text, formula = @formula, caution = @caution, otc_text = @otc_text, document_url = @document_url;
Query OK, 1493 rows affected, 4454 warnings (0.05 sec)
Records: 2031  Deleted: 0  Skipped: 538  Warnings: 4454

Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '［用法関連注意］' for column 'id' at row 437
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '［用法関連注意］' for column 'id' at row 438
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '（1）用法・用量を厳守してください。' for column 'id' at row 439
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '（2）小児に服用させる場合には，保護者の指導監督のもとに服用させてください。' for column 'id' at row 440
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 441
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '［成分関連注意］' for column 'id' at row 442
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '本剤に含まれるヘスペリジ' for column 'id' at row 443られることがあります。"
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1062): Duplicate entry '45' for key 'drugs.PRIMARY'
Warning (Code 1262): Row 444 was truncated; it contained more data than there were input columns
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 445
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '［年齢：1回量：1日服用回数］' for column 'id' at row 446
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '成人（15歳以上）：3錠：3回' for column 'id' at row 447
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1265): Data truncated for column 'id' at row 448
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1062): Duplicate entry '11' for key 'drugs.PRIMARY'
Warning (Code 1265): Data truncated for column 'id' at row 449
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1062): Duplicate entry '5' for key 'drugs.PRIMARY'
Warning (Code 1265): Data truncated for column 'id' at row 450
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1062): Duplicate entry '5' for key 'drugs.PRIMARY'
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 451
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '［用法関連注意］' for column 'id' at row 452
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '［用法関連注意］' for column 'id' at row 453
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '（1）用法・用量を厳守してください。' for column 'id' at row 454
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '（2）小児に服用させる場合には，保護者の指導監督のもとに服用させてください。' for column 'id' at row 455
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 456
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '［成分関連注意］' for column 'id' at row 457
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '本剤に含まれるヘスペリジ' for column 'id' at row 458られることがあります。"
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1062): Duplicate entry '46' for key 'drugs.PRIMARY'
Warning (Code 1262): Row 459 was truncated; it contained more data than there were input columns
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 460
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '［年齢：1回量：服用回数］' for column 'id' at row 461
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1366): Incorrect integer value: '成人（15才以上）：3錠：1日3回' for column 'id' at row 462
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1265): Data truncated for column 'id' at row 463
Warning (Code 1048): Column 'name' cannot be null
Warning (Code 1062): Duplicate entry '12' for key 'drugs.PRIMARY'
Warning (Code 1265): Data truncated for column 'id' at row 464

テーブルの中身は現在このような形で、@の中身と噛み合っていない状況です。また、テーブル内の最初の一行しか取得できていません。

こちらがCSVファイルの中身です。 nameカラムの中身などが跳ねられていると考えています。

こちらがMySQLの文字コードの設定です。

mysql> show variables like "chara%";
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8mb4                                                |
| character_set_connection | utf8mb4                                                |
| character_set_database   | utf8mb4                                                |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8mb4                                                |
| character_set_server     | utf8mb4                                                |
| character_set_system     | utf8mb3                                                |
| character_sets_dir       | /opt/homebrew/Cellar/mysql/8.1.0/share/mysql/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.02 sec)

mysql> show create table drugs \G;
*************************** 1. row ***************************
       Table: drugs
Create Table: CREATE TABLE `drugs` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品名',
  `effect_text` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ' 効能効果',
  `usage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用法用量',
  `document_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '添付文書URL',
  `formulation` int DEFAULT NULL COMMENT '剤型 -- tablet(錠剤): 0, powder(粉): 1, capsule(カプセル): 2, liquid(液剤): 3, nose(点鼻): 4',
  `division` int DEFAULT NULL COMMENT 'リスク区分 -- to_guide(要指導医薬品): 0, one_kind(一類医薬品): 1, two_kind(二類医薬品): 2, three_kind(三類医薬品): 3, two_designate(指定二類医薬品): 4',
  `taxation` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'セルフメディケーション税制',
  `for_days` int DEFAULT NULL COMMENT '何日ぶん',
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `maker_name_id` int DEFAULT NULL COMMENT '製薬企業id',
  `formula` text COMMENT '成分分量',
  `otc_text` text COMMENT '製品の特徴',
  `caution` text COMMENT '使用上の注意',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=61433 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

MySQL Workbenchを使用してのインポートも考えています。現在のテーブル状況はこちらです。

MySQL Workbenchを使用してのインポートも考えています。良いと思います。やりたいこと（データをデータベースに入れ込む）に対して、アプローチは１つではないので、引き続き「現状把握・分解・仮説・検証」のサイクルを回していきましょう。

上記で共有している参考記事も含め、いろいろ調べたりして、「現状把握・分解・仮説・検証」を行っていきましょう。

MySQL Workbenchを使用してDrugsテーブルへのcsvのインポートをUTF-8形式で試みたところ、エラーが発生してしまいました。

Unhandled exception: 'ascii' codec can't decode byte 0xe7 in position 102: ordinal not in range(128)

と言われてしまいました。

CotEditorというソフトを用いてUTF-8から無損失ASC IIに変換し、csvを読み込ませたところ、今度はインポート機能は動いたのですが、エラーが発生してしまいました。列の認識をできていないことが問題なようです。

Starting...
Prepare Import...
Prepare Import done
Import data file....
- Prepare Import

Traceback (most recent call last):
  File "/Applications/MySQLWorkbench.app/Contents/Resources/libraries/workbench/wizard_progress_page_widget.py", line 197, in thread_work
    self.func()
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_import_wizard.py", line 131, in start_import
    retval = self.module.start(self.stop)
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_import_export_be.py", line 300, in start
    ret = self.start_import()
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_power_import_export_be.py", line 430, in start_import
    self._editor.executeManagementCommand(query, 1)
grt.DBError: ("Column 'formulation' specified twice", 1110)
ERROR: Import data file: ("Column 'formulation' specified twice", 1110)
Failed

Excelでの保存形式をUTF-8のカンマ区切りのcsv形式で保存しておりますが、保存時のセルごとの区切りと見出し列の認識が上手くいっていないのでしょうか。

Excelを用いたCSVファイルのエクスポート時に、idカラムやdivisionカラムに含まれている数字は文字列に設定した方がよろしいでしょうか？また、セル内に含まれているカンマが誤って区切りだと認識されてしまうことはありますでしょうか？

CotEditorというソフトも存じ上げないので、ご質問されていることに対して適切な回答ができず申し訳ございません。

書籍、現場Railsにも参考になる記載があるかと思うので、いろいろ調べて試してみてください。

CarbazochromeT / drug_app

CSVファイルをMySQLのデータベースにインポートしたい #10