Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I'm trying to import my csv but I'm getting error on date rows using SpartnerNL/Laravel-Excel
The separation symbol could not be found Unexpected data found. Trailing data
here's my code
return new Statement([
'reading_date' => \Carbon\Carbon::createFromFormat('m/d/Y', $row['billdate']),
'due_date' => \Carbon\Carbon::createFromFormat('m/d/Y', $row['duedate']),
]);
in my csv:
billdate: 10/15/2022
duedate: 10/30/2022
Schema:
Schema::create('statements', function (Blueprint $table) {
$table->bigIncrements('id');
$table->date('reading_date')->nullable();
$table->date('due_date')->nullable();
});
Maybe its an integer. That is how the built ind date type in excel is stored. I use this to convert it
$date = $row['date']
if (is_numeric($date)) {
$date = ($date - 25569) * 86400;
}
$date = Carbon::parse($date)->toDateString()
If that isnt the case, try just using parse
reading_date' => \Carbon\Carbon::parse($row['billdate']),
@Sinnbeck i tried this and throws same error
I tried another approach but still throws invalid datetime format
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '10/15/2022' for column
mubsdb.statements.reading_dateat row 1
INSERT INTO
`statements` (
`reading_date`
)
VALUES
(
10 /15 /2022
),
Your SQL is indeed not valid SQL
What is you trim the input first?
$output = \Carbon\Carbon::createFromFormat('m/d/Y', trim($row['billdate']))->toDateString();
And try storing the Y-m-d format for SQL:
return new Statement([
'reading_date' => \Carbon\Carbon::createFromFormat('m/d/Y', trim($row['billdate']))->toDateString(),
'due_date' => \Carbon\Carbon::createFromFormat('m/d/Y', trim($row['duedate']))->toDateString(),
]);
@click tried your suggestion, still throws error
The separation symbol could not be found Unexpected data found. Trailing data
@jlrdw still same error
The separation symbol could not be found Unexpected data found. Trailing data
@krutsednar can you show exactly what your CSV content is? or a screenshot.
BillDate | DueDate
10/03/2022 | 10/18/2022
10/03/2022 | 10/18/2022
10/03/2022 | 10/18/2022
10/03/2022 | 10/18/2022
10/03/2022 | 10/18/2022
@krutsednar Use YYYY-MM-DD format for storing a date.
@jlrdw same error still. I don't know what I'm missing
Illuminate\Database\QueryException PHP 8.1.6 9.37.0
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '10/18/2022' for column `mubsdb`.`statements`.`due_date` at row 1
INSERT INTO
`statements` (`due_date`, `reading_date`)
VALUES
( 10 / 18 / 2022, 10 / 03 / 2022),
(
10 / 18 / 2022,
10 / 03 / 2022
),
( 10 / 18 / 2022, 10 / 03 / 2022),
( 10 / 18 / 2022, 10 / 03 / 2022),
(10/18/2022, 10/03/2022), (10/18/2022, 10/03/2022), (10/18/2022, 10/03/2022),
DB:
Name Type Null Default
reading_date date Yes NULL
due_date date Yes NULL
return new Statement([
'account_number' => $row['accountno'],
'account_name' => $row['name'],
'reading_date' => \Carbon\Carbon::createFromFormat('m/d/Y', $row['billdate']),
'due_date' => \Carbon\Carbon::createFromFormat('m/d/Y', $row['duedate']),
]);
this worked
'reading_date' => $row['billdate'] ? \Carbon\Carbon::createFromFormat('m/d/Y', $row['billdate'])->format('m/d/Y') : null,
'due_date' => $row['duedate'] ? \Carbon\Carbon::createFromFormat('m/d/Y', $row['duedate'])->format('m/d/Y') : null,
Please or to participate in this conversation.