Laravel7导入CSV文件提示Undefined offset: 1错误如何解决？

报错原因 #

Undefined offset: 1错误是代码直接访问$row[1]、$row[2]下标时，当前CSV行的字段数量不足3个，数组不存在对应索引导致的。常见触发场景包括CSV文件末尾有空行、部分行缺失字段、CSV分隔符和解析规则不匹配。

修复实现 #

你使用的是Maatwebsite/Laravel-Excel扩展，修改后的可运行代码如下：

<?php

namespace App\Imports;

use App\Model\BookedVoucher;
use App\Model\BookedVoucherDetail;
use App\Model\Voucher;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\WithChunkReading;
// 新增自定义CSV配置的依赖
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;

class BookedVoucherDetailImport implements ToCollection, WithStartRow, WithChunkReading, ShouldQueue, WithCustomCsvSettings
{
    /**
     * @var BookedVoucher 
     */
    protected $bookedVoucher;

    /**
     * @param BookedVoucher $bookedVoucher
     */
    public function __construct(BookedVoucher $bookedVoucher)
    {
        $this->bookedVoucher = $bookedVoucher;
    }

    /**
     * @return int
     */
    public function startRow(): int
    {
        return 2;
    }

    public function chunkSize(): int
    {
        return 1000;
    }

    // 自定义CSV解析参数
    public function csvSettings(): array
    {
        return [
            'delimiter' => ',', // 这里如果你的CSV是分号分隔就改成';'，制表符分隔就改成"\t"
            'enclosure' => '"',
            'escape' => '\\',
            'encoding' => 'UTF-8',
        ];
    }

    public function collection(Collection $rows)
    {
        foreach ($rows as $row) 
        {
            // 新增行字段校验，不足3个字段的异常行直接跳过，也可以自行改为记录错误日志
            if (count($row) < 3) {
                continue;
            }

            $item = new BookedVoucherDetail();
            $item->booked_voucher_id = $this->bookedVoucher->id;
            $item->course_code = $row[0];
            $item->voucher_code = $row[1];
            $item->prakerja_name = $row[2];

            $voucher = Voucher::where('code', $row[1])->first();

            if ($voucher && $voucher->claimed == 0 && $voucher->is_booked == 0) {
                $item->status = 'OK';
            } elseif ($voucher && $voucher->claimed == 0 && $voucher->is_booked == 1) {
                $item->status = 'Already Booked';
            } elseif ($voucher && $voucher->claimed == 1 && $voucher->is_booked == 0) {
                $item->status = 'Already Claimed';
            } elseif ($voucher && $voucher->claimed == 1 && $voucher->is_booked == 1) {
                $item->status = 'Already Booked and Claimed';
            } else {
                $item->status = 'Not Found';
            }

            $item->save();

            if ($voucher) {
                $voucher->update(['is_booked' => 1]);
            }
        }
    }
}

优化建议 #

如果导入数据量较大，建议提前把所有CSV中的voucher code批量查询出来放到内存数组中匹配，避免每行都查询数据库，可大幅提升导入速度。

内容的提问来源于stack exchange，提问作者anon