バックエンド

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


Warning: Trying to access array offset on value of type bool in /home/mochaccino8/coinbaby8.com/public_html/wp-content/themes/mag_tcd036/functions/short_code.php on line 36

Warning: Trying to access array offset on value of type bool in /home/mochaccino8/coinbaby8.com/public_html/wp-content/themes/mag_tcd036/functions/short_code.php on line 36

Warning: Trying to access array offset on value of type bool in /home/mochaccino8/coinbaby8.com/public_html/wp-content/themes/mag_tcd036/functions/short_code.php on line 36

Warning: Trying to access array offset on value of type bool in /home/mochaccino8/coinbaby8.com/public_html/wp-content/themes/mag_tcd036/functions/short_code.php on line 36

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

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

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

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

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

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

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

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

Sponsored link

PHP(Laravel)でCSVインポートをする方法

『PHP』を使って『データベース』にCSVファイルをインポートする場合、

大きく2つの方法があるのかなと思います。

  1. ライブラリを使う
  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つかなと思います。

  1. SplFileObjectクラスを使う
  2. 連想配列にまとめて連想配列で操作する
  3. 大量データならバルクインサートを検討する

Splというのはスプール(データを一時的に貯める場所)の略で

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

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

手順的には、

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

になります。

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

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

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

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

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

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

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

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

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

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

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

Sponsored link

PHP(Laravel)でCSVインポートするコード

ちょっと加工していますがほぼ実際のコードを載せておきます。

  1. モデル・・データベース設定
//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 で指定しておきます。

2. マイグレーション・・データベースのテーブル構成

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

マイグレーションはそのまんまです。

3. ルート・・クリックした後どの処理をするか

//laravel/Http/routes.php

<?php

Route::get('/', 'CSVimportsController@index')->name('csvimport_index');

Route::post('/import', 'CSVimportsController@import')->name('csvimport_import');

ルートもそのまんま。(Laravel5.3以上だとroute.phpの場所が変わるので注意)

4. ビュー・・見た目・フォーム

//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をインポートするなら必須と思われます。

続いて肝のコントローラー。

コメントでざっと解説してます。

5. コントローラー・・実際の処理

//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

さいごに

PHP(Laravel)でCSVインポートを実装する過程でいろんな事をググりまして、

  • SplFileObjectの使い方
  • 連想配列の操作
  • バルクインサートの方法

などを知ることができ、知識を整理することができました。

いろんな知識や技術を組み合わせる必要があるので結果的にすごく勉強になったなと思います。

アオキ
この記事が何かしらお役にたちますように。
created by Rinker
¥3,960 (2022/12/03 21:23:41時点 Amazon調べ-詳細)

『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がめっさ便利

アオキ
ツイッターでも記事ネタ含めちょろちょろ書いていくので、よろしければぜひフォローお願いしますm(_ _ )m

アオキのツイッターアカウント


関連記事一覧 (一部広告あり)

コメント

    • トキキ
    • 2020年 5月 13日 9:03pm

    はじめまして。
    こちらの記事を参考にmysqlへのバルクインサートを実装することが出来ました。
    コードの間違いもなく、あまりにもさらっと実装できてしまったので、びっくりしています。
    これはひとえに青木さんの努力の結晶かと感じました。
    大変参考になりました。
    今後ともブログの更新、楽しみにしており、また勉強させていただく機会があると思います。
    よろしくお願いします。

      • aoki_monpro
      • 2020年 5月 14日 9:41am

      トキキさん、メッセージありがとうございます。
      CSVインポートおめでとうございます! 書いた甲斐がありました^^

    • がろ
    • 2019年 7月 23日 10:57am

    >> Splというのはスプール(データを一時的に貯める場所)の略で、
    https://www.php.net/manual/ja/book.spl.php
    Standard PHP Library の略だと思います

      • aoki_monpro
      • 2019年 7月 23日 1:21pm

      ご指摘ありがとうございます! 修正いたしました。

  1. この記事へのトラックバックはありません。

CAPTCHA


最近の記事

アーカイブ

  1. オンライン教材

    【AWS】【初心者向け】インフラの基礎からわかる講座をリリースしました【Udem…
  2. オンライン教材

    【Vue.js】初心者向け講座をリリースしました。【Udemy】
  3. オンライン教材

    【PHP/Laravel】初心者向けの動画をリリースしました【Udemy】
  4. STEAM教育

    熊本市東区でプログラミングのワークショップやります〜夏休み特別企画 こども向けス…
  5. MENTA

    MENTA「メンタ」始めました
PAGE TOP