CODEWITHSUNDEEP
phpmysqldateformatphpexcel
pam
pam

Reputation: 13

Using PHPExcel to import contents of excel file into MySQL database (format problems)

I'm importing the contents of an Excel 2007 file into a MySQL database but the date field in the spreadsheet is a custom date format (dd-mmm-yy). I cannot change this to text because a client will be uploading it so it is not feasible. When I import the contents of the file it inserts the date as a number e.g. 40978 instead of 12-Jan-09. I know that changing the database table format of the field will have no effect as its excels formatitng but does anyone know a way around this? Without changing the format of the spreadsheet?

Upvotes: 1

Views: 4389

Answers (2)

Mark Baker
Mark Baker

Reputation: 212412

Use PHPExcel's built in conversion methods like PHPExcel_Shared_Date::ExcelToPHP() or PHPExcel_Shared_Date::ExcelToPHPObject() to convert the date values to a PHP/Unix timestamp or a PHP DateTime object respectively.

You can then format this to a yyyy-mm-dd format string using the appropriate PHP date() or $DateTimeObject->format() functions.

EDIT

$excelDateValue = 40978;

$PHPTimeStamp = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
echo $PHPTimeStamp,'<br />';
echo date('Y-m-d',$PHPTimeStamp),'<br />';

or

$excelDateValue = 40978;

$PHPDateTimeObject = PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);
echo $PHPDateTimeObject->format('Y-m-d'),'<br />';

Incidentally An Excel datestamp of 40978 is 10th March 2012 (based on the Excel Windows 1900 Calendar) or 11th March 2016 (based on the Excel Mac 1904 calendar). 12-Jan-09 would be an Excel timestamp of 39825 (based on the Excel Windows 1900 Calendar).

Upvotes: 4

mlewis54
mlewis54

Reputation: 2380

Use code thar will convert the string date into a mysql formatted date. Something like:

$ma=array("jan"=>"-01-","feb"=>"-02-","mar"=>"-03-", etc );
$month=$ma[strtolower(substr($exceldate,3,3))];
$newdate="20".substr($exceldate,7,2).$month.substr($exceldate,0,2);

Upvotes: 0

Related Questions