RailsでCSVファイルをデータベースに格納したい

Question

実現したいこと

ruby on railsで、db/csvディレクトリに置いた「ACWI_ETF_Stock_Price_History.csv」の内容をコマンドを使ってデータベースに登録したいと考えています（登録するための作業をimport_data.rbに記載）。

発生している問題・分からないこと

docker環境で、rubyファイルを実行したところエラーが表示されました。CSV一行目の属性情報のところでエラーが出ているようです

@MacBook-Air-3 yfinance % docker-compose exec web bash
root@e47b6dfc6df2:/yfinance# ruby import_data.rb
/usr/local/lib/ruby/3.1.0/csv/parser.rb:1077:in `parse_quotable_robust': Illegal quoting in line 1. (CSV::MalformedCSVError)
	from /usr/local/lib/ruby/3.1.0/csv/parser.rb:1007:in `block in parse_quotable_loose'
	from /usr/local/lib/ruby/3.1.0/csv/parser.rb:52:in `block in each_line'
	from /usr/local/lib/ruby/3.1.0/csv/parser.rb:49:in `each_line'
	from /usr/local/lib/ruby/3.1.0/csv/parser.rb:49:in `each_line'
	from /usr/local/lib/ruby/3.1.0/csv/parser.rb:963:in `parse_quotable_loose'
	from /usr/local/lib/ruby/3.1.0/csv/parser.rb:406:in `parse'
	from /usr/local/lib/ruby/3.1.0/csv.rb:2554:in `each'
	from /usr/local/lib/ruby/3.1.0/csv.rb:2554:in `each'
	from /usr/local/lib/ruby/3.1.0/csv.rb:1334:in `block in foreach'
	from /usr/local/lib/ruby/3.1.0/csv.rb:1610:in `open'
	from /usr/local/lib/ruby/3.1.0/csv.rb:1333:in `foreach'
	from import_data.rb:7:in `<main>'

該当のソースコード

require 'csv'

# 1 … CSVファイルのパスを設定する
csv_file_path = 'db/csv/ACWI_ETF_Stock_Price_History.csv' # ルートディレクトリにスクリプトファイルを、db/csvディレクトリにcsvファイルを置いた場合の記述 

# 2 … CSVファイルからデータを読み込み、Hogesテーブルに登録
CSV.foreach(csv_file_path, headers: true) do |row|
  
  ImportCsv.create!(
    date: Date.parse(row['Date']),
    price: row['Price'].to_f,
    open: row['Open'].to_f,
    high: row['High'].to_f,
    low: row['Low'].to_f,
    volume: row['Vol.'].gsub('M', '').to_i * 1000000,
    change_percentage: row['Change %'].gsub('%', '').to_f / 100
  )
end

# 3 … ファイル実行後に、処理が成功したという文章を表示させる。
# ここはお好みで。記述しなくても動作はする。
puts 'CSV data imported successfully!'

"Date","Price","Open","High","Low","Vol.","Change %"
"08/01/2024","116.64","113.98","117.00","105.53","56.03M","2.19%"
"07/01/2024","114.13","112.81","116.81","111.11","59.14M","1.54%"
"06/01/2024","112.40","111.58","113.32","110.32","64.46M","1.19%"
"05/01/2024","111.08","106.22","112.49","105.80","46.77M","4.58%"
"04/01/2024","106.22","110.24","110.45","104.29","61.15M","-3.55%"
"03/01/2024","110.13","106.92","110.37","106.24","50.51M","3.26%"
"02/01/2024","106.65","102.39","107.11","102.24","82.37M","4.51%"
"01/01/2024","102.05","101.01","103.61","99.26","129.99M","0.28%"
"12/01/2023","101.77","97.83","102.28","97.66","82.81M","3.83%"
"11/01/2023","98.02","90.23","98.32","90.09","85.31M","8.89%"
"10/01/2023","90.02","92.15","94.12","88.33","76.69M","-2.54%"
"09/01/2023","92.37","97.24","97.39","91.32","40.95M","-4.28%"
"08/01/2023","96.50","98.83","99.01","93.02","64.18M","-2.91%"
"07/01/2023","99.39","96.02","99.53","93.98","62.65M","3.60%"
"06/01/2023","95.94","91.77","96.81","91.64","71.30M","4.72%"
"05/01/2023","91.62","92.56","93.38","90.50","60.46M","-1.05%"
"04/01/2023","92.59","91.13","92.92","90.40","57.18M","1.57%"
"03/01/2023","91.16","88.53","91.20","84.94","81.61M","3.33%"
"02/01/2023","88.22","91.02","93.24","87.66","66.46M","-3.32%"
"01/01/2023","91.25","85.51","91.81","84.39","132.52M","7.50%"
"12/01/2022","84.88","90.09","90.37","83.59","120.65M","-5.34%"
"11/01/2022","89.67","83.98","89.67","79.85","122.60M","8.34%"
"10/01/2022","82.77","78.66","83.44","75.71","104.61M","6.35%"
"09/01/2022","77.83","85.21","88.99","77.77","115.64M","-9.39%"
"08/01/2022","85.90","89.23","93.06","85.90","91.73M","-4.36%"
"07/01/2022","89.82","83.50","89.89","81.47","64.76M","7.07%"
"06/01/2022","83.89","92.78","93.31","81.26","79.35M","-8.95%"
"05/01/2022","92.14","91.69","94.96","85.52","77.29M","0.45%"
"04/01/2022","91.73","100.49","101.40","91.65","74.05M","-8.07%"
"03/01/2022","99.78","97.60","102.03","91.53","93.60M","1.94%"
"02/01/2022","97.88","101.19","103.27","92.84","98.56M","-3.06%"
"01/01/2022","100.97","106.12","106.78","95.60","167.38M","-4.55%"
"12/01/2021","105.78","104.15","106.54","101.03","112.68M","2.82%"
"11/01/2021","102.88","105.64","107.46","102.50","63.44M","-2.31%"
"10/01/2021","105.31","100.39","105.83","98.94","70.00M","5.39%"
"09/01/2021","99.92","104.70","105.41","99.82","61.66M","-4.23%"
"08/01/2021","104.33","102.70","104.50","100.84","71.46M","2.17%"
"07/01/2021","102.11","101.28","102.90","98.82","71.31M","0.91%"
"06/01/2021","101.19","101.57","101.69","99.03","53.03M","0.54%"
"05/01/2021","100.65","99.83","100.93","96.53","66.48M","1.47%"
"04/01/2021","99.19","95.85","100.49","95.84","57.50M","4.25%"
"03/01/2021","95.15","93.84","96.23","90.75","85.01M","2.85%"
"02/01/2021","92.51","91.49","96.93","91.02","70.17M","2.29%"
"01/01/2021","90.44","91.60","94.38","89.44","89.18M","-0.31%"
"12/01/2020","90.72","88.40","90.99","87.94","53.45M","3.88%"
"11/01/2020","87.33","78.94","88.46","78.36","96.18M","11.76%"
"10/01/2020","78.14","80.53","84.03","77.34","86.70M","-2.23%"
"09/01/2020","79.92","82.61","84.12","76.94","73.30M","-2.95%"
"08/01/2020","82.35","78.19","82.88","78.07","64.43M","6.03%"
"07/01/2020","77.67","73.95","78.65","73.89","63.39M","5.36%"
"06/01/2020","73.72","72.46","77.38","70.87","63.50M","2.08%"
"05/01/2020","72.22","67.56","72.88","65.77","97.16M","5.09%"
"04/01/2020","68.72","60.12","70.07","59.30","116.75M","9.83%"
"03/01/2020","62.57","72.62","75.93","53.31","178.65M","-13.41%"
"02/01/2020","72.26","78.46","81.75","70.17","74.24M","-7.49%"
"01/01/2020","78.11","79.77","81.39","77.90","50.37M","-1.44%"
"12/01/2019","79.25","77.64","79.92","76.02","43.74M","2.21%"
"11/01/2019","77.54","76.21","78.06","76.19","35.18M","2.34%"
"10/01/2019","75.77","73.82","76.04","71.12","55.92M","2.74%"
"09/01/2019","73.75","71.65","74.92","71.44","45.21M","2.25%"
"08/01/2019","72.13","73.73","74.47","69.82","89.00M","-2.21%"
"07/01/2019","73.76","74.50","75.06","73.13","45.09M","0.07%"
"06/01/2019","73.71","70.18","74.07","69.89","56.10M","5.16%"
"05/01/2019","70.09","74.70","74.87","69.86","62.23M","-6.07%"
"04/01/2019","74.62","72.76","74.68","72.68","37.67M","3.42%"
"03/01/2019","72.15","71.49","72.89","69.50","66.80M","1.58%"
"02/01/2019","71.03","69.31","71.75","68.43","57.25M","2.47%"
"01/01/2019","69.32","63.28","69.42","62.91","115.21M","8.04%"
"12/01/2018","64.16","70.98","71.03","61.01","175.12M","-8.12%"
"11/01/2018","69.83","69.15","71.37","67.03","79.32M","1.59%"
"10/01/2018","68.74","74.63","74.75","66.23","106.43M","-7.43%"
"09/01/2018","74.26","73.40","74.95","72.18","37.39M","0.61%"
"08/01/2018","73.81","73.27","74.70","71.22","44.94M","0.70%"
"07/01/2018","73.30","70.34","73.94","70.34","44.86M","3.07%"
"06/01/2018","71.12","72.82","74.35","70.21","50.03M","-1.67%"
"05/01/2018","72.33","71.89","74.10","70.74","33.78M","0.47%"
"04/01/2018","71.99","71.39","73.53","69.67","39.86M","0.40%"
"03/01/2018","71.70","72.71","74.76","70.05","47.44M","-1.50%"
"02/01/2018","72.79","75.96","76.41","68.52","89.18M","-4.48%"
"01/01/2018","76.20","72.51","77.54","72.33","40.63M","5.70%"
"12/01/2017","72.09","71.61","72.76","70.79","49.46M","0.52%"
"11/01/2017","71.72","70.63","71.89","69.53","29.03M","2.02%"

create_table "import_csvs", force: :cascade do |t|
    t.date "date"
    t.decimal "price", precision: 10, scale: 2
    t.decimal "open", precision: 10, scale: 2
    t.decimal "high", precision: 10, scale: 2
    t.decimal "low", precision: 10, scale: 2
    t.integer "volume"
    t.decimal "change_percentage", precision: 5, scale: 2
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

binding.pryで原因を見ようと思い、下記の記事を参考にしてrubyコマンドを実行したところ、反応がありませんでした。docker環境でbinding.pryや他のシステムが反映できていないかもしれないです
https://qiita.com/st5e_jp/items/7c9d266480079910de5c

yfinance % docker ps                                  
CONTAINER ID   IMAGE                                  COMMAND                   CREATED          STATUS          PORTS                                         NAMES
bdc3f213942f   yfinance-web                           "bash -c 'rm -f tmp/…"   11 minutes ago   Up 15 seconds   0.0.0.0:3000->3000/tcp                        yfinance-web-1
d3cf9bd4e91a   seleniarm/standalone-chromium:latest   "/opt/bin/entry_poin…"   8 days ago       Up 15 seconds   4444/tcp, 5900/tcp, 0.0.0.0:4500->4500/tcp    yfinance-chrome-1
9d058c173246   mysql:5.7                              "docker-entrypoint.s…"   8 days ago       Up 11 minutes   3306/tcp, 33060/tcp, 0.0.0.0:3500->3499/tcp   yfinance-db-1
yanas@MacBook-Air-3 yfinance % docker attach yfinance-web-1               

補足

下記ページをもとに作成しています。
https://qiita.com/fukaken2000/items/693bcfa2fbcba9db033e

Answer 1

今回のエラーとは関係ないですが、そもそもrubyコマンドにスクリプトのパスを渡してもRailsの機能は使えません。
モデルなどRailsの機能を使ったスクリプトをコマンドラインで実行したい場合は、rails runnerを使います。
プロジェクトのルートディレクトリへ移動して、bin/rails runner スクリプトのパスで実行します。
import_data.rbがプロジェクトのルートディレクトリにあるなら、bin/rails runner import_data.rbとなります。

質問に記載されたCSVデータをコピペで手元で試したところ問題なくCSV.foreachで読み込みができました。
そのためデータそものではなく、CSVファイルのエンコーディングが怪しいのではないかと思います。BOMなしのUTF-8で保存されていますでしょうか。

Comments

[yy0225]

ありがとうございます。
bin/rails runner import_data.rbを実行し、コマンドを実行することができました（「BOMなしのUTF-8で保存」という部分から「, encoding: 'UTF-8'」を追加しています）。一方で、登録されたデータを見たところ、date のところがnilになっており取得できていない状況です

```
root@88c564fe167b:/yfinance# bin/rails runner import_data.rb
CSV data imported successfully!
```

```
require 'csv'

