CSVダウンロード

データベースのデータを引っこ抜いて、ユーザにダウンロードして使ってもらう。というシュチュエーションはよくあると思うのだが、ベスト・プラクティスがない。

要件

1.実行環境はphp、DBの文字コードはUTF8

2.windows,macのエクセルできれいに開ける。

UTFの場合、windowsエクセルはUTF8BOM。macはUTF-16LE。→ しょうがないのでSJISで

タブ区切りは、そのままでは、きれいに表示されない。→ カンマ区切りで。

3.データ最大5万件

オンラインで提供するので、ユーザの待ちは、まあ1分が限界。

 

でphpで色々試したのが

fputcsvが一番早いが、改行コードがLF。CRLFにするには、出力ストリームの調整が必要でめんどくさいしわかりずらい。

実測15sec/10000件くらい。

1行ずつデータ加工して、fwrite 実測20sec/10000件

Webで調査、そして実際にコードを書いて試すこと数時間。もうこれはphp(PDO)を前提にしている限り、性能向上は無理なんじゃないかと考えて、方向転換。

バッチ処理にして、処理完了後ユーザがダウンロードできるようにするのが一般的だが、今その仕組みはないので、そうとうな作り込みになる。。。

サーバにmysqlクライアントは入っているのでそれを使えば??

1.とりあえず、それっぽいファイルを吐く。

$file=CSVファイル名。

$command = “パスmysql -u ユーザ名 -pパスワード -h ホスト名 –default-character-set=sjis DB名 -e \”実行したいSQL\” > $file”;
$ret = exec ($command);

2.CSVに加工

$str = file_get_contents(file);
$str = str_replace(‘”‘,'””‘,$str);   //データ内のダブルコーテーションをエスケープ
$str = str_replace(“\t”,'”,”‘,$str); //タブを”,”に置換
$str = preg_replace(“/^|$/m”,'”‘,$str);//行頭行末を”に置換
$str = str_replace(“\n”, “\r\n”, $str);//LFをCRLFに テキストで開く人用。
$str = mb_convert_encoding($str,”SJIS”,”UTF-8″); //SJISに sqlでやってしまうように変更
file_put_contents(file, $str);

 

fputcsvで1分以上かかっていた処理が

この実装だと、10秒になった。

数値を含め、全てのデータをダブルコーテーションでくくるのが気になるけど。。。

あとはnullデータががNULLと表示されてしまうところと

データ内にタブがあると、1列ずつずれるところ。

タブについては、入力時にタブを入力されないようにすることで防げる。

NULLを含むカラムを出力する場合はstr_replaceで空文字に置き換えてしまえばいいとする。

 

 

エクセルでCSVが

UTF8、タブを区切り文字としてのデフォルトオープンを扱ってくれれば、ほとんどの変換処理はいらないのだが。

赤塚