CSVダウンロード

Posted on | by whitebox

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

要件

１．実行環境はphp、DBの文字コードはUTF8

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

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

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

３．データ最大5万件

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

 

でphpで色々試したのが

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

実測15sec/10000件くらい。

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

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

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

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

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

$file=CSVファイル名。

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

２．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で１分以上かかっていた処理が

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

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

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

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

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

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

 

 

エクセルでCSVが

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

赤塚