使用PHP导入到单个列的Excel到MySQL
I'm trying to upload an excel file into my mysql database using php, but I have two problems: the first one, i'm getting a "Notice: Undefined offset:" warning for each row of the .csv file, and the second problem, is that it's importing all the three columns of the .csv file into a single one of the db.
My code is the following:
<?php
if(isset($_POST["Import"]))
{
$conexion=mysql_connect("localhost","root","") or die("Problemas en la conexion");
mysql_select_db("kontor",$conexion) or die("Problemas en la seleccion de la base de datos");
echo $filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
$count = 0;
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
$count++;
if($count>1){
mysql_query("INSERT into stock (nombre,prneto,descr) values ('$emapData[0]','$emapData[1]','$emapData[2]')", $conexion)
or die("Problemas en el select".mysql_error());
}
}
fclose($file);
echo 'Archivo importado';
//header('Location: index.php');
}
else
echo 'Formato de archivo incorrecto';
}
?>
Just take a look at the csv-file ... what separator and enclosure are used?
Excel often uses ; and " for separator and enclosure.
most of the time one of these will do the job:
while (($emapData = fgetcsv($file, 10000, ";", '"')) !== FALSE)
// or
while (($emapData = fgetcsv($file, 10000, ",", '"')) !== FALSE)
Excel usually doesn't like to consistently quote CSV column values; it only does so when it thinks its necessary (at least with some versions of excel). For this reason I usually don't recommend using CSV generated by Excel and opt to just read the excel file in its native format with PHPExcel
// this is the older version of excel for .xls
// if the file is xlsx you would use PHPExcel_Reader_Excel2007()
$objReader = new PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($_FILES["file"]["tmp_name"]);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
// you should also be using PDO or mysqli for your db interactions
// NOT mysql - this example will use pdo
$db = new PDO($dsn, $user, $pass, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
try {
// presumebaly you want this operation to be atomic
// ie. if any one row fails don't insert any of the rows
// so we will use a transaction
$db->beginTransaction();
// prepare the query
$stmt = $db->prepare('INSERT into stock (nombre,prneto,descr) values (?,?,?)');
foreach ($sheetData as $row) {
// execute the insert for a row of csv
$stmt->execute(array_values($row));
}
// attempt to commit the transaction
$db->commit();
} catch (Exception $e) {
// we had an error somewhere, roll back the transaction
$db->rollBack();
// retrhow the error
throw $e;
}