#作業環境
Windows 10
Laravel : 6.18.35
Laravel/ui : 1.0
Laravel-mix : 5.0.1
Bootstrap : 4.0.0
XAMPP
Apache : 2.4.41
phpMyAdmin : 5.0.2
PHP : 7.4.3
Visual Studio Code
#はじめに
この記事はプログラミングをはじめたばかりの素人が、できたことをメモするのに利用しています。
内容には誤りがあるかもしれません。
#やりたい事
郵便番号のデータ(csv)を取り込み、自身のデータベースに郵便番号のテーブルを作成する。
その際に以下の要件を加える。
①csvはエクセルなどで一切加工しない
②コマンドで簡単に更新が行える
#この記事について
この記事は以下のサイトを参考に、うまくいかなかった所を試行錯誤して自分なりに修正したものです。
とりあえずやるならリンク先を試して見た方がいいかもしれません。
それでは、はじめていきます。
#郵便番号データ(csv)のダウンロード
郵便番号のデータは、日本郵便のものではなく、下記サイトのものを使用します。
なぜなら、加工したくないからです(要件①)!
サイトにアクセスし、郵便番号データ(加工済みバージョン)をダウンロードする。
#郵便番号データ(csv)の保存
ダウンロードした郵便番号データのzipファイルを解凍する。
Laravelのプロジェクトのstorage>app>内に、新しくcsvフォルダを作成し、解凍した郵便番号のデータを移動する。
#モデルとマイグレーションファイルの作成
ターミナルで以下を実行
php artisan make:model Models/PostalCode -m
-mのオプションを付けることで、マイグレーションファイルも一緒に作成されます。
####作成されたマイグレーションファイルの編集
database>migrations の中に作成されたマイグレーションファイルがあるので、
xxxx_xx_xx_xxxxxx_create_postal_codes_table.php を開き、以下のように書き加えます。
※xxxx部分はタイムスタンプです。
public function up()
{
// postal_codesテーブルがデータベース上に無ければ新規作成する
if (!Schema::hasTable('postal_codes')) {
Schema::create('postal_codes', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('first_code')->index()->comment('郵便番号上3桁');
$table->string('last_code')->index()->comment('郵便番号下4桁');
$table->string('prefecture')->comment('都道府県');
$table->string('city')->comment('市区町村');
$table->string('address')->comment('住所');
});
}
}
comment()部分は無くてもかまいません。
phpMyAdminで見た時わかりやすいから付けてるだけです。
#####★ポイント★
郵便番号上3桁と郵便番号下4桁は、後ほど検索に使うことを想定し、index() を付けてます。
郵便データはマスターデータなので$table->timestamps();は削除しています。
####モデルの編集
app>Models 内のPostalCode.phpを編集します。
class PostalCode extends Model
{
// タイムスタンプを使わないように設定
public $timestamps = false;
// idを変更対象から除外(ブラックリスト方式)
protected $guarded = ['id'];
}
#マイグレーションの実行
ターミナルで以下を実行。
php artisan migrate
#オリジナルコマンドの作成
ターミナルで以下を実行。
php artisan make:command ImportPostalCodeCommand
#####★ポイント★
コマンドを作成することで、データを更新する際に artisan コマンドを打つだけで済むようにします。
####コマンドの編集
app>Console>Commands>ImportPostalCodeCommand.php を開き、内容を以下に変更する。
※参考サイトの記述では問題があったので、記述を変更してます。
// \SplFileObject と記述するのがいやだったので追記しました
use SplFileObject;
// モデルを使用する為に追記
use App\Models\PostalCode;
class ImportPostalCodeCommand extends Command
{
/**
* コンソールコマンドの名前と署名。
*
* @var string
*/
// 実際に使用するコマンドの名前と署名を設定。
protected $signature = 'import:postal-code';
/**
* コンソールコマンドの説明。
*
* @var string
*/
// コマンドの説明。
protected $description = 'Import postal-code';
// ※中略--------------------------------------
/**
* コンソールコマンドを実行します。
*
* @return mixed
*/
public function handle()
{
// テーブルを一度空にする
PostalCode::truncate();
// 郵便番号データ(csv)のフルパスを取得する storage_path を使うことでstorage\が不要になる
$csv_path = storage_path('app\csv\x-ken-all.csv');
// 郵便番号データ(csv)はfile_put_contents部分で加工しpostal_code.csvとして保存しなおします
// 加工済みデータのフルパスを取得する
$put_csv_path = storage_path('app\csv\postal_code.csv');
// 囲み文字(")を削除して加工済みデータをファイルに出力。
file_put_contents($put_csv_path,
str_replace("\"", "", file_get_contents($csv_path)));
// 加工したcsvファイルをオブジェクトとして読み込む。
$file = new SplFileObject($put_csv_path);
// csv列として行を読み込む(SplFileObject によって使われるフラグをセットする)。
$file->setFlags(SplFileObject::READ_CSV);
// 読み込んだデータを1つずつ取り出す
foreach ($file as $row) {
// 変数の文字コードをUTF-8に変換する 第一引数に変換後の形式 第二引数に変換前の形式 第三引数に変換する文字列
mb_convert_variables("UTF-8", "sjis-win", $row);
// 行が空でなければ処理を行う。
if (!is_null($row[0])) {
// テーブルの作成。
PostalCode::create([
// 指定された位置から指定されたバイト分の文字列を返す 第一引数に文字列 第二引数に位置 第三引数にバイト数
'first_code' => substr($row[2], 0, 3),
'last_code' => substr($row[2], 3, 4),
'prefecture' => $row[6],
'city' => $row[7],
'address' => $row[8]
]);
}
}
}
}
#動作確認
ターミナルで以下を実行し、テーブルに問題なくデータが反映されれば完了です。
php artisan import:postal-code
#参考にしたサイト通り作成して起こった問題
「この記事について」で記載した通り、最初は以下のサイトを参考に同じように作成しましたが、同じ問題が発生しハマリました。
初期の段階で原因はなんとなくわかったのですが、どこを直せばいいのかわからず、試行錯誤しました。
やった事をメモとして記載しておきます。
参考にしたサイト
https://blog.capilano-fw.com/?p=1215
####発生した問題
データベースに反映はされるが、入る値がおかしい。
以下はphpMyAdminで確認したサンプルです。
※参考サイトでは郵便番号を整数値(int)として扱っているので、3桁・4桁になっていないのは問題ありません。
後から修正する形をサイトではとっています。
◎正しい反映
| id | first_code | last_code | prefecture | city | address |
|---|---|---|---|---|---|
| 1 | 60 | 0 | 北海道 | 札幌市中央区 | |
| 2 | 64 | 941 | 北海道 | 札幌市中央区 | 旭ケ丘 |
| 3 | 60 | 41 | 北海道 | 札幌市中央区 | 大通東 |
| 4 | 60 | 42 | 北海道 | 札幌市中央区 | 大通西 |
×問題の反映
| id | first_code | last_code | prefecture | city | address |
|---|---|---|---|---|---|
| 1 | 60 | 0 | 北海道 | 札幌市中央区 | |
| 2 | 64 | 941 | 北海道 | 札幌市中央区 | 旭ケ丘 |
| 3 | 60 | 41 | 北海道,札幌市中央区" | 大通東,0,0,0,1,0,0" | |
| 4 | 60 | 42 | 北海道 | 札幌市中央区 | 大通西 |
上の表のように、一部データの郵便番号より後の項目の取り込みが崩れてしまう。
デリミタ(区切り文字)の「,」が文字列として登録されていることから、SQLの構文に置き換わる際に、="北海道,札幌市中央区"のような形になっているのではないかと考え、修正をすることにしました。
#問題解決にならなかった事
####エンコードの見直し
参考サイトではmb_convert_encodingを使用し、元のCSVの文字コードをsjis-winからUTF-8に変換して保存し直してます。
リファレンス
https://www.php.net/manual/ja/function.mb-convert-encoding.php
このエンコードの段階で、デリミタ(区切り文字)と囲い文字の認識がうまくいってないのではないかと考えましたが、リファレンスを確認しても特にデリミタ等に関する内容はなく、改善できず。
変換前のデータと変換後のデータを、Windowhs標準のメモ帳で開き内容を確認しましたが、右下の表示がANSIからUTF-8に変わっているだけで、特に違いはありませんでした。
ちなみに元データをエクセルで確認しても、取り込まれた区切り位置に問題はありませんでした。
メモ帳で開いた場合のサンプル
01101,"060 ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","","北海道","札幌市中央区","",0,0,0,0,0,0
上記のように囲い文字(")が入ってます。
####SplFileObjectの見直し
リファレンス
https://www.php.net/manual/ja/class.splfileobject.php
SplFileObjectのフラグに以下をセットしてみましたが、改善せず。
DROP_NEW_LINE
READ_AHEAD
SKIP_EMPTY
####setCsvControlで区切り文字と囲い文字を設定
リファレンス
https://www.php.net/manual/ja/splfileobject.setcsvcontrol.php
$file->setCsvControl(",", """, "\")を追記しましたが、問題は解決できませんでした。
区切り文字と囲い文字の設定ができるのでいけるかと思ったのですが・・・
####手順の見直し
操作の順番を変えたり、一度保存せずに直接処理するやり方も考えましたが、当方の現知識では解決できませんでした。
#問題解決の為やった事
そもそもとして、本当に区切り文字や囲い文字が原因なのかを調査することにしました。
メモ帳で元のデータを開き、置換を使って手動で囲い文字を削除し、そのデータを使って記述を変更する事にしました。
※のちのち処理の段階で囲い文字を削除できるようにする事を前提に修正していってます。
削除後のサンプル
01101,060 ,0600000,ﾎｯｶｲﾄﾞｳ,ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ,,北海道,札幌市中央区,,0,0,0,0,0,0
####手順の再構成
エンコードに問題がある気がしていたので、以下の手順に直すのと、その為に必要な記述を探しました。
①元データ(csv)から区切り文字(")を削除して、一度保存する。※この段階でエンコードはしない。
②SplFileObjectで保存したデータを開く
③foreachでデータを1つずつ取り出す。
④取り出した所でエンコードする。
⑤エンコードした物を登録する。
####苦労した点
①エンコードをどのタイミングでやるのがいいか
②区切り文字(")を削除するのにどんな関数を使えばいいか
####改善策
エンコードのタイミングは色々参考にした結果、直前がいいという結論に達しました。
また、SplFileObjectで読み込むとmb_convert_encodingは使えないので、mb_convert_variablesに変更しました。
使えない理由は、SplFileObjectで読み込むとオブジェクト扱いになる為です。
リファレンス
https://www.php.net/manual/ja/function.mb-convert-variables.php
その他参考にしたサイト
https://www.tomiryu.com/php/mb_convert_variables/
https://qiita.com/Uuya/items/72f79e7eb83e2a6c8595
https://www.p-nt.com/s/technicblog/archives/147
区切り文字の削除にはstr_replaceを使う事にしました。
削除に使えるのは知っていましたが、大量のデータでもいけるとは思いませんでした。
リファレンス
https://www.php.net/manual/ja/function.str-replace.php
以上を考慮し、掲載している記述にしました。
#その他参考サイトと違う点
####郵便番号
サイトでは数値で扱い、のちのち検索等で使う段階で消えた0を復元(60 -> 060)していますが、面倒だったので文字列にしてます。
色々見た所文字列で使ってる事が多かったのも理由です。
※ただし、検索等で使う時は入力された値を文字列で取り扱うのをお忘れ無く。
####使う元データ
サイトではaddressの取り込み時に、
'address' => (str_contains($row[8], '（')) ? current(explode('（', $row[8])) : $row[8]
上記のように加工されてないデータを扱う為に、(個人的に)わかりづらい記述をされています。
※あくまで素人の自分がわかりづらいと感じてるだけです。
記述が長いので、最初から加工されたデータを使う事にしました。
以上です。