skimemo

_ ExcelVBAで出力したUTF-8はBOM付きだからPHPで読む際には除去が必要

LaravelっちゅうかPHPですが、掲題のような事で数十分浪費したのでメモです。

_ 概要

Excelでテストデータを作成し自動でSQL文を出力するようにしたとしましょう。
そのSQL文をLaravelで読み込んで一行ずつ実行するという寸法です。

_ Excel側

そのまま書き出すとSJISになってしまうので、UTF-8で書き出します。
ググるとすぐ出てきますが、こんな感じです。追記型です。

  1
  2
  3
  4
  5
  6
  7
  8
  9

With CreateObject("ADODB.Stream")
    .Charset = "UTF-8"
    .Open
    .LoadFromFile [ファイル名]
    .Position = .Size
    .WriteText str, 1
    .Savetofile [ファイル名], 2
    .Close
End With 

_ Laravel側

これを一行ずつ読み込んで実行します。大体こんな感じ。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20

$filename = [ファイル名];
$sqls = file($filename);
if($sqls!==false){
    try{
        DB::beginTransaction();
        // SQL文を順に実行
        foreach( $sqls as $sql ){
            if( !empty($sql) ){
                DB::statement($sql);
            }
        }
        DB::commit();
    } catch(\Exception $e){
        // 1つでもエラーが起きたらrollback
        DB::rollBack();
        logger()->error(__METHOD__.':'.$e->getMessage());
    }
} else {
    // エラー: ファイルが無い
} 

と・こ・ろ・が、これは1行目のSQL文からエラーになります。

App\Http\Controllers\****Controller::setDemoData:SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete from company' at line 1 (SQL: delete from company)

試しに生のSQL文をそのままDB::statement('delete from company');のように書いて実行すると正常に完了します。
何が違うのかと、エラーログからコピペして比べてみると、PHPStorm上で何故か表現が違う・・・。

どうやらエラーになる方はSQL文の先頭に1文字何かが入っているようです。
文字コードを確認すべくサクラエディタで開くとエンコードの欄に「UTF-8 BOM付」の文字が・・・。

そう、ExcelVBAでUTF-8で書き出したファイルはBOM付きになるのです。(メモ帳とかでもなるらしいです)

というわけで、BOMを除去してあげれば良いことが分かりました。

_ BOM除去

検索すると以下の方法が見つかりました。

$data = preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $string)

しかし、これだとその他の日本語まで全て除去してしまうようでした。 最終的にはこちらを採用。

$bom = pack('H*', 'EFBBBF');
$data = preg_replace("/^$bom/", '', $string);

http://blog.higty.xyz/post/php-bom/
これで無事ExcelでUTF-8出力したSQL文をPHPで実行することができました。
おしまい。

ここで言うBOMは部品表の事だそうです。なんか昔そんな名前の雑誌があったような・・・(笑) BOMで会社の利益体質を改善しよう！ 中小企業だからこそできる [ 谷口潤 ] 価格：1728円（税込、送料無料) (2019/2/17時点)