Change the format of 1st column of the imported .csv from "general" to "date" in php

Wednesday, November 4, 2015

I had a .xlsx file with 2 columns (date, number). I converted it to a .csv file.
I have a mysql table with 2 columns (date, number).



I am trying to load this file into my table in mysql.



The first column (date) in my .csv file is in a general format but I want it to be in a date format like : %Y-%m-%d.



I am using the following code to convert the .xlsx to .csv and then import it in the database:



            $fileType=PHPExcel_IOFactory::identify($path.$file.'.xlsx');
$objReader = PHPExcel_IOFactory::createReader("Excel2007");

$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($path.$file.'.xlsx');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save($path.$file.'.csv');

// Load csv to table
$sql = 'LOAD DATA LOCAL INFILE \'path'.$file.'.csv\' INTO TABLE '.$dbtable.'
FIELDS TERMINATED BY \',\'
ENCLOSED BY \'"\'
LINES TERMINATED BY \'\n\'
IGNORE 8 LINES
(@date, number)
set date=str_to_date(@date,\'%Y-%m-%d\')
';

$Db->query($sql);
echo $Db->error;


It works. My only problem is the format of the first column. I want to change it to a date format before loading the file into my table.

0 comments:

Post a Comment