文字コードsjisの変換について

Question

###前提・実現したいこと

全体の流れ

DBからCSVファイルを出力 → ユーザがそのCSVファイルをエクセルで編集 → DBにCSVファイルを入力

の ”DBにCSVファイルを入力” 部分の質問になります。
このプログラムはartisanコマンドで呼び出され実行されます。
csvを読み込んで配列に格納し、その配列をもとにDBのデータを更新するプログラムです。

###発生している問題・エラーメッセージ

下記のエラーが出てしまいました。

SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'phone_number' at row 16876

###該当のソースコード

該当箇所をvar_dumpで見ると下記のようになっており、

array(11) {
　[0]=>
　string(13) "001r357820094"
　[1]=>
　string(3) "001"
　[2]=>
　string(57) "あいう機構",00-1234-5678""

csvの区切り文字がうまく読まれません。本来は[3]に下記のように入るはずです。

array(10) {
　[0]=>
  string(13) "001r357820094"
　[1]=>
  string(3) "001"
　[2]=>
  string(57) "あいう機構"
　[3]=>
  string(12) "00-1234-5678"

初心者ながら自力で調べたところ0x5c問題というもので、構の文字の2バイト目が￥になり,がエスケープされているということに気が付きました。

しかし読み込まれるcsvの文字コードはsjis(エクセルの保存関係で)であり、それをうまくデータベースに戻さなければならず、どう解決すればいいかわかりません。

またsjisのファイルをsjis-winとしてmb_convert_variablesをかけていいのでしょうか？そうすることでハシゴの高（髙）などの問題は解決できました。しかしそれがやっていいことなのかはわかっていません。

csvの読み込みは、下記のようにSplFileObjectを使い、それをlist関数を使い変数に詰めています。
(変数名$aなどは簡略化して書いています)

public function handle()
{   
    // CSVファイル読み込み
    $filePath = './aaa.csv';
    $file = new SplFileObject($filePath);
    $file->setFlags(SplFileObject::READ_CSV);

    // ファイル内のデータをループ
    foreach ($file as $row) {    
        // 文字コード変換
        mb_convert_variables('utf8', 'sjis-win', $row);
            
        // list関数を使い変数にデータを格納
        list(
            $a,
            $b,
            $c,
            $d,
            $e,
            $f,
            $g,
            $h,
            $i,
            $j,
            $k
        ) = $row;

その後、DBは下記のようにクエリービルダーを使い必要項目だけアップデートしています。
DBはutf8-unicode-ciでMySQLを使っています。

// CSVから取得したデータをDBに戻す
DB::table('test_database')->where('a', $a)->update([                    
    'b' => $b,
    'c' => $c,
    'updated_at' => date("Y-m-d H:i:s")
]);  

###補足情報(言語/FW/ツール等のバージョンなど)

開発環境：Vagrant + VirtualBox / Homestead
言語：php-7.1.14 (NTS)
フレームワーク：Laravel5.5 (LTS)
サーバー：nginx
DB：MySQL

Answer 1

　　　　　　 // (略)
            if(isset($row[2])) {
                $company_name = $row[2];
                # 電話番号検索
                $flg = false;
                # 異常値から電話番号を抽出　["あいう機構",03-5555-6666""]
                if(preg_match('/([0-9]{2,4}-[0-9]{2,4}-[0-9]{3,4})/', $company_name, $match)) {
                    $phone_number = $match[0];
                    # 異常値から電話番号の削除 ["あいう機構",""]
                    $company_name = str_replace($phone_number, '', $company_name);
                    # 異常値から\,\を削除 ["あいう機構"]
                    $company_name = str_replace("\",\"", '', $company_name);
                    $flg = true;
                }
            };
            if(isset($row[3])) {
                if($flg) {
                    $mail_address = $row[3];
                } else {
                    $phone_number = $row[3];
                }
            };
            // (略)

ごり押しかもしれませんが、list関数を止め上記のようにして解決しました。

Answer 2

mb_convert_variables()やmb_convert_encoding()を使ってSJIS-winからUTF-8に変換しないと、
お気づきのように正しく処理できないと思われます。

あと、Excelで加工するとのことなので、
例えばCSVでなくxlsやxlsxをphpから読み書きする方法もありますね、
PHPExcel(保守されてないので非推奨)とかPHPSpreadsheetとか。
PHPExcelの後継っぽいPhpSpreadsheetでExcelファイルを読み書きするサンプルコード - ariarijp’s blog
PhpSpreadsheetを試してみる(テンプレート読み込み・セルの結合・スタイル設定・CSV出力) - Qiita

Comments

[T.FUJII]

m6u様 ありがとうございます。
確かにExcelファイルでやる方がいい気がします。
参考にさせていただきます。
今今自分なりに解決方法はどれも無理やりな感じです。
①DBにデータをインサートする時点で0x5c文字はエスケープする
②ユーザにはエクセル以外のcsvエディタでutf8として編集してもらう
③エクセルでcsvを編集する場合は下記手順でutf8にしてもらう
https://goo.gl/NKX8sV