I'm doing a CSV import with Laravel, one specific column is giving me grief. Here is one of the offending rows:
..."Smith, John",John,Smith,11E2,"06 Jun 2007",F,,No,No,No,...
The "11E2" is being entered as 1100 into my varchar column in mySql.
Here are some steps I tried to figure out where this issue is occurring:
https://i.imgur.com/kOekHKQ.png
At step one, I did a dump to see how the data was formatted, here is the result:
https://i.imgur.com/Yc244Ag.png
https://imgur.com/Z1fbCZL.png
Great - the data looks correct so far.
On step two, the import:
https://imgur.com/1k1Urte.png
I get:
https://imgur.com/wZrOG0C.png
Here is the StudentsImport file:
<?php
namespace App\Imports;
use App\Models\Student;
use App\User;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\WithUpserts;
use Maatwebsite\Excel\HeadingRowImport;
class StudentsImport implements ToModel, WithHeadingRow, WithUpserts
{
/**
* @return string|array
*/
public function uniqueBy()
{
return 'upn';
}
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
$current = Student::where('upn', $row['upn'])->first();
/*if($row['last_name']=="Waller"){
dd($row);
}*/
if(!$current){
$user = new Student();
}
else{
$user = $current;
}
//Get year from form group colum as arbor doesnt have some students with a year group
if(str_starts_with($row['registration_forms_this_academic_year'], '1')){
$row['year'] = substr($row['registration_forms_this_academic_year'], 0, 2);
}else{
$row['year'] = substr($row['registration_forms_this_academic_year'], 0, 1);
}
$user->first_name = $row['first_name'];
$user->last_name = $row['last_name'];
$user->year = $row['year'];
$user->date_of_birth = $row['date_of_birth'];
$user->registration_forms_this_academic_year = $row['registration_forms_this_academic_year'];
$user->sex = $row['sex'];
$user->looked_after_in_care_status = $row['looked_after_in_care_status'];
$user->ever_6_fsm_at_any_time_this_academic_year = $row['pupil_premium_eligible_at_any_time_this_academic_year'];
$user->eal_at_any_time_this_academic_year = $row['eal_at_any_time_this_academic_year'];
$user->coursesclasses = $row['coursesclasses'];
$user->upn = $row['upn'];
$user->student_number = $row['student_number'];
$user->ppm = $row['ppm'];
$user->school_history = $row['school_history'];
$code = null;
if($row['sen_status_for_today'][0]=="N"){
$code = "M";
}else{
$code = $row['sen_status_for_today'][0];
}
$user->send_status_code = $code;
$user->created_at = now();
$user->updated_at = now();
return $user;
}
}
I assume some conversion is happening here because PHP is presuming this is a number? I tried wrapping that column in the csv with quotes, but this did not solve the issue. Do I have to somehow force this as a string? If so, how would I go about doing that?
I pass the csv file itself to the import:
https://imgur.com/PgLX4MG.png
Everything imports successfully, apart from this.
I hope I was clear in my explanation.