Laravel：カンマの付いた数字を含むCSVデータをMySQLにConvertする方法

2021年1月8日 2021年1月8日

akinori

Laravel：カンマの付いた数字を含むCSVデータをMySQLにConvertする方法

納品したシステムでおかしな動きが見つかり修正しました。

最初はプログラム側ばかり見ていたので原因特定できず途方に暮れていたのですが、ふとCSVデータの方に目を向けたら「数字にカンマが入ってる」と気づき即修正です。

まずはCSVデータのおさらい

【 1 】ダブルクォーテーションで囲っているタイプ

"id","name","price","stock"
"1","hoge","700","30"
"2","fuga","1000","4000"
"3","hega","2,100","200"

【 2 】ダブルクォーテーションで囲っていないタイプ

id,name,price,stock
1,hoge,700,30
2,fuga,1000,4000
3,hoga,2100,200

【 3 】一部をダブルクォーテーションで囲うタイプ

id,name,price,stock
1,hoge,700,30
2,fuga,"1,000","4,000"
3,hoga,"2,100",200

今回のエラーを引き起こしていたデータは【3】のタイプでした。
Excelで作成したデータはカンマの有無で【2】か【3】になります。

LaravelからMySQLへ格納しようとした時、INTの規制に掛かってしまうのは【1】と【3】です。
ダブルクォーテーションで囲う＝文字列なので「数値指定してるのに何入れてんだ」となるわけです。

以前のデータは【1】のタイプだったため、この回避に (int)$row[3] と書いていいました。
これが【3】のタイプに変わりデータ不整合を引き起こしていたようです。

カンマ区切り数字のあるCSVデータの読込み方法～その1～

カンマ区切りの数字を含むCSVの処理は、Data総数が少ない場合 str_getcsv関数が快適です。

test.csv
---------
id,name,price,stock
1,hoge,700,30
2,fuga,"1,000","4,000"
3,hoga,"2,100",200
----------

$input = fopen("test.csv", "r");

  //1行ごと読む
  while($result= str_getcsv($input)){
    print_r($result);
  }
fclose($f);

Array
(
    [0] => 'id'
    [1] => 'name'
    [2] => 'price'
    [3] => 'stock'
)
Array
(
    [0] => '1'
    [1] => 'hoge'
    [2] => '700'
    [3] => '30'
)
Array
(
    [0] => '2'
    [1] => 'fuga'
    [2] => '1,000'
    [3] => '4,000'
)
Array
(
    [0] => '3'
    [1] => 'hoga'
    [2] => '2,100'
    [3] => '200'
)

こんな感じでしっかりとダブルクォーテーションに囲まれたカンマを文字列として認識してくれます。

str_getcsvで回避した場合の問題点

str_getcsvを利用する場合、fopenなどで読み込んで行ごとに処理すると言う流れになると思います。
その為、サイズの大きなCSVの場合などで3つの大きな課題を抱える事になります。

動作が遅い
メモリを食う
メモリ不足でエラーで完走できない

カンマ区切り数字のあるCSVデータの読込み方法～その2～

個人的には str_getcsv関数よりもこちらの方がお勧めです。

SplFileObject関数でCSVを読込む

// setlocaleを設定
setlocale(LC_ALL, 'ja_JP.UTF-8');
file_path = "../test.csv";

$file = new SplFileObject($file_path);
$file->setFlags(\SplFileObject::READ_CSV);

$row_count = 1;//初期カウント用意

#アップロードしたCSVファイルを読込
foreach ($file as $row){
  if ($row === [null]) continue;//最終行の処理(最終行が空っぽの場合の対策)
  if ($row_count > 1){//2行目からスタート
    mb_convert_variables('UTF-8', 'SJIS-WIN', $row);
    print_r($row);
  }
  $row_count++;  
}

Array
(
    [0] => '1'
    [1] => 'hoge'
    [2] => '700'
    [3] => '30'
)
Array
(
    [0] => '2'
    [1] => 'fuga'
    [2] => '1,000'
    [3] => '4,000'
)
Array
(
    [0] => '3'
    [1] => 'hoga'
    [2] => '2,100'
    [3] => '200'
)

foreach内で1行目の出力（print_r）を飛ばしてみました。
こんな感じでお馴染みのforeach内で標準的な配列として扱えるので好きなように弄れます。

配列の数が問題ないのに登録する値がオカシイ

今回発生していた問題『1,000 という数字をSQLに格納したら 1 が登録されていた』というものでした。

もともと【その2】の方法でスクリプトを組んでいたので、配列の数に問題はなく【 [2] => '1,000' 】として格納されています。ここに (int)$row[2] として数値化して格納していましたが、この段階でカンマの前だけが取り出されていたようです。

ちょっと実験してみました。

// setlocaleを設定
setlocale(LC_ALL, 'ja_JP.UTF-8');
file_path = "../test.csv";

$file = new SplFileObject($file_path);
$file->setFlags(\SplFileObject::READ_CSV);

$row_count = 1;//初期カウント用意

#アップロードしたCSVファイルを読込
foreach ($file as $row){
  if ($row === [null]) continue;//最終行の処理(最終行が空っぽの場合の対策)
  if ($row_count > 1){//2行目からスタート
    mb_convert_variables('UTF-8', 'SJIS-WIN', $row);
    print_r('1：'.$row[2]);
    print_r('2：'.(int)$row[2]);
    print_r('3：'.str_replace(",","",$row[2]));
    print_r('4：'.(int)str_replace(",","",$row[2]));
  }
  $row_count++;  
}

#2レコード目のみ記載

1：'1,000'
2：1
3：'1000'
4：1000

カンマが入った数値を(int)で数値変換してあげるとカンマの前だけ取り出されます。
これを処理するには「カンマを強制的に取ってあげればよい」ということで、Replaceしてみました。
その結果、SQLの格納も想定通りに動作。

if(!empty($row)){
  #新規レコード追加
  $item = new Temporaryfile();
  $item->name = $row[1];
  $item->price= $row[2];
  $item->stock = $row[3];
  $item->save();
}