CODEWITHSUNDEEP
phpmysqlphpexcel
Tyas
Tyas

Reputation: 73

Read date value from import Excel in PHP

I've problem with read date value from import value (Excel) to mysql database. i've code as below :

$dataexcel[$i-3]['date_edit'] = date('m/d/Y', strtotime(trim($data['cells'][$i][25])));

i tried use strtotimeto define value from excel, but i've problem to save it into database, if my value in excel with excel date format is 1/1/2018 (it read as 1 Jan 2018, English time format), after i used my code above, it saved become 1970-01-01 it means 1 Jan 1970.

in another example, i tried another input with date 2/1/2018, it saved into database as 2018-02-02.

from my samples above, in first there missmatch problem with year, then and in the 2nd sample missmatch problem come from date,

so how to declare date to solve my problem, if i want to save date format with simple way? if there any advice , please, thanks...

Upvotes: 1

Views: 4087

Answers (4)

S M Jadhav
S M Jadhav

Reputation: 43

You can convert the excel date to mysql date like below I did.

//Your input date
$input_date = $sheet->getCellByColumnAndRow(1,$i)->getValue();

$excel_date = $input_date;  //here is that excel value 41621 or 41631

//Convert excel date to mysql db date
      $unix_date = ($excel_date - 25569) * 86400;
      $excel_date = 25569 + ($unix_date / 86400);
      $unix_date = ($excel_date - 25569) * 86400;
      //echo gmdate("Y-m-d", $unix_date);

//Insert below to sql
$added_date = gmdate("Y-m-d", $unix_date);

Upvotes: 1

karisma
karisma

Reputation: 156

From your comment answer, you can modification some code like below :

$date = str_replace("/", ".", $data['cells'][$i][25]);
$dates = date("Y-m-j", strtotime($date));
$dataexcel[$i-3]['date_edit']  = $dates;

$date still pick up from value without used strotime, so date format didn't read correctly...

Upvotes: 1

Ben Yitzhaki
Ben Yitzhaki

Reputation: 1416

keep it simple. use a defined dateformat when reading the file (the dateformat can be provided as a parameter in case it needs to be adjustable). then, read the date in the following way instead of just using strtotime (taken from php.net's example):

$date = DateTime::createFromFormat('j-M-Y', '15-Feb-2009');

relevant example to your code, using the previous $date var:

$dataexcel[$i-3]['date_edit'] = $date->format("y-M-Y");

more info - http://php.net/manual/en/datetime.createfromformat.php

Upvotes: 0

Sanjit Bhardwaj
Sanjit Bhardwaj

Reputation: 893

A simple function will do this thing

    $date = str_replace("/", ".", $data['cells'][$i][25]);// replace the / with .
    $date = date("Y-m-d", strtotime($date));
    $dataexcel[$i-3]['date_edit']  = $date;

And in the database the default date format is YYYY-MM-DD , so it save correctly to the db

Upvotes: 0

Related Questions