Laravel导入CSV数据时如何修复'Invalid datetime format'错误？

错误信息 #

Invalid datetime format: 7 ERROR: invalid input syntax for type date: "" CONTEXT: unnamed portal parameter $6 = '' (SQL: update "crew_members" set "cdcno" = , "passportno" = , "birth_date" = 01-01-2005, "birth_city" = , "cdc_issued_at" = , "cdc_issued_on" = , "cdc_expires_on" = , "passport_issued_at" = , "passport_issued_on" = , "passport_expires_date" = , "presea_cert" = , "last_dna_date" = , "address_medical_center" = , "updated_at" = 2023-05-25 12:52:13 where "id" = 49)

从CSV文件读取数据更新数据库时出现日期格式无效错误，需解决该问题。

现有代码 #

控制器代码 #

public function crewupdatecsv(Request $request)
{
    $file = $request->file('updated_crew');
    //dd($file);
    if (strtolower($file->getClientOriginalExtension()) == 'csv') {
        $fh = fopen($file, 'r');
        $header = fgetcsv($fh);

        while ($data = fgetcsv($fh)) {                
            //dd(request()->all());
            $crewmember = CrewMember::where('id', $data[1])->first();
            //dd($crewmember);
           // $update['company_id'] = $data[2];
            $update['last_vessel'] = $data[3];
            $update['first_name'] = $data[4];
            $update['middle_name'] = $data[5];
            $update['last_name'] = $data[6];
            $update['gender'] = $data[7];
            $update['rank_id'] = $data[8];
            $update['nationality'] = $data[9];
            $update['birth_date'] = $data[10];
            $update['birth_city'] = $data[11];
            $update['cdcno'] = $data[12];
            $update['cdc_issued_at'] = $data[13];
            $update['cdc_issued_on'] = $data[14];
            $update['cdc_expires_on'] = $data[15];
            $update['passportno'] = $data[16];
            $update['passport_issued_at'] = $data[17];
            $update['passport_issued_on'] = $data[18];
            $update['passport_expires_date'] = $data[19];
            $update['presea_cert'] = $data[20];
            $update['last_dna_date'] = $data[21];
            $update['last_dna_test_result'] = $data[22];
            $update['address_medical_center'] = $data[23];
            //dd($update);
            $crewmember->update($update);
            
        }
        
        return back()->with('success', 'Bulk Crew Member are updated.');
    } else
        return back()->with('failure', 'Wrong type of file is uploaded.');
   
}

Blade视图代码 #

@foreach ($crewmembers as $i => $cm)
<tr>
    <td>{{$cm->id}}</td>
    <td class="standard_grid_table_yellow">{{$cm['company']['company_name']??''}}</td>
    <td class=""><input type="text" name="last_vessel" value="{{$cm['vessel']['vessel_name']??''}}"> </td>
    <td class=""><input type="text" name="first_name" value=" {{$cm->first_name}}"></td>
    <td class="standard_grid_table_yellow"><input type="text" name="middle_name" value="{{$cm->middle_name}}"></td>
    <td class=""><input type="text" name="last_name" value="{{$cm->last_name}}"></td>
    <td class="standard_grid_table_yellow"><input type="text" name="gender" value="{{$cm->gender}}"></td>
    <td class=""><input type="text" name="rank_id" value="{{$cm['rank']['rank_name']??''}}"></td>
    <td class="standard_grid_table_yellow"><input type="text" name="nationality" value="{{$cm['country']['country_name']}}"></td>
    <td class=""><input type="text" name="birth_date" class="dateentry" value="{{$cm->birth_date}}"></td>
    <td class="standard_grid_table_yellow"><input type="text" name="birth_city" value="{{$cm->birth_city }}"></td>
    <td class=""><input type="text" name="cdcno" value="{{$cm->cdcno}}"></td>
    <td class="standard_grid_table_yellow"> <input type="text" name="cdc_issued_at" value="{{$cm->cdc_issued_at}}"></td>
    <td class=""><input type="text" name="cdc_issued_on" class="dateentry" value="{{$cm->cdc_issued_on }}"></td>
    <td class="standard_grid_table_yellow"><input type="text" class="dateentry" name="cdc_expires_on" value="{{$cm->cdc_expires_on}}"></td>
    <td class=""><input type="text" name="passportno" value="{{$cm->passportno}}"></td>
    <td class="standard_grid_table_yellow"><input type="text" name="passport_issued_at" value="{{$cm->passport_issued_at}}"></td>
    <td class=""><input type="text" name="passport_issued_on" class="dateentry" value="{{$cm->passport_issued_on}}"></td>
    <td class="standard_grid_table_yellow"><input type="text" name="passport_expires_date" class="dateentry" value="{{$cm->passport_expires_date}}"></td>
    <td class=""><input type="text" name="presea_cert" value="{{$cm->presea_cert}}"></td>
    <td class="standard_grid_table_yellow"><input type="text" name="last_dna_date" value="{{$cm->last_dna_date}}"></td>
    <td class=""><input type="text" name="last_dna_test_result" class="dateentry" value="{{$cm->last_dna_test_result}}"></td>
    <td class="standard_grid_table_yellow"><input type="text" name="address_medical_center" value="{{$cm['centre']['centre_name']??''}}"></td>
</tr>
@endforeach

解决方案 #

1. 处理空日期值 #

数据库日期字段无法接收空字符串，需将CSV中的空值转为null，或跳过空值保留原有数据。封装处理函数：

function processDateValue($value) {
    return empty(trim($value)) ? null : $value;
}

赋值时调用该函数处理所有日期字段：

$update['birth_date'] = processDateValue($data[10]);
$update['cdc_issued_on'] = processDateValue($data[14]);
$update['cdc_expires_on'] = processDateValue($data[15]);
$update['passport_issued_on'] = processDateValue($data[18]);
$update['passport_expires_date'] = processDateValue($data[19]);
$update['last_dna_date'] = processDateValue($data[21]);

2. 统一日期格式转换 #

CSV中的日期格式（如01-01-2005）可能不符合数据库要求的YYYY-MM-DD格式，需转换格式：
更新处理函数，增加格式转换逻辑：

function processDateValue($value) {
    $trimmedValue = trim($value);
    if (empty($trimmedValue)) {
        return null;
    }
    // 解析常见日期格式并转换为标准格式
    $date = DateTime::createFromFormat('d-m-Y', $trimmedValue);
    if (!$date) {
        $date = DateTime::createFromFormat('Y-m-d', $trimmedValue);
    }
    return $date ? $date->format('Y-m-d') : null;
}

3. 提前验证数据有效性 #

在更新前验证日期字段，拦截无效值：

// 循环内赋值后添加验证
$validator = Validator::make($update, [
    'birth_date' => 'nullable|date',
    'cdc_issued_on' => 'nullable|date',
    'cdc_expires_on' => 'nullable|date',
    'passport_issued_on' => 'nullable|date',
    'passport_expires_date' => 'nullable|date',
    'last_dna_date' => 'nullable|date',
]);

if ($validator->fails()) {
    // 可根据需求记录错误或返回提示
    continue; // 跳过当前行，继续处理其他数据
}

$crewmember->update($validator->validated());

4. 保留原有数据（可选） #

若希望CSV空值不覆盖数据库原有数据，仅在CSV有值时更新对应字段：

// 示例：处理birth_date
$birthDate = trim($data[10]);
if (!empty($birthDate)) {
    $update['birth_date'] = processDateValue($birthDate);
}
// 其他日期字段同理处理

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