Avatar of Opeyemi AbdulRasheed
Opeyemi AbdulRasheed
 asked on

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?
PHPMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Opeyemi AbdulRasheed

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Opeyemi AbdulRasheed

ASKER
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

Opeyemi AbdulRasheed

ASKER
I got
Fatal error: Call to a member function format() on boolean in ...
in both cases
Chris Stanyon

Either would work :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Chris Stanyon

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

Opeyemi AbdulRasheed

ASKER
...\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 AbdulRasheed

ASKER
when i removed the Heading:
...\upload_bulk_students.php:50:string '31/12/2001' (length=10)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Stanyon

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 Stanyon

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 AbdulRasheed

ASKER
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Opeyemi AbdulRasheed

ASKER
Thank you so much sir
Chris Stanyon

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 AbdulRasheed

ASKER
so for example how would you know whether "12/09/2018" is 12th September or 9th December !!
Point. Thanks sir
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.