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