目次

【Laravel】CSVアップロード(インポート)

CSVファイルをアップロードし、バリデーションチェック&DBに登録する方法の一例。

config\site\csv.php

コンフィグファイルに、csv の列に対応した DB カラム名の関連付けを定義する。

<?php
return [
    'csv_columns' => [
        0  => 'number',
        1  => 'title',
        2  => 'name',
        3  => 'age',
        4  => 'comment',
     ],
];

app\Libs\SampleCsv.php

csv 周りの処理をクラスにまとめておく。

<?php

namespace App\Libs;

class SampleCsv
{
    private $file;
    private $data = [];

    public function __construct($path)
    {
        $this->file = new \SplFileObject($path);
    }

    public function convertColumns()
    {
        $count = 0;

        while (!$this->file->eof()) {
            $line = $this->file->fgetcsv(',', '\'');

            $count ++;

            if (!$line || is_null($line[0])) {
                continue;
            }

            mb_convert_variables('UTF-8', 'sjis-win', $line);

            $tmp = [];
            foreach ($line as $key => $val) {
                $column_name = config('site.csv.csv_columns')[$key];
                $tmp[$column_name] = $val;
            }

            $this->data[$count] = $this->revise($tmp);
        }

        return $this->data;
    }

    // 入力内容の補正
    private function revise($inputs)
    {
        foreach ($inputs as $key => $input) {
            switch ($key) {
                case 'number':
                case 'age':
                    // 全角英数を半角に
                    $input = mb_convert_kana($input, 'as');
                    break;
            }

            $inputs[$key] = $input;
        }

        return $inputs;
    }

    public function validationRules()
    {
        return [
            'number'            => 'bail|required|integer|between:0,999999999',
            'title'             => 'bail|required|max:100',
            'name'              => 'bail|required|max:50',
            'age'               => 'bail|required|integer|between:0,150',
            'comment'           => 'bail|max:1000',
        ];
    }

    public function validationMessages()
    {
        return [
        ];
    }

    public function validationAttributes()
    {
        return [
            'number'            => 'No',
            'title'             => 'タイトル',
            'name'              => '名前',
            'age'               => '年齢',
            'comment'           => 'コメント',
        ];
    }
}

app\Http\Controllers\SampleController.php

ajax の場合の例。
普通の POST 送信の場合は「return response()→json()」としているところを書き換えれば OK。

<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Validator;

use DB;

# models
use App\Models\Sample;

# requests
use App\Http\Requests\Admin\SampleRequest;

# libs
use App\Libs\SampleCsv;

class SampleController extends Controller
{
    public function store(SampleRequest $request)
    {
        $sample_csv = new SampleCsv($request->file('csv')->path());
        $csv_data = $sample_csv->convertColumns();

        $csv_errors = [];		// エラーメッセージを格納
        $number_count = [];		// number の重複チェック用

        foreach ($csv_data as $key => $val) {
            $validator = Validator::make(
                $val,
                $sample_csv->validationRules(),
                $sample_csv->validationMessages(),
                $sample_csv->validationAttributes()
            );

            if ($validator->fails() === true) {
                $csv_errors[$key] = $validator->errors()->all();
            }

            $number = $val['number'];

            $number_count[$number] = isset($number_count[$number])
                ? $number_count[$number] + 1
                : 1;

            $sample = Sample::where('number', $number)
                ->first();

            if (!is_null($sample)) {
                $csv_errors[$key][] = $number . ' は登録済みのNoです。';

            } elseif ($number_count[$number] > 1) {
                $csv_errors[$key][] = 'No ' . $number . ' はcsvファイル内で重複しています。';
            }
        }

        if (count($csv_errors) > 0) {
            $response = [];
            $response['message'] = 'The given data was invalid.';
            $response['errors'] = ['csv_errors' => $csv_errors];
            return response()->json($response, 422);
        }

        try {
            DB::beginTransaction();

            Sample::delete();

            foreach ($csv_data as $val) {
                $sample = new Sample();
                $sample->fill($val);
                $sample->save();
            }

        } catch (\Exception $e) {
            $log_message = var_export([
                    'message' => $e->getMessage(),
                    'file' => $e->getFile(),
                    'line' => $e->getLine(),
                ], true);
            \Log::channel('system_error')->info($log_message);

            return response()->json(['status' => 'error']);
        }

        DB::commit();

        return response()->json(['status' => 'success']);
    }
}

app\Http\Requests\SampleRequest.php

<?php

namespace App\Http\Requests;

use Illuminate\Foundation\Http\FormRequest;

class SampleRequest extends FormRequest
{
    public function authorize()
    {
        return true;
    }

    public function rules()
    {
        return [
            'csv'               => [
                'bail',
                'required',
                'file',
            ],
        ];
    }

    public function attributes()
    {
        return [
            'csv'               => 'csv',
        ];
    }
}