Maatwebsite/Excel import date just displays 0000-00-00

@tangtang I don't want to change the date format, I want the date format to change when I import it automatically.

@rdiyto

you can do that if this received_date data type in database is text or varchar but if the data type is date it's clear you need to follow mysql rules is yyyy-mm-dd for saving data, other than that type will be not accept in database.

update : seems like I missread your response

with the code in my response before.

the date format just work when you import it. but does not change the file excel itself. even you export it, it will the excel format again (if you don't specify it first)

@tangtang Where should I put your code? It doesn't matter if the date format follows the rules of MySQL.

@rdiyto

 public function model(array $row)
    {
		$date = $this->transformDateTime($row['expired'], 'Y-m-d'); // new code
        return new Vouchers([
            'voucher_code' => $row['voucher_code'],
            'voucher_value' => $row['voucher_value'],
            'batch' => $row['batch'],
            'expired' => $date,
            'received_date' => $row['received_date'],
            'customer_address' => $row['customer_address'],
            'status' => $row['status'],
        ]);
    }

I update the code, seems like the first code is wrong formated.

@tangtang Get an error:

Carbon\Exceptions\InvalidFormatException
Not enough data available to satisfy format

@rdiyto the VERY FIRST thing you need to do is check if excel thinks this is a date type or a text type, for which you will need to open the file in excel

@rdiyto

try with this code, choose one

$date = Carbon::createFromFormat('m/d/Y', $row[expired])->toDateString();

$date = $this->transformDateTime($row['expired'], 'Y-m-d');

this error

Carbon\Exceptions\InvalidFormatException, Not enough data available to satisfy format

you need to make sure this date from excel is in m/d/Y format. try to dd it first if the result of dd is 12/31/2021 than the format is m/d/Y

if the dd result is 31/12/2021 than format it with Y/m/d in carbon.

second code not need to reformated

@Snapey Excel is a date format, and MySQL is a date type.

@rdiyto

 $date= ExcelDate::excelToDateTimeObject($row['received_date'])->format('Y-m-d');

@tangtang Where do I put the dd code?

@Snapey Getting error:

Error
Class "App\Imports\ExcelDate" not found

@rdiyto You have to import the class

use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;

@rdiyto

in your model function

dd($row)

before return new Vouchers code

@Snapey Getting error:

TypeError
floor(): Argument #1 ($num) must be of type int|float, string given

@tangtang This is the result:

"expired" => "31/12/2024"

I've tried it using this code:

$date = Carbon::createFromFormat('m/d/Y', $row[received_date])->toDateString();

But doesn't work and getting this error:

Carbon\Exceptions\InvalidFormatException
Not enough data available to satisfy format

@rdiyto FULL error message please

@rdiyto

so your type date is not m/d/y like what you say in your question.

in your excel is m/d/y

when you dd it, the format go to d/m/y

so try to change the carbon to Y-m-d

try this code

$date = Carbon::createFromFormat('Y-m-d', $row[expired])->toDateString();
'expired' =>$date,
// other code here

@rdiyto This is never going to work if the column contains an excel date. You have to parse it as such

@tangtang This doesn't work. I have changed the format to Y-m-d, but this error message appears:

TypeError
floor(): Argument #1 ($num) must be of type int|float, string given

@rdiyto

would you like to share the latest code from this controller (all) here ? seems like there a little missunderstanding from your first code in your question.

@tangtang This is import code:

<?php

namespace App\Imports;

use Carbon\Carbon;
use App\Vouchers;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class VouchersImport implements ToModel, WithHeadingRow, WithBatchInserts, WithChunkReading
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */

    private function transformDateTime($value, $format = 'Y-m-d')
    {
        try {
            return Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value))->format($format);
        } catch (\ErrorException $e) {
            return Carbon::createFromFormat($format, $value);
        }
    }

    public function model(array $row)
    {
        //dd($row);
        return new Vouchers([
            'voucher_code' => $row['voucher_code'],
            'voucher_value' => $row['voucher_value'],
            'batch' => $row['batch'],
            'expired' => $this->transformDateTime($row['expired']),
            'received_date' => $row['received_date'],
            'customer_address' => $row['customer_address'],
            'status' => $row['status'],
        ]);
    }

    public function batchSize(): int
    {
        return 1000;
    }

    public function chunkSize(): int
    {
        return 1000;
    }
}

This is controller:

<?php

namespace App\Http\Controllers;

use App\Vouchers;
use App\Stores;
use App\Exports\VouchersExport;
use App\Imports\VouchersImport;
use App\Http\Controllers\Controller;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Input;
use Illuminate\Http\Request;
use DB;

class VouchersController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */

    //Show data
    public function vcrdata()
    {
        $vouchers = Vouchers::orderby('id_voucher', 'DESC')->get();
        $voucherbelumdibayar = Vouchers::where('status', 'Belum dibayar')->get();
        $voucheravailable = Vouchers::where('status', 'Available')->get();
        $vouchersudahdigunakan = Vouchers::where('status', 'Sudah digunakan')->get();
        $vouchertotalbelumdibayar = Vouchers::where('status', 'Belum dibayar')->get('voucher_value');

        $batchsummary = Vouchers::select('batch', DB::raw('count(*) as batch_count'), DB::raw('sum(voucher_value) as batch_sum'))->where('status', 'Available')->groupBy('batch')->get();

        $monthsummary = Vouchers::select('received_date', DB::raw('count(*) as month_count'), DB::raw('sum(voucher_value) as month_sum'), DB::raw('EXTRACT(MONTH from received_date) as month'), DB::raw('EXTRACT(YEAR from received_date) as year'))->where('status', 'Sudah digunakan')->groupBy('month', 'year')->get();

        return view('Pages.Voucher.vcrdata', compact('vouchers', 'voucherbelumdibayar', 'voucheravailable', 'vouchersudahdigunakan', 'vouchertotalbelumdibayar', 'batchsummary', 'monthsummary'));
    }

    public function exportvouchers()
    {
        return Excel::download(new VouchersExport, 'exportvouchers.xlsx');
    }

    public function importvouchers(Request $request)
    {
        $file = $request->file('file');
        $namaFile = $file->getClientOriginalName();
        $file->move('DataVouchers', $namaFile);

        Excel::import(new VouchersImport, public_path('/DataVouchers/' . $namaFile));
        return redirect('vcrdata')->with('toast_success', 'All good!');
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function vcrused($id)
    {
        $vouchers = Vouchers::findorfail($id);
        $stores = Stores::all();
        return view('Pages.Voucher.vcrused', compact('vouchers', 'stores'));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function vcrupdate(Request $request, $id)
    {
        $vouchers = Vouchers::findorfail($id);
        $vouchers->update($request->all());
        return redirect('vcrdata')->with('toast_success', 'All good!');
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function vcrdelete($id)
    {
        $vouchers = Vouchers::findorfail($id);
        $vouchers = Vouchers::where('id_voucher', $id)->delete();
        return redirect('vcrdata')->with('toast_success', 'All good!');
    }
}

@rdiyto

try this new function

private function transformDateTime($value, $format = 'Y-m-d')
    {
       return \DateTime::createFromFormat('d/m/Y', $excelDate)->format($format);
    }

this format d/m/Y you can look on this $row['expired'] value (you can dd it to make sure what exactly this value is)

see if the value represent the d/m/y or m/d/Y format, just edit this format as what this expired value format represent.

@tangtang So I change the existing function to yours?

@rdiyto

yes, try with the new function, this function is return the date format which supported to mysql column date type.

you can edit this function as you need, but make sure the return of this function is always a date with format yyyy-mm-dd

before you edited this function, try it first. if work, than you can add another logic inside, like try catch or other method you like to handle if the data is not date type

@tangtang $excelDate error when I put to vscode.

@rdiyto

typo, edit $excelDate to $value

@tangtang Ok it works. Thank you

@ManoMahe When working with excel dates, you MUST use the phpspreadsheet conversion method to bring it into the right format or info a format that Carbon can understand.

use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;

$datecol = ExcelDate::excelToDateTimeObject($datecol)->format('Y-m-d');

@Snapey Yeah, I understand. Sorry I missed adding a clear example of how I created the variable $excelDate

use \PhpOffice\PhpSpreadsheet\Shared\Date;

public function collection(Collection $rows)
{
    foreach ($rows as $key => $col)
    {
        $excelDate = Date::excelToDateTimeObject($col[5])->format('Y-m-d H:i:s');
    }
}

My question here is if the user has amount values instead of date values at $col[5] also allows me to import with the wrong date in DB. I couldn't catch this exception to inform the user as this format was wrong before importing the data.

@ManoMahe

if user kept the amount like numeric value instead of the date in column is supposed to be date, even if the column is not supposed to contain date values, the column still can be attempted to be converted into dates (assume the value is represent numeric only) and potentially leading to incorrect data.

@tangtang Yes... I'm asking for a solution for this scenario only. I would like to catch this mistake made by the user before importing 1 million incorrect data into the DB and inform them to correct the Excel

@ManoMahe

well, until now (but just for me) I never find easiest way how to do this validation from code only.

simple way to do is, train the user to make sure this excel column need to fill with date.

just like the html form, if you're not using input type="date" but using input type="text" to save the datetype, you need to inform user what will be format (dd-mm-yyyy, mm-dd-yyyy or dd mm yyyy or another) for this input before click the submit button.

@ManoMahe You can do this if you can set bounds for the correct value of date/time.

use try-catch around the conversion, and if converts ok, then check the bounds of the date returned

@Snapey I didn't get it clearly, what are the bounds? Because the user may have the data for the past 5 years along with current & upcoming years, then how we can set bounds in this case. So can you please share here any examples for this scenario.

@ManoMahe for instance your date should be between 01-01-2019 and 01-01-2027

if its any other date then its invalid

@Snapey Got it, but this also leads to false import. If I set the bounds as you mentioned and try to import the Excel for the following scenario also have a wrong data import. For example, the user Excel has an amount column with a data/value of 144444. The Date::excelToDateTimeObject() - function is capable of converting this type of number into a date (let us assume the converted amount into date - 20/02/2023) similarly, which allows us to finish the import with the wrong data, right?

@ManoMahe of course but you cant be responsible for bad data? it looks like a date, you are expecting a date, what else can you do?

@Snapey Okay, it's still a mystery to solve with Maatwebsiteexcel. Another issue I'm facing with this is US date format conversion. If the Excel data contains a US date format like m/d/Y, it takes the month as date and the date as month.

Example - If the Excel file date column has a value as 02/28/2024, convert this format into a wrong date because Maatwebsiteexcel considers the first part 02(actually it's a month) as date and then makes the wrong date.

@ManoMahe You need to be clear if the cell contains a date or just a string field with something that looks like a date entered into it. In which case you need to adopt the format used by the spreadsheet's creator.

@Snapey Yeah, that's the logic I'm stuck with on how to go further... Can you please share any related examples if you have them?