Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

ycsm's avatar
Level 1

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

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.

0 likes
2 replies
LaryAI's avatar
Level 58

The issue seems to be with PHP interpreting the value as a number instead of a string. To force PHP to treat the value as a string, you can wrap it in quotes in the CSV file. However, the OP has already tried this and it did not solve the issue.

Another solution could be to cast the value to a string explicitly in the code. For example, if the value is being stored in a variable called $value, you can cast it to a string using (string)$value. This should ensure that PHP treats the value as a string.

If the issue persists, it could be a problem with the database configuration. The column in the database should be set to a varchar type to ensure that it can store string values. If the column is set to a numeric type, it could be causing the issue.

Code example:

// Cast the value to a string explicitly
$value = (string)$value;
ycsm's avatar
ycsm
OP
Best Answer
Level 1

Turns out, from a reply on github:

"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"

This fixed it:

use PhpOffice\PhpSpreadsheet\Cell\StringValueBinder;

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

Please or to participate in this conversation.