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

2021.01.08
php備忘録
ECシステム, Laravel, MySQL, PHP, エラー改善, スクリプト備忘録, 自作システム

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

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

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

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

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

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

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

"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

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

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

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

$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'
)

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++;  
}

// 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'
)

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++;  
}

// 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

#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();
}

if(!empty($row)){

#新規レコード追加

$item = new Temporaryfile();

$item->name = $row[1];

$item->price= $row[2];

$item->stock = $row[3];

$item->save();

}

まとめ

$row[2] = “1,234” の時、(int)str_replace(“,”,””,$row[2]));　とすると　1234 を取り出せる。

今回は最初の原因究明に時間を取られてしまいましたが、そこが終わればあっさり解決に至りました。

というかね、最近はVBScriptとPythonばかりでPHP触ってなかったので2時間ほど頭の感覚が戻らなかったです。
満遍なく解決早い人っていますけど、尊敬します。

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

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

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

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

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

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

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

まとめ

関連

Python：DataFrameから列を取り出して配列(リスト)にする方法 2021.01.07

Laravel：composer のパッケージアンインストールは軽率に実行してはダメでした 2021.01.14

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

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

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

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

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

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

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

まとめ

共有:

関連

Python：DataFrameから列を取り出して配列(リスト)にする方法 2021.01.07

Laravel：composer のパッケージアンインストールは軽率に実行してはダメでした 2021.01.14

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

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