Convert Date in Excel file for Upload into MySQL Using PHPExcel_Reader

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?
Opeyemi AbdulRasheedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
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:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Opeyemi AbdulRasheedAuthor Commented:
Now when you want to insert it into your DB, just use the format() method with the correct format string:

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

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

Open in new window

Opeyemi AbdulRasheedAuthor Commented:
I got
Fatal error: Call to a member function format() on boolean in ...
in both cases
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Chris StanyonWebDevCommented:
Either would work :)
Chris StanyonWebDevCommented:
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

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

$query = "...";

Open in new window

Opeyemi AbdulRasheedAuthor Commented:
...\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
Opeyemi AbdulRasheedAuthor Commented:
when i removed the Heading:
...\upload_bulk_students.php:50:string '31/12/2001' (length=10)
Chris StanyonWebDevCommented:
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.
Chris StanyonWebDevCommented:
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
Opeyemi AbdulRasheedAuthor Commented:
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?
Opeyemi AbdulRasheedAuthor Commented:
Thank you so much sir
Chris StanyonWebDevCommented:
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 !!
Opeyemi AbdulRasheedAuthor Commented:
so for example how would you know whether "12/09/2018" is 12th September or 9th December !!
Point. Thanks sir
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.