# 1 … CSVファイルのパスを設定する
csv_file_path = 'db/csv/ACWI_ETF_Stock_Price_History.csv' # ルートディレクトリにスクリプトファイルを、db/csvディレクトリにcsvファイルを置いた場合の記述

# 2 … CSVファイルからデータを読み込み、Hogesテーブルに登録
CSV.foreach(csv_file_path, headers: true, liberal_parsing: true, encoding: 'UTF-8') do |row|

ImportCsv.create!(
date: (row['Date']),
price: row['Price'].to_f,
open: row['Open'].to_f,
high: row['High'].to_f,
low: row['Low'].to_f,
volume: row['Vol.'].gsub('M', '').to_i * 1000000,
change_percentage: row['Change %'].gsub('%', '').to_f / 100
)
end

# 3 … ファイル実行後に、処理が成功したという文章を表示させる。
# ここはお好みで。記述しなくても動作はする。
puts 'CSV data imported successfully!'
```

```
root@88c564fe167b:/yfinance# bundle exec rails c
Loading development environment (Rails 7.0.8.4)
[1] pry(main)> ImportCsv.all
=> ImportCsv Load (3.4ms) SELECT "import_csvs".* FROM "import_csvs"
[#<ImportCsv:0x0000ffff7c9570a0
id: 1,
date: nil,
price: 0.11664e3,
open: 0.11398e3,
high: 0.117e3,
low: 0.10553e3,
volume: 56000000,
change_percentage: 0.2e-1,
created_at: Sat, 14 Sep 2024 03:37:50.195994000 UTC +00:00,
updated_at: Sat, 14 Sep 2024 03:37:50.195994000 UTC +00:00>,
#<ImportCsv:0x0000ffff7c91d0f8
id: 2,
date: nil,
price: 0.11413e3,
open: 0.11281e3,
high: 0.11681e3,
low: 0.11111e3,
volume: 59000000,
change_percentage: 0.2e-1,
created_at: Sat, 14 Sep 2024 03:37:50.205924000 UTC +00:00,
updated_at: Sat, 14 Sep 2024 03:37:50.205924000 UTC +00:00>,
#<ImportCsv:0x0000ffff7c91d030
```

[mingos]

ぱっと見て怪しいのはこれですかね。
date: (row['Date']),
他の項目ではカッコをつけていないので、date: row['Date'], とするべきかと思います。
たぶん、Date.parseを消したときにカッコを取り忘れたのではないでしょうか。

[yy0225]

ありがとうございます。
すみません、おっしゃる通り取り忘れがありました。
date: row['Date'],　に修正しましたが、dateはnilとして出力される状況です。
```
root@88c564fe167b:/yfinance# bin/rails runner import_data.rb
CSV data imported successfully!
root@88c564fe167b:/yfinance# bundle exec rails c
Loading development environment (Rails 7.0.8.4)
[1] pry(main)> ImportCsv.all
=> ImportCsv Load (1.8ms) SELECT "import_csvs".* FROM "import_csvs"
[#<ImportCsv:0x0000ffff9d085b18
id: 2168,
date: nil,
price: 0.11664e3,
open: 0.11398e3,
high: 0.117e3,
low: 0.10553e3,
volume: 56000000,
change_percentage: 0.2e-1,
created_at: Sun, 15 Sep 2024 13:37:11.596372000 UTC +00:00,
updated_at: Sun, 15 Sep 2024 13:37:11.596372000 UTC +00:00>,
#<ImportCsv:0x0000ffff9d04bb48
id: 2169,
date: nil,
price: 0.11413e3,
open: 0.11281e3,
high: 0.11681e3,
low: 0.11111e3,
volume: 59000000,
change_percentage: 0.2e-1,
created_at: Sun, 15 Sep 2024 13:37:11.600016000 UTC +00:00,
updated_at: Sun, 15 Sep 2024 13:37:11.600016000 UTC +00:00>,
#<ImportCsv:0x0000ffff9d04ba80
id: 2170,
date: nil,
price: 0.1124e3,
open: 0.11158e3,
high: 0.11332e3,
low: 0.11032e3,
volume: 64000000,
change_percentage: 0.1e-1,
created_at: Sun, 15 Sep 2024 13:37:11.603263000 UTC +00:00,
updated_at: Sun, 15 Sep 2024 13:37:11.603263000 UTC +00:00>,
#<ImportCsv:0x0000ffff9d04b9b8
```

Answer 2

コメント全部は読んでないですが（code類は質問編集してそちらに載せて欲し）
import_data.rb って単独で動くのですよね？ railsの中にあるわけではない。
すると rails c で class ImportCsv が有ったというのは import_data.rb 実行時にあることの証明にはなりません。
「CSVのデータをDatabaseに入れる」というrubyプログラムを動かすには rubyとrdbをつなげる部分を自力で作るか、探してくるかしないと入ってくれません。ActiveRecord を取り込むとか。
まだ初心者ですか？
でしたら Rails の Web 画面から Rais アプリ内に作った import_data.rb を実行するようにするのが一番楽か。

Answer 3

/usr/local/lib/ruby/3.1.0/csv/parser.rb で Illegal quoting in line 1
のエラーがでています。ライブラリの奥なのでおそらく与えるデータにおかしなのがあってそれが問題を起こしているのだと思います。
おかしなデータなのか、おかしなカラムなのか。

脊椎反射的には date: Date.parse(row['Date']), が怪しいです。
ここ 変換なしに date: row['Date'] で無いのはなぜ？

Comments

[yy0225]

すみません
date: Date.parse(row['Date'])について、CSVデータが「月、日、年」の順番でそのままではデータベースに入れられないのではと思い記載しました。date: row['Date'] に修正したところ、ImportCsvクラスが未定義というエラーが出ました。ただ、rails c上で確認したところ、ImportCsvクラスはありそうでした

```
root@88c564fe167b:/yfinance# ruby import_data.rb
import_data.rb:10:in `block in <main>': uninitialized constant ImportCsv (NameError)

ImportCsv.create!(
^^^^^^^^^
from /usr/local/lib/ruby/3.1.0/csv/parser.rb:1275:in `emit_row'
from /usr/local/lib/ruby/3.1.0/csv/parser.rb:1045:in `parse_quotable_robust'
from /usr/local/lib/ruby/3.1.0/csv/parser.rb:404:in `parse'
from /usr/local/lib/ruby/3.1.0/csv.rb:2554:in `each'
from /usr/local/lib/ruby/3.1.0/csv.rb:2554:in `each'
from /usr/local/lib/ruby/3.1.0/csv.rb:1334:in `block in foreach'
from /usr/local/lib/ruby/3.1.0/csv.rb:1610:in `open'
from /usr/local/lib/ruby/3.1.0/csv.rb:1333:in `foreach'
from import_data.rb:8:in `<main>'
```

```
require 'csv'

# 1 … CSVファイルのパスを設定する
csv_file_path = 'db/csv/ACWI_ETF_Stock_Price_History.csv' # ルートディレクトリにスクリプトファイルを、db/csvディレクトリにcsvファイルを置いた場合の記述

# 2 … CSVファイルからデータを読み込み、Hogesテーブルに登録
CSV.foreach(csv_file_path, headers: true, liberal_parsing: true) do |row|

ImportCsv.create!(
date: (row['Date']),
price: row['Price'].to_f,
open: row['Open'].to_f,
high: row['High'].to_f,
low: row['Low'].to_f,
volume: row['Vol.'].gsub('M', '').to_i * 1000000,
change_percentage: row['Change %'].gsub('%', '').to_f / 100
)
end

# 3 … ファイル実行後に、処理が成功したという文章を表示させる。
# ここはお好みで。記述しなくても動作はする。
puts 'CSV data imported successfully!'
```

```
@MacBook-Air-3 yfinance % rails c
Loading development environment (Rails 7.0.8.4)
[1] pry(main)> ImportCsv
=> ImportCsv (call 'ImportCsv.connection' to establish a connection)
[2] pry(main)>
```