PHPでCSVファイルをデータベースにインポートする方法

2023年3月21日 2023年3月31日

phpの標準クラスの「SplFileObject」を使えば可能。
シーダーにCSVファイルを読み込ませて、それを登録するって感じ。

こんなCSVファイルがあったとする↓
プロジェクトディレクトリ/storage/app/private/csv/inquiries_migration.csv

created_at,serial,customer,dealer,type,operator_id,questioner,phoneNumber,kinds,question,answer,satisfaction,inquiry_id,remote
2023-03-19 00:00:00,P292I3456,アタオカ建築工房,販売店A,NASサーバー,1,e,,設定,電源が入らない,コンセントが抜けていあt,不満,F1222,TeamViewer
2023-03-20 00:00:P292I3457,奈良左官工業,販売店B,NASサーバー,1,e,050-5555-5555,,初期パスワードがわからない\n変更した記憶もない,変更初期パスワード案内,不満,F1223,TeamViewer
2023-03-20 00:00:00,P292I3458,山口司法書士事務所,販売店C,NASサーバー,1,e,050-5555-6666,障害,解約したい\n取り外しに来い,契約した販売店にお問い合わせいただくよう案内,不満,F1224,TeamViewer

プロジェクトディレクトリ/database/seeders/InquiryTableSeeder.php ↓

<?php

namespace Database\Seeders;

use App\Models\Inquiry;
use DateTime;
use Illuminate\Database\Seeder;
use Illuminate\Support\Arr;
use SplFileObject;

class InquiryTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $csv = new SplFileObject(storage_path('app/private/csv/inquiries_migration.csv'));
        $csv->setFlags(SplFileObject::READ_CSV | SplFileObject::READ_AHEAD | SplFileObject::SKIP_EMPTY | SplFileObject::DROP_NEW_LINE);

        $headers = [];
        foreach ($csv as $i => $line) {
            if ($i === 0) {
                $headers = $line;
                continue;
            }

            $values = array_combine($headers, $line);

            $values['kinds'] = $values['kinds'] ?? '設定'; // nullまたは空の場合は '設定' を代入

            if (isset($values['phoneNumber']) && !empty($values['phoneNumber'])) {
                $values['PhoneNumberWithoutHyphen'] = preg_replace('/[^0-9]/', '', $values['phoneNumber']);
            }

            $values = array_map(function ($value) {
                return str_replace('\n', "\n", $value);
            }, $values); // 全てのカラムについて '\n' を改行文字に変換

            $values['created_at'] = $values['created_at'] ?? new DateTime('1970-01-01'); // nullまたは空の場合は new DateTime('1970-01-01') を代入

            //inquiry_idが同じなら同じレコードとして判定(なければ新規作成、あれば値を更新)
            Inquiry::updateOrCreate(['inquiry_id' => $values['inquiry_id']], Arr::except($values, ['inquiry_id']));
        }
    }
}

上記のコードをもとに解説する。

ジャンプできる目次

1 値を整形する場合
2 改行はどのように表現するのか
3 SplFileObjectとは
4 Column not found: 1054 Unknown column 'created_at’

値を整形する場合

csv上のkindsの値が空ならデータベースのkindsカラムに「設定」と挿入したい
csv上のphoneNumberに値があるならデータベースphoneNumberWithoutカラムに数字以外の文字列を取り除いた値を挿入したい

csv上のcreated_atが空なら1970-01-01をデータベースのcreated_atに挿入したい
みたいな意図で、下記のようなコードが含まれている。

        $values['kinds'] = $values['kinds'] ?? '設定'; // nullまたは空の場合は '設定' を代入

        if (isset($values['phoneNumber']) && !empty($values['phoneNumber'])) {
            $values['PhoneNumberWithoutHyphen'] = preg_replace('/[^0-9]/', '', $values['phoneNumber']);
        }

        $values = array_map(function ($value) {
            return str_replace('\n', "\n", $value);
        }, $values); // 全てのカラムについて '\n' を改行文字に変換

        $values['created_at'] = $values['created_at'] ?? new DateTime('1970-01-01'); // nullまたは空の場合は new DateTime('1970-01-01') を代入

別に整形する必要がなければ、これらの処理は書かなくていい。

改行はどのように表現するのか

上記のコードのこの部分で変換している。

$values = array_map(function ($value) {
    return str_replace('\n', "\n", $value);
}, $values); // 全てのカラムについて '\n' を改行文字に変換

\nは改行としてデータベースに登録してね！
って意味になる。ダブルクオーテーションに変換しているところがみそ。
別に'<br>'だろうが何だろうが、"\n"に変換してしまえば改行になるはず。

参考にしたサイト
https://teratail.com/questions/309408

SplFileObjectとは

SplFileObject は、PHPが提供するファイル操作用のクラスで、ファイルからデータを読み込むためのインターフェースを提供します。 SplFileObject は、SplFileInfo クラスの拡張クラスであり、ファイルに対する抽象化レイヤーを提供します。

SplFileObject は、ファイルの読み込み、書き込み、テキスト・バイナリーモードでのオープン、行指向でのデータの読み込みなど、多くの便利なメソッドを提供しています。また、SplFileObject は、イテレータとして機能するため、foreachループを使用してファイルを逐次的に読み込むこともできます。

Column not found: 1054 Unknown column 'created_at’

データベースのテーブルにちゃんとあるはずのカラムが、さも存在しないかのようなエラーが出ることがある。

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created_at' in 'field list' (SQL: insert into `inquiries` (`inquiry_id`, `created_at`, `serial`, `customer`, `dealer`, `type`, `operator_id`, `questioner`, `phoneNumber`, `kinds`, `question`, `answer`, `satisfaction`, `remote`, `updated_at`, `created_at`) values (aaaaaa, 2023-03-19 00:00:00, 21555555, asdf, e, NASサーバー, 1, e, , 設定, 改行, 変更完了, 不満, TeamViewer, 2023-03-23 18:36:52, 2023-03-23 18:36:52))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:703
    699?         // If an exception occurs when attempting to run a query, we'll format the error
    700?         // message to include the bindings with SQL, which will make this exception a
    701?         // lot more helpful to the developer instead of just the database's errors.
    702?         catch (Exception $e) {
  ? 703?             throw new QueryException(
    704?                 $query, $this->prepareBindings($bindings), $e
    705?             );
    706?         }
    707?     }

  ~ A column was not found: You might have forgotten to run your migrations. You can run your migrations using `php artisan migrate`.
    https://laravel.com/docs/master/migrations#running-migrations

      +18 vendor frames
  19  database/seeders/InquiryTableSeeder.php:94
      Illuminate\Database\Eloquent\Model::__callStatic("updateOrCreate")

      +22 vendor frames
  42  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

この場合は、CSVファイルを作り直したほうがいい。

テスト環境から中身をコピペで作り直すのではなく、1回削除して作り直したほうがいい。

複数のエディターを経由してCSVファイルを更新したりすると、なにかしらのタイミングで文字コードがおかしくなったりする可能性がある。

1つのエディターでは開けるけど、ほかのエディターではエラーが出たりする場合などはまさに怪しい。

自分の場合LibreOfficeなら読み込めるのに、CresentEveだと開くときに複数の文字コードが混在しているぞ！ってエラーが出た。

参考:

シーダー読み込み時に、データベースにcreated_atの登録処理が2回走りエラーになる