Level 75
Work out a getter and setter to do the conversion correctly.
Nov 15, 2023
10
Level 1
Date insert issue when data upload from excel using maatwebsite/excel in laravel
Date insert issue when data upload from excel using maatwebsite/excel in laravel. Follwing error shows -
Carbon\Exceptions\InvalidFormatException: The separation symbol could not be found in file D:\lara-agentApp\vendor\nesbot\carbon\src\Carbon\Traits\Creator.php on line 690
My excel file date format is: 10-Nov-23
Level 6
can you show the dd result of this date field ?
and take a look this another thread
https://laracasts.com/discuss/channels/laravel/maatwebsiteexcel-import-date-just-displays-0000-00-00
seems like similar with your case.
Level 1
Please check below dd result-
array:7 [ // app\Imports\VesselImport.php:49 "vessel" => "USER VSL01" "voyage" => "U123s" "vessel_flag" => "LR" "vessel_departure_date" => 44928 "vessel_arrival_date" => 44932 "vessel_pol" => "LKCMB" "vessel_pod" => "BDCGP" ]
Level 6
is your date data from this array ?
"vessel_departure_date" => 44928 "vessel_arrival_date" => 44932
in this vessel_arrival_date and vessel_departure_date the value is not represent the date type.
thats why the carbon send error respons.
can you show this full import code ?
Level 1
@tangtang full import Code as follows -
Level 1
I use below code for date upload -
'vessel_departure_date' => Carbon::createFromFormat('d-M-Y', $row['vessel_departure_date'])->toDateTimeString(), // D2
'vessel_arrival_date' => Carbon::createFromFormat('d-M-Y', $row['vessel_arrival_date'])->toDateTimeString(), // E2
Level 6
to make sure again show the dd result from this $row['vessel_arrival_date'] and $row['vessel_departure_date']
and with this toDateTimeString will result the time format like Y-m-d H:i:s
you have the time value in your date excel column ?
and what your vessel_departure_date and vessel_arrival_date data type in database ? date only or datetime ?
Level 1
@tangtang dd result like beow - array:7 [ // app\Imports\VesselImport.php:49 "vessel" => "USER VSL01" "voyage" => "U123s" "vessel_flag" => "LR" "vessel_departure_date" => 44928 "vessel_arrival_date" => 44932 "vessel_pol" => "LKCMB" "vessel_pod" => "BDCGP" ]
Level 6
seems like the vessel_departure_date and vessel_arrival_date are being read as numeric values (possibly excel date serial numbers) rather than as date strings.
modify your code like this
'vessel_departure_date' => Carbon::createFromTimestamp(($row['vessel_departure_date'] - 25569) * 86400)->toDateTimeString(),
'vessel_arrival_date' => Carbon::createFromTimestamp(($row['vessel_arrival_date'] - 25569) * 86400)->toDateTimeString(),
Level 1
@tangtang Now working well. Thanks Lot!!!!
Please or to participate in this conversation.