Upon importing a CSV file, any rows containing "E2" are being entered into the database as 00

[woottonn]

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.

[patrickbrouwers]

PhpSpreadsheet detected 11E2 as a number (11^2) so it makes sense it inserts as 1100. If you want to prevent that, you'll have to tell PhpSpreadsheet that that column is not a number. You can do so via a value binder, or if you want everything to be a string you can use the stringvaluebinder

[woottonn]

You sir, are a life saver. Thank you Patrick! This did the trick:

use PhpOffice\PhpSpreadsheet\Cell\StringValueBinder;

class StudentsImport extends StringValueBinder implements WithCustomValueBinder, ToModel, WithHeadingRow, WithUpserts
{