如何将 Excel 文件从 PHP 导入 mysql 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21965255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Import Excel file into mysql Database from PHP
提问by user3342646
Lets say, i want to import/upload excel file to mysql from PHP
比方说，我想从 PHP 导入/上传 excel 文件到 mysql
My HTML is like below
我的 HTML 如下所示
<form enctype="multipart/form-data" method="post" role="form">
<div class="form-group">
<label for="exampleInputFile">File Upload</label>
<input type="file" name="file" id="file" size="150">
<p class="help-block">Only Excel/CSV File Import.</p>
</div>
<button type="submit" class="btn btn-default" name="Import" value="Import">Upload</button>
</form>
PHP code is like below
PHP代码如下
<?php
if(isset($_POST["Import"]))
{
//First we need to make a connection with the database
$host='localhost'; // Host Name.
$db_user= 'root'; //User Name
$db_password= '';
$db= 'product_record'; // Database Name.
$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());
echo $filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
//$sql_data = "SELECT * FROM prod_list_1 ";
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
//print_r($emapData);
//exit();
$sql = "INSERT into prod_list_1(p_bench,p_name,p_price,p_reason) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')";
mysql_query($sql);
}
fclose($file);
echo 'CSV File has been successfully Imported';
header('Location: index.php');
}
else
echo 'Invalid File:Please Upload CSV File';
}
?>
Excel file is like below
Excel文件如下
I want to import only the 2nd row values into my table. Please help me how can i solve it or give me any resource. I was able to upload the excel file but it is not correctly formatted. i want to upload 4 column in mysql's 4 column but it is uploading all 4 column in mysql's 1 column. am i missing something ?
我只想将第二行值导入我的表中。请帮助我如何解决它或给我任何资源。我能够上传excel文件，但格式不正确。我想在 mysql 的 4 列中上传 4 列，但它正在上传 mysql 的 1 列中的所有 4 列。我错过了什么吗？
回答by Atanu Saha
For >= 2nd row values insert into table-
对于 >= 2nd 行值插入到表中-
$file = fopen($filename, "r");
//$sql_data = "SELECT * FROM prod_list_1 ";
$count = 0; // add this line
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
//print_r($emapData);
//exit();
$count++; // add this line
if($count>1){ // add this line
$sql = "INSERT into prod_list_1(p_bench,p_name,p_price,p_reason) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')";
mysql_query($sql);
} // add this line
}
回答by Victor del Ama
You are probably having a problem with the sort of CSV file that you have.
您所拥有的 CSV 文件类型可能有问题。
Open the CSV file with a text editor, check that all the separations are done with the comma, and not semicolon and try the script again. It should work fine.
使用文本编辑器打开 CSV 文件，检查所有分隔是否使用逗号而不是分号完成，然后再次尝试脚本。它应该可以正常工作。
