PHP開発日記：CSVの文字コードとの闘い
昨晩から日本全国の住所が入ったCSVファイルと格闘中。
目下の問題点は「文字化け」。昨日以下のコードで試しに画面に出してみたが、見事に文字化けしていた。
//csv読み込み処理
<?php
$file=@fopen('./csv/KEN_ALL.CSV','r') or die('ファイルを開けませんでした');
//var_dump($file);
flock($file,LOCK_SH);
//とりあえず画面に出してみよう
while (!feof($file)) {
$line=fgets($file);
echo '<p>'.$line.'</p>';
}
flock($file,LOCK_UN);
fclose($file);
何か方法はないかと調べていたところ、Githubに以下の書き込みを発見。
GithubのURLを貼り付けるとコードがそのまま見れる…！！今回はこのコードをお借りする。
試しにエンコーディング処理まで書いてvar_dump()。
<?php
setlocale(LC_ALL, 'ja_JP.UTF-8');
require_once('config.php');
require_once('./helper/db_helper.php');
require_once('./helper/extra_helper.php');$csv = array();
$file = './csv/KEN_ALL.CSV';
//エンコーディング処理
$data = file_get_contents($file); // string型に変換
$data = mb_convert_encoding($data, 'UTF-8', 'sjis-win'); //この関数を通すことで文字化けが解消される
var_dump($data);
見事、文字化けせずに出力。これをCSVの1行に沿った形で改行して表示したい。
<?php
setlocale(LC_ALL, 'ja_JP.UTF-8');
require_once('config.php');
require_once('./helper/db_helper.php');
require_once('./helper/extra_helper.php');$csv = array();
$file = './csv/KEN_ALL.CSV';
//エンコーディング処理
$data = file_get_contents($file); // string型に変換
$data = mb_convert_encoding($data, 'UTF-8', 'sjis-win'); //この関数を通すことで文字化けが解消される
//初期化・一時ファイル作成
$temp = tmpfile();
$csv = array();
$count=0;
fwrite($temp, $data); //$tempファイルに、$dataの内容を書き込む。
rewind($temp); //ファイルポインタを先頭の位置に戻す
while *1 !== FALSE) {
$data = implode(",", $data); //一行づつ、配列を","で連結して、文字列として返す
$csv[] = htmlentities($data);
echo $csv[$count].'<br>';
$count+=1;
}
これでやっと以下の形で表示された。
次は二次元配列として、「$ken[0][8]='札幌市中央区'」のような形でカンマ区切りのフィールドごとに配列に入れたい。
<?php
setlocale(LC_ALL, 'ja_JP.UTF-8');
require_once('config.php');
require_once('./helper/db_helper.php');
require_once('./helper/extra_helper.php');$csv = array();
$kendata=array();
$file = './csv/KEN_ALL.CSV';
//エンコーディング処理
$data = file_get_contents($file); // string型に変換
$data = mb_convert_encoding($data, 'UTF-8', 'sjis-win'); //この関数を通すことで文字化けが解消される
//初期化・一時ファイル作成
$temp = tmpfile();
$csv = array();
$count=0;
fwrite($temp, $data); //$tempファイルに、$dataの内容を書き込む。
rewind($temp); //ファイルポインタを先頭の位置に戻す
while *2 !== FALSE) {
$data = implode(",", $data); //一行づつ、配列を","で連結して、文字列として返す
$csv= explode(",", $data); //カンマ区切りで配列の要素に分解
$kendata[$count]=$csv;
echo $kendata[$count][6].$kendata[$count][7].$kendata[$count][8].'<br>';
$count+=1;
if($count>10){break;
}
}
echo "<br>end";
1時間くらい粘った…。
これで二次元配列として住所を取り込むことができるようになった。
今回の二次元配列の構成は以下の通り。
$kendata[$count]→csvの1行
$kendata[$count][2]→郵便番号
$kendata[$count][3]→都道府県(半角カナ)
$kendata[$count][4]→市区町村(半角カナ)
$kendata[$count][5]→町域(半角カナ)
$kendata[$count][6]→都道府県(漢字)
$kendata[$count][7]→市区町村(漢字)
$kendata[$count][8]→町域(漢字)
ちなみに「implodeしないでexplodeだけでいいのでは…？」と思ったが、implodeをコメントアウトするとうまく動かなかった。
これからさらにMySQLへの取り込みを行う。
db_helper.phpに以下の関数を作成。
function insert_ken($dbh,$addno,$add1,$add1kana,$add2,$add2kana,$add3,$add3kana){
//sql作成
$sql ="insert into address";
$sql+="(addno,add1,add1kana,add2,add2kana,add3,add3kana) values ";
$sql+="(:addno,:add1,:add1kana,:add2,:add2kana,:add3,:add3kana)";
//プリペア、引数置換
$stmt=$dbh->prepare($sql);
$stmt->bindvalue(':addno',$addno,PDO::PARAM_STR);
$stmt->bindvalue(':add1',$add1,PDO::PARAM_STR);
$stmt->bindvalue(':add1kana',$add1kana,PDO::PARAM_STR);
$stmt->bindvalue(':add2',$add2,PDO::PARAM_STR);
$stmt->bindvalue(':add2kana',$add2kana,PDO::PARAM_STR);
$stmt->bindvalue(':add3',$add3,PDO::PARAM_STR);
$stmt->bindvalue(':add3kana',$add3kana,PDO::PARAM_STR);
if ($stmt->execute()) {
return true;
} else {
return false;
}
}
作成したはいいが、引数が長い…。配列の形で渡せないかどうかをテストしてみる。
db_hepler.phpに以下の関数を作り、csvget.phpから呼び出してみる。
function test($array){
var_dump($array);
}
//csvget.php
<?php
//～中略
test($kendata[10]);
☆結果
array(15) { [0]=> string(5) "01101" [1]=> string(5) "064 " [2]=> string(7) "0640822" [3]=> string(21) "ﾎｯｶｲﾄﾞｳ" [4]=> string(36) "ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ" [5]=> string(44) "ｷﾀ2ｼﾞｮｳﾆｼ(20-28ﾁｮｳﾒ)" [6]=> string(9) "北海道" [7]=> string(18) "札幌市中央区" [8]=> string(39) "北二条西（２０～２８丁目）" [9]=> string(1) "1" [10]=> string(1) "0" [11]=> string(1) "1" [12]=> string(1) "0" [13]=> string(1) "0" [14]=> string(1) "0" }
二次元配列が普通の一次元配列になっている。ということで、insert_ken()関数を以下のように引数を配列にする形で書き換え。
function insert_ken($dbh,$kendata){
//SQL作成
$sql="insert into address (addno,add1,add1kana,add2,add2kana,add3,add3kana)
values (:addno,:add1,:add1kana,:add2,:add2kana,:add3,:add3kana)";
//プリペア、引数置換
$stmt=$dbh->prepare($sql);
$stmt->bindValue(':addno',$array[2],PDO::PARAM_STR);
$stmt->bindValue(':add1',$array[3],PDO::PARAM_STR);
$stmt->bindValue(':add1kana',$array[4],PDO::PARAM_STR);
$stmt->bindValue(':add2',$array[5],PDO::PARAM_STR);
$stmt->bindValue(':add2kana',$array[6],PDO::PARAM_STR);
$stmt->bindValue(':add3',$array[7],PDO::PARAM_STR);
$stmt->bindValue(':add3kana',$array[8],PDO::PARAM_STR);
if($stmt->execute()){
return true;
} else {
return false;
}
}
作成したはいいが、二次元配列でforeachすると二次元配列の方を取得してしまう…。とりあえず今回は引数の長いバージョンで処理を行うことにする。この方がbindValueの処理も確実にできそうだし。
csvget.phpで配列に都道府県名を渡す処理の下にMySQLへのInsert処理を加える。
//MySQL取り込み処理
$count=0;
foreach ($kendata as $row) {
if (insert_ken($dbh,$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8])===false) {
die('Error!('.$row[6].$row[7].$row[8].')');
}
$count+=1;
}echo "OK!";
いろいろ手直しをして、csvget.phpは以下のように生まれ変わった。
<?php
setlocale(LC_ALL, 'ja_JP.UTF-8');
ini_set('memory_limit', '512M');
require_once('config.php');
require_once('./helper/db_helper.php');
require_once('./helper/extra_helper.php');//CSV取り込み処理
$csv = array();
$kendata=array();
$file = './csv/KEN_ALL.CSV';
//エンコーディング処理
$data = file_get_contents($file); // string型に変換
$data = mb_convert_encoding($data, 'UTF-8', 'sjis-win'); //この関数を通すことで文字化けが解消される
//初期化・一時ファイル作成
$temp = tmpfile();
$csv = array();
$count=0;
fwrite($temp, $data); //$tempファイルに、$dataの内容を書き込む。
rewind($temp); //ファイルポインタを先頭の位置に戻す
while *3 !== FALSE) {
$data = implode(",", $data); //一行づつ、配列を","で連結して、文字列として返す
$csv= explode(",", $data); //一行ずつ、カンマ区切りしたフィールドを配列の要素として扱う
$kendata[$count]=$csv;
$count+=1;
}
echo $count.'件のデータを配列に格納完了!!<br>';//MySQL取り込み処理
$count=0;
$dbh=get_db_connect();
foreach ($kendata as $row) {
if ($row[8]!=='以下に掲載がない場合') {
if (insert_ken($dbh,$row[2],$row[6],$row[3],$row[7],$row[4],$row[8],$row[5])===false) {
die('Error!('.$row[6].$row[7].$row[8].')');
}
$count+=1;
}
}echo $count.'件のデータをMySQLに取り込み完了!!<br>';
途中でメモリが足りなくなる事態が発生したので、ini_set('memory_limit', '512M');で一時的にphpが使えるメモリを増やした。
この処理を走らせることで、KEN_ALL.CSVから124228件のデータを配列に格納し、122354件のデータをMySQLに取り込むことができた。ちなみに、どうも郵便番号に重複があるらしく、郵便番号を主キーとしていると途中でエラーを出した。これについてはMySQLで「alter table address drop primary key;」を実行、主キーを削除して対応した。
今日はここまで。20時から風呂を挟んで3時間くらいやってました。明日は検索部分を作る予定。
