Import Excel list into database DATEFIELD PROBLEM

Topics: Developer Forum, User Forum
Mar 17, 2009 at 9:23 PM
Edited Mar 17, 2009 at 10:50 PM

Hey,

I want to import a releaselist into a mysql database.I have a problem with the date in the excel sheet.
When i import it the value is not a date any more but normal integer.
How can i solve this problem?
I need belgium date notation DD/MM/YYYY

The datafield is always located in column C

ini_set('include_path', ini_get('include_path').':/srv/www/htdocs/intranet/frsbe/life/includes/Classes/');
include 'PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load($file);
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow(); // e.g. 10

$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = 2 ;
for ($row = 2; $row <= $highestRow; ++$row) {
$imput=array();
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
$value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
array_push($imput,$value);
}

$sql = "INSERT INTO Pur_Releaselist VALUES ('','$imput[0]','$imput[1]','$imput[2]', '$imput[3]','$type','$DATE','$UserName')";
echo $sql;
$result = mysql_query($sql)
or die('Invalid: ' . mysql_error());
echo "<META HTTP-EQUIV=\"Refresh\" Content=\"0; URL=?page=ADMIN&sub=RELEASELIST&dep=" . $dep . "\">";
exit;
}
unlink($file);
echo "The file is uploaded and processed [".$file."]";
}

Developer
Mar 17, 2009 at 10:09 PM
Here is what I would do. Let's assume that A1 holds the date: 2009-03-17

// get the raw value, number of days elapsed since 1. Jan 1900 (or 1904 with Excel Mac workbook)
$value = $sheet->getCell('A1')->getValue();
var_dump($value); // float(39889)

// convert to PHP time aka Unix time / Unix seconds
$time = PHPExcel_Shared_Date::ExcelToPHP($value);
var_dump($time); // int(1237248000)

// convert to formatted string using date(), make sure timezone is set to UTC
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set('UTC');
var_dump(date('d/m/y H:i:s', $time)); // string(17) "17/03/09 00:00:00"
date_default_timezone_set($saveTimeZone);

// alternative method using strftime()
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set('UTC');
var_dump(strftime("%Y-%m-%d %H:%M:%S", $time)); // string(19) "2009-03-17 00:00:00"
date_default_timezone_set($saveTimeZone);

Admitted, it's messy and we should see if we can improve the classes, the problem is that Excel, PHP, and MySQL all have different preferred ways of storing dates. And then there are also time zones to watch out for.

In addition to this, Excel on Windows uses 1900 based calendar while Excel on Mac is 1904 based. But PHPExcel_Shared_Date() fortunately inspects the workbook, so this will not give you trouble.

Stick to the suggestions above and you will be safe.