alamin dawan alamin dawan - 4 months ago 42
SQL Question

how to upload date from excel to mysql ? (using php)

I am trying to upload few data from excel to mysql i am using php. When i upload date 5/14/2015 it could not upload properly. It went this format 1970-01-01. I am very tired to search in google.

Here is my code.

if ($ext == 'xlsx' || $ext == 'xls') {
try {
$inputFileType = PHPExcel_IOFactory::identify($target_path);
$objReader = PHPExcel_IOFactory::createReader($inputFileType,'CSV');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($target_path);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($target_path, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
}else{
//In case of CSV
if (($handle = fopen($target_path, "r")) !== FALSE) {
while (($data = fgetcsv($handle)) !== FALSE) {
$csvData[] = $data;
}
fclose($handle);
$highestRow = count($csvData);

}
}

$rowData = array();
for ($row = 2; $row <= $highestRow; $row++) {
if ($ext == 'xlsx' || $ext == 'xls') {
// Read a row of data into an array
$test = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$rowData[] = $test[0];

}
}



if ($ext == 'csv') {
$rowData = $csvData;
}
$variable = array();
$i = 0;
$targetArr = array();
$error = '';
foreach($rowData as $item){
if($item[0] !='' && $item[1] !='' && $item[2] !=''):
$variable[$i]['Customer'] = array();
$variable[$i]['Customer']['msisdn'] = $item[0];
$variable[$i]['Customer']['csactivated'] = date('y-m-d',strtotime($item[1]));
$variable[$i]['Customer']['csdeactivated'] = date('y-m-d',strtotime($item[2]));
//$targetArr[] = $variable;
$i++;
else:
$count =+1;
$error .= 'Skip this Row'.$i++.'<br>';
continue;
endif;
}


i got date like 42104.592743056 when i import excel though it is not my date format .........and mysql insert like 1970-01-01

Answer

As you're telling PHPExcel to ignore any formatting in the spreadsheet when you load it ($objReader->setReadDataOnly(true);), such as the date/time formatting, you'll only be able to get MS Excel's internal timestamp value. You'll need to call PHPExcel's built-in functions to convert date vaues manually:

Either:

PHPExcel_Shared_Date::ExcelToPHPObject(42104.592743056);

to convert that MS Excel date/time value to a PHP DateTime object

or

PHPExcel_Shared_Date::ExcelToPHP(42104.592743056);

to convert MS Excel value to a Unix timetamp

And then you can use the appropriate PHP functions to format it however you like


Alternatively, don't suppress the formatting information when you load the spreadsheet, and tell rangeToArray() to apply the formatting

Comments