【PHP】CSVインポートの方法〜大量データもバルクインサートでバッチリ！〜【Laravel】

ホーム
バックエンド
【PHP】CSVインポートの方法〜大量データもバルクインサートでバッチリ！〜【Laravel】

バックエンド

2018.12.27

【PHP】CSVインポートの方法〜大量データもバルクインサートでバッチリ！〜【Laravel】

デスクワークの人なら誰もが知っていると思われる『CSVファイル』。

データが軽くてエクセルでも開け、プログラミングでも扱いやすいということで、

あらゆる業種で使われているファイル形式じゃないかなと思います。

エクセルの関数を使えば簡単に計算もできるんですが、

例えば数千件、数万件と大量のデータを扱おうとするとエクセルではなかなかしんどくなってくるので、

データベースの出番になります。

今回、CSVファイルをデータベースにインポートする機会があったので、

『PHP(Laravel5.2)』を使って『データベース(mysql)』にCSVデータをインポートする方法をまとめてみました。

Sponsored link

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というのは~~スプール(データを一時的に貯める場所)の略で~~、

アオキ

Standard PHP Library の略でした、ご指摘ありがとうございます！

PHPでCSVファイルを使うなら定番のクラスのようです。

手順的には、

SplFileObjectでCSVファイルを取得して、
1行ずつ配列の箱に入れて、
データベースにインポート

になります。

もし配列やクラスがピンとこない場合はこれらの記事も参考にしてみてください。

もんプロ～問題発見と解決のためのプログラミング〜

【PHP】配列や連想配列が覚えづらかったので学校に例えてみた【初心者向け】

https://coinbaby8.com/php-how-to-learn-array.html

配列ってなんか覚えにくいんだよね〜、いくつもくっついてたりするし。うまい覚え方ないのかなぁ・・ググって見ても配列のわかりやすい記事があまりなかったので、小学校に例えてみることにしました。※わかりやすさ重視でいろいろ説明をはしょっています。PHPの配列の使い方〜わかりやすさ重視〜『PHP』に限らず、ほとんどのプログラミング言語には、『変数』という機能があります。『変数』というのはざっくりいうと『箱』の事で、『箱』をつくって、その中に好きな文字や数字を入れて、取り出しやすくしたものです。※『PHP』の『変数...

もんプロ～問題発見と解決のためのプログラミング〜

【PHP】クラスと抽象クラスとインターフェースとトレイトとDIをまとめてみた【図解...

https://coinbaby8.com/php-class-abstract-interface-trait-di.html

クラス？抽象クラス？インターフェース？トレイト？DI?なるほどわからん。。プログラミングを覚えるにつれ、目にする機会が増えてくるのが、クラスやオブジェクト指向というキーワード。これまでに何度もなんどもググっては挫折、ググっては挫折を繰り返してきたのですが、何度も何度も『Laravel』のソースコードを読み進めるうちに、なんとなく、ぼんやりですが、使い分けがわかりはじめた(ような気がする)ので、忘れないように記事にしようと思います。※あくまでも初心者向けで、いろいろはしょっていますので、ぜひ寛大な心でお読...

PHP(Laravel)でCSVインポートする場合のファイル

今回は、こんなデータを想定しています。

横の列に、名前と日付と金額があって、
縦の行にそれぞれのデータが入っていると。

適当に4件書いてますが、実際には数千件〜数万件のデータ量になります。

今回は『Laravel』で実施した関係で、5つのファイルを作ることになりました。

モデル・・データベース設定
マイグレーション・・データベースのテーブル構成
ルート・・クリックした後どの処理をするか
ビュー・・見た目・フォーム
コントローラー・・実際の処理

本来は、コントローラーに処理を書きすぎるとファイルが大きくなりすぎるので、

(ファットコントローラーと呼ばれています)

サービスというフォルダを作ってそこにファイルを作った方がよいようです。

(サービスプロバイダやDIといった機能もいずれ記事にする予定です)

Sponsored link

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件ずつインポートしていた事。

その事をメンターに相談してみると、

メンター

基本的に、大量データを書き込む場合は、一件一件INSERTするのは効率悪すぎるのでやめたほうがいいです。

メンター

500件とか1000件とかまとまった単位でBULK INSERTしてみてください！

アオキ

バルクインサートですか・・

聞いたことはあったけれど実装するのは初。

これまたいろいろググって完成しました。

Sponsored link

大量データをインポートする場合(バルクインサート)

変更点だけコメントしてます。

//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]);
        
        }

    }

という流れになっています。

アオキ

いやね、さらっと書いてるけど結構時間かかったんですって。

Sponsored link