We help IT Professionals succeed at work.

Convert Date in Excel file for Upload into MySQL Using PHPExcel_Reader

91 Views
Last Modified: 2018-09-12
Hello Experts!

I'm using PHPExcel_Reader to import data into MySQL. One of the columns in Excel file is Date_Of_Birth.

I've tried many methods to no avail. I'm afraid, google search has not yielded any desirable result either.

In the script, I have:
$date_of_birth = "";
if(isset($Row[4])) {
   $date_of_birth = date('Y-m-d', strtotime($Row[4]));
}

Open in new window

In Excel file, the date format is: DD-MM-YYYY (31-12-1999). After upload, it reads: (1970-01-01). It appears it's not seen 31 as Day but Month.

In fact, when I used 11-12-2001  (December 11, 2001), I still got 2001-11-12 (November 12, 2001).

Please, how can I get through this irrespective of the date format in Excel file?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Now when you want to insert it into your DB, just use the format() method with the correct format string:

$date_of_birth->format("Y-m-d");
Sir, you mean something like this?
$query = "INSERT INTO ... (..., Date_Of_Birth) VALUES('"..."','".$date_of_birth->format("Y-m-d")."')";

Open in new window

or i should first use a variable like:
$dob = $date_of_birth->format("Y-m-d");

Open in new window

then:
$query = "INSERT INTO ... (..., Date_Of_Birth) VALUES('"..."','".$dob."')";

Open in new window

I got
Fatal error: Call to a member function format() on boolean in ...
in both cases
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Either would work :)
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. If you're getting a fatal error on boolean, then it means the call to createFromFormat() didn't work correctly.

Output some debugging steps along the way to make sure you have the correct info coming from your Excel file:

var_dump($Row[4]); // see what's coming from Excel. It has to be a string in the dd-mm-yyyy format

$date_of_birth = DateTime::createFromFormat("d-m-Y", $Row[4]); // create a new datetime object
var_dump($date_of_birth);

$dob = $date_of_birth->format("Y-m-d"); // format the datetime object
var_dump($dob);

$query = "...";

Open in new window

...\upload_bulk_students.php:50:string 'DATE OF BIRTH' (length=13)

...\upload_bulk_students.php:53:boolean false

( ! ) Fatal error: Call to a member function format() on boolean in ...\upload_bulk_students.php on line 55
when i removed the Heading:
...\upload_bulk_students.php:50:string '31/12/2001' (length=10)
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. $Row[4] contains the words 'DATE OF BIRTH' which you clearly can't convert into a DateTime object.

My guess is that you're trying to process the header row from the spreadsheet. You should be skipping that row.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. So the format you mention in your original question is wrong. It's not dd-mm-yyyy, but dd/mm/yyyy

You'll need to change the createFromFormat function to deal with that:

$date_of_birth = DateTime::createFromFormat("d/m/Y", $Row[4]); // create a new datetime object
Well, that did the trick. My problem is, what if a client downloaded the Excel file template to another system where the Time and Date format is quite different from the original setup in the template, does that mean I'll have to change the php code again?

I really want a permanent  solution so irrespective of the format in Excel file. Is that possible?
Thank you so much sir
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
No worries.

You'll have to standardise of the format that's in the file in order to parse it. If it's not in a known format, then you have no idea what the data will hold, so for example how would you know whether "12/09/2018" is 12th September or 9th December !!
so for example how would you know whether "12/09/2018" is 12th September or 9th December !!
Point. Thanks sir