デスクワークの人なら誰もが知っていると思われる『CSVファイル』。
データが軽くてエクセルでも開け、プログラミングでも扱いやすいということで、
あらゆる業種で使われているファイル形式じゃないかなと思います。
エクセルの関数を使えば簡単に計算もできるんですが、
例えば数千件、数万件と大量のデータを扱おうとするとエクセルではなかなかしんどくなってくるので、
データベースの出番になります。
今回、CSVファイルをデータベースにインポートする機会があったので、
『PHP(Laravel5.2)』を使って『データベース(mysql)』にCSVデータをインポートする方法をまとめてみました。
PHP(Laravel)でCSVインポートをする方法
『PHP』を使って『データベース』にCSVファイルをインポートする場合、
大きく2つの方法があるのかなと思います。
- ライブラリを使う
- PHP直書きで頑張る
1のライブラリ(便利ツール)は、Laravelの場合、
- Laravel-Excel
- fast-excel・・Laravel5.3以上
と2つのライブラリが用意されています。
今回は『Laravel5.2』で実施する必要があったため、
『fast-excel』は対象から外れ、
『Laravel-Excel』を試してみたのですがうまく動かず・・
ということで、2番の『PHP直書き』で頑張ることにしました。
『PHP CSV インポート』的なキーワードでググりにググって、
時にはメンターの方にもサポートいただきつつ、
無事CSVインポートできるようになったのでまとめてみます。
PHP(Laravel)でCSVインポートする時のポイント
PHPでCSVインポートするポイントは大きく3つかなと思います。
- SplFileObjectクラスを使う
- 連想配列にまとめて連想配列で操作する
- 大量データならバルクインサートを検討する
Splというのはスプール(データを一時的に貯める場所)の略で、
PHPでCSVファイルを使うなら定番のクラスのようです。
手順的には、
- SplFileObjectでCSVファイルを取得して、
- 1行ずつ配列の箱に入れて、
- データベースにインポート
になります。
もし配列やクラスがピンとこない場合はこれらの記事も参考にしてみてください。
PHP(Laravel)でCSVインポートする場合のファイル
今回は、こんなデータを想定しています。
横の列に、名前と日付と金額があって、
縦の行にそれぞれのデータが入っていると。
適当に4件書いてますが、実際には数千件〜数万件のデータ量になります。
今回は『Laravel』で実施した関係で、5つのファイルを作ることになりました。
- モデル・・データベース設定
- マイグレーション・・データベースのテーブル構成
- ルート・・クリックした後どの処理をするか
- ビュー・・見た目・フォーム
- コントローラー・・実際の処理
本来は、コントローラーに処理を書きすぎるとファイルが大きくなりすぎるので、
(ファットコントローラーと呼ばれています)
サービスというフォルダを作ってそこにファイルを作った方がよいようです。
(サービスプロバイダやDIといった機能もいずれ記事にする予定です)
PHP(Laravel)でCSVインポートするコード
ちょっと加工していますがほぼ実際のコードを載せておきます。
- モデル・・データベース設定
//laravel/app/CSVimport.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class CSVimport extends Model
{
protected $fillable = ['name','reserved_date','checkin_date','total_price'];
}
CSVインポートする時に必ず必要な要素は protected $fillable で指定しておきます。
２. マイグレーション・・データベースのテーブル構成
//laravel/database/migrations/create_csvimports_table.php
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateCSVimportsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('csvimports', function (Blueprint $table) {
//$table->increments('id');
$table->string('name',50);
$table->datetime('reserved_date')->nullable();
$table->datetime('checkin_date')->nullable();
$table->integer('total_price')->nullable();
});
}
public function down()
{
Schema::drop('csvimports');
}
}
マイグレーションはそのまんまです。
３. ルート・・クリックした後どの処理をするか
//laravel/Http/routes.php
<?php
Route::get('/', 'CSVimportsController@index')->name('csvimport_index');
Route::post('/import', 'CSVimportsController@import')->name('csvimport_import');
ルートもそのまんま。(Laravel5.3以上だとroute.phpの場所が変わるので注意)
４. ビュー・・見た目・フォーム
//laravel/resources/views/welcome.blade.php
<!DOCTYPE html>
<html>
<head>
<title>BookingcurveTest</title>
<link href="https://fonts.googleapis.com/css?family=Lato:100" rel="stylesheet" type="text/css">
<style>
</style>
</head>
<body>
<div class="container">
<div class="content">
<div class="title">Bookingcurve</div>
<h4>CSVファイルを選択してください</h4>
<div class="row">
<div class="col-md-6">
■手順
1. CSVで保存します。
2. ファイルを選択し読み込んでください。
</div>
</div>
<form role="form" method="post" action="import" enctype="multipart/form-data">
{{ csrf_field() }}
<input type="file" name="csv_file" id="csv_file">
<div class="form-group">
<button type="submit" class="btn btn-default btn-success">保存</button>
</div>
</form>
</div>
</div>
</body>
</html>
ポイントは、formの中の
<form role="form" method="post" action="import" enctype="multipart/form-data">
という箇所かと。CSVをインポートするなら必須と思われます。
続いて肝のコントローラー。
コメントでざっと解説してます。
５. コントローラー・・実際の処理
//laravel/Http/Controllers/CSVimportsController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Http\Requests;
use App\CSVimport;
//useしないと 自動的にnamespaceのパスが付与されるのでuse
use SplFileObject;
class CSVimportsController extends Controller
{
protected $csvimport = null;
public function __construct(CSVimport $csvimport)
{
$this->csvimport = $csvimport;
}
public function index()
{
return view('welcome');
}
/**
* CSVインポート
*
* @param Request
* @return \Illuminate\Http\Response
*/
public function import(Request $request)
{
//全件削除
CSVimport::truncate();
// ロケールを設定(日本語に設定)
setlocale(LC_ALL, 'ja_JP.UTF-8');
// アップロードしたファイルを取得
// 'csv_file' はビューの inputタグのname属性
$uploaded_file = $request->file('csv_file');
// アップロードしたファイルの絶対パスを取得
$file_path = $request->file('csv_file')->path($uploaded_file);
//SplFileObjectを生成
$file = new SplFileObject($file_path);
//SplFileObject::READ_CSV が最速らしい
$file->setFlags(SplFileObject::READ_CSV);
$row_count = 1;
//取得したオブジェクトを読み込み
foreach ($file as $row)
{
// 最終行の処理(最終行が空っぽの場合の対策
if ($row === [null]) continue;
// 1行目のヘッダーは取り込まない
if ($row_count > 1)
{
// CSVの文字コードがSJISなのでUTF-8に変更
$name = mb_convert_encoding($row[0], 'UTF-8', 'SJIS');
$reserved_date = mb_convert_encoding($row[1], 'UTF-8', 'SJIS');
$checkin_date = mb_convert_encoding($row[2], 'UTF-8', 'SJIS');
$total_price = mb_convert_encoding($row[3], 'UTF-8', 'SJIS');
//1件ずつインポート
CSVimport::insert(array(
'name' => $name,
'reserved_date' => $reserved_date,
'checkin_date' => $checkin_date,
'total_price' => $total_price
));
}
$row_count++;
}
return view('welcome');
}
}
という流れです。
このコードで無事CSVインポートできたのですが、一つ問題が・・
2000行くらいのCSVファイルをインポートしようとした時に、
タイムアウトでエラーが発生してしまったのです。
原因は、1件ずつインポートしていた事。
その事をメンターに相談してみると、
聞いたことはあったけれど実装するのは初。
これまたいろいろググって完成しました。
大量データをインポートする場合(バルクインサート)
変更点だけコメントしてます。
//laravel/Http/Controllers/CSVimportsController.php
public function import(Request $request)
{
CSVimport::truncate();
setlocale(LC_ALL, 'ja_JP.UTF-8');
$uploaded_file = $request->file('csv_file');
$file_path = $request->file('csv_file')->path($uploaded_file);
$file = new SplFileObject($file_path);
$file->setFlags(SplFileObject::READ_CSV);
//配列の箱を用意
$array = [];
$row_count = 1;
foreach ($file as $row)
{
if ($row === [null]) continue;
if ($row_count > 1)
{
$name = mb_convert_encoding($row[0], 'UTF-8', 'SJIS');
$reserved_date = mb_convert_encoding($row[1], 'UTF-8', 'SJIS');
$checkin_date = mb_convert_encoding($row[2], 'UTF-8', 'SJIS');
$total_price = mb_convert_encoding($row[3], 'UTF-8', 'SJIS');
$csvimport_array = [
'name' => $name,
'reserved_date' => $reserved_date,
'checkin_date' => $checkin_date,
'total_price' => $total_price
];
// つくった配列の箱($array)に追加
array_push($array, $csvimport_array);
// 数が多いと処理重すぎなのでバルクインサートに切り替える
// CSVimport::insert(array(
// 'name' => $name,
// 'reserved_date' => $reserved_date,
// 'checkin_date' => $checkin_date,
// 'total_price' => $total_price
// ));
}
$row_count++;
}
//追加した配列の数を数える
$array_count = count($array);
//もし配列の数が500未満なら
if ($array_count < 500){
//配列をまるっとインポート(バルクインサート)
CSVimport::insert($array);
} else {
//追加した配列が500以上なら、array_chunkで500ずつ分割する
$array_partial = array_chunk($array, 500); //配列分割
//分割した数を数えて
$array_partial_count = count($array_partial); //配列の数
//分割した数の分だけインポートを繰り替えす
for ($i = 0; $i <= $array_partial_count - 1; $i++){
CSVimport::insert($array_partial[$i]);
}
}
という流れになっています。
さいごに
PHP(Laravel)でCSVインポートを実装する過程でいろんな事をググりまして、
- SplFileObjectの使い方
- 連想配列の操作
- バルクインサートの方法
などを知ることができ、知識を整理することができました。
いろんな知識や技術を組み合わせる必要があるので結果的にすごく勉強になったなと思います。
『PHP(Laravel)』ではこんな記事も読まれています。
1. 【PHP/Laravel】初心者向けの動画をリリースしました【Udemy】2. 【Laravel(PHP)】初心者向け アプリのつくり方 をリリースしました【techpit】
3. 【PHP】【Laravel】CSVエクスポートの方法〜5つのポイント〜
4. 【PHP】オブジェクトと連想配列の違いについて調べてみた【初心者向け】
5. 【PHP】CSVインポートの方法〜大量データもバルクインサートでバッチリ！〜【laravel】
6. 【PHP】【図解】クラスと抽象クラスとインターフェースとトレイトとDIをまとめてみた【初心者向け】
7. 【PHP】配列や連想配列が覚えづらかったので学校に例えてみた【初心者向け】
8. 【PHP】ホームページに天気予報を表示させる方法【Webスクレイピング】【初心者向け】
9. 【Laravel】Webアプリ環境構築の仕方【Vue.js】【初心者向け】
10. 【PHP】2次元(多次元)配列でデータ取得したいならarray_columnがめっさ便利
アオキのツイッターアカウント。
はじめまして。
こちらの記事を参考にmysqlへのバルクインサートを実装することが出来ました。
コードの間違いもなく、あまりにもさらっと実装できてしまったので、びっくりしています。
これはひとえに青木さんの努力の結晶かと感じました。
大変参考になりました。
今後ともブログの更新、楽しみにしており、また勉強させていただく機会があると思います。
よろしくお願いします。
トキキさん、メッセージありがとうございます。
CSVインポートおめでとうございます！ 書いた甲斐がありました＾＾
>> Splというのはスプール(データを一時的に貯める場所)の略で、
https://www.php.net/manual/ja/book.spl.php
Standard PHP Library の略だと思います
ご指摘ありがとうございます！ 修正いたしました。