Convert Date in Excel file for Upload into MySQL Using PHPExcel_Reader

Opeyemi AbdulRasheed
Opeyemi AbdulRasheed used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
You can use the DateTime::createFromFormat() to convert your Excel date into a PHP DateTIme object. You can then format that object into the correct format to use in mysql (yyyy-mm-dd)

$date_of_birth = DateTime::createFromFormat("d-m-Y", $Row[4]);

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");
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Either would work :)
Most Valuable Expert 2018
Distinguished Expert 2018

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)
Most Valuable Expert 2018
Distinguished Expert 2018

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.
Most Valuable Expert 2018
Distinguished Expert 2018

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
Most Valuable Expert 2018
Distinguished Expert 2018

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial