asked on 9/12/2018

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?

PHP Microsoft Excel Microsoft Office

Last Comment

Opeyemi AbdulRasheed

9/12/2018

ASKER CERTIFIED SOLUTION

Chris Stanyon

9/12/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Opeyemi AbdulRasheed

9/12/2018

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

9/12/2018

ASKER

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

Chris Stanyon

9/12/2018

Either would work :)

Chris Stanyon

9/12/2018

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

9/12/2018

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

9/12/2018

ASKER

when i removed the Heading:
...\upload_bulk_students.php:50:string '31/12/2001' (length=10)

Chris Stanyon

9/12/2018

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

9/12/2018

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

9/12/2018

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?

Opeyemi AbdulRasheed

9/12/2018

ASKER

Thank you so much sir

Chris Stanyon

9/12/2018

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

9/12/2018

ASKER

so for example how would you know whether "12/09/2018" is 12th September or 9th December !!

Point. Thanks sir