0

I was trying to import csv file using Laravel-Excel 3.1. In the csv file have a date column and it's format is 'dd/mm/yy', example - "24/07/20". I am using ToModel method. How can I save intu the database in format of "yyyy/mm/dd", exaple - "2020/07/24". My code is as followed.

class DeviceDataImport implements ToModel, WithHeadingRow
{
    private $counterNumber;

    public function __construct($counterNumber)
    {
        HeadingRowFormatter::default('none');
        $this->counter = $counterNumber;
    }

    public function model(array $row)
    {
       return new DeviceData([
            'counter_id'        => $this->counter,
            'date'              => $row['DATE'],
            'amount'            => $row['AMOUNT'
    }
}
Pallab
  • 145
  • 1
  • 9

4 Answers4

0

Try this...

'date' => date('Y-m-d', strtotime($row['DATE'])),

Read more about strtotime function

Rajen Trivedi
  • 1,225
  • 2
  • 5
  • 10
0

Hi you can create a date instance using Carbon, as thus you should have

DeviceData::create([
            'counter_id' => $this->counter,
            'date'  => Carbon::createFromFormat('Y/m/d', $row['DATE']),
            'amount' => $row['AMOUNT']
           ]);

You might want to use validations incase the date is other wise but be sure it is of that format

Again I don't know if counter is what I am thinking but it can be gotten from the import see - https://docs.laravel-excel.com/3.1/imports/chunk-reading.html#keep-track-of-the-row-number

Hope this helps thanks

KyleK
  • 4,643
  • 17
  • 33
Mua Rachmann
  • 109
  • 2
  • 7
  • It's showing Unexpected data found. Unexpected data found. Data missing. When I am doing ```dd(Carbon::createFromFormat('yyyy/mm/dd', '24/07/20'));``` it's result is same. – Pallab May 21 '21 at 09:48
  • `Carbon::createFromFormat('Y/m/d', $row['DATE'])` is the correct syntax, see https://www.php.net/manual/en/datetime.createfromformat.php – KyleK May 21 '21 at 10:08
  • It's saving "0024-07-20" – Pallab May 21 '21 at 11:29
  • You need to parse the correct format, as per your question you mentioned this `yyyy/mm/dd` so it should work. Unless your date is not in that format. – Mua Rachmann May 21 '21 at 11:39
  • You can use artisan tinker and play with the dates to see the correct format. Else look here to see yours - https://www.w3schools.com/php/func_date_date_format.asp – Mua Rachmann May 21 '21 at 11:44
0

Carbon's createFromFormat() requires you to define the input format.

In this case, the input format for 24/07/20 is d/m/y, not what is being used in the other answer.

See PHP: DateTime::format for a listing of parameters.

'date' => Carbon::createFromFormat('d/m/y', $row['DATE']),

If you do not want to save the model using a Carbon instance, you can format the date as a string. This is where you would define a specific output format.

'date' => Carbon::createFromFormat('d/m/y', $row['DATE'])->format('Y/m/d'),
matticustard
  • 4,850
  • 1
  • 13
  • 18
0

Finally, I've approached a manual method as follows.

$year = Carbon::createFromFormat('d/m/Y', $row['DATE'])->year;
$month = Carbon::createFromFormat('d/m/Y', $row['DATE'])->month;
$day = Carbon::createFromFormat('d/m/Y', $row['DATE'])->day;

And after that I had concat and reformat the date manually as per my project.

'date' => Carbon::createFromFormat('d/m/Y', $day . '/' . $month . '/' . 20 . '' . $year),

I think it's not a very good solution, but will work for the next many years.

Thanks to all for contributing ideas.

Pallab
  • 145
  • 1
  • 9