如何使用 PHP 将大型 Excel 文件导入 MySql 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23869743/
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 Large Excel File To MySql Database Using PHP
提问by Darshan
I have to upload excel file's data to MySQL database using php. I have found the code for that but I am unable to upload large files.
我必须使用 php 将 excel 文件的数据上传到 MySQL 数据库。我找到了代码，但我无法上传大文件。
Can anyone please tell me that how can I increase the max file size limit for the code mentioned in below link:
谁能告诉我如何增加以下链接中提到的代码的最大文件大小限制：
http://www.9code.in/how-to-import-excel-file-to-mysql-database-using-php/
http://www.9code.in/how-to-import-excel-file-to-mysql-database-using-php/
<!DOCTYPE html>
<?php
include 'db.php';
include 'Excel/reader.php';
function uploadFile($fieldName, $fileType, $folderName, $name = "")
{
$flg = 0;
$MaxID = "";
$ext = "";
$uploadfile = "";
if (isset($fieldName) AND $fieldName['name'] != '')
{
$flg = 1;
$allowed_filetypes = $fileType;
// I Need to increase this..... I tried changing values but nothing happened
$max_filesize = 1048576;
$filename = $fieldName['name'];
if ($name == "")
$MaxID = time() . time() . rand(1, 100);
else
$MaxID = $name;
$ext = substr($filename, strpos($filename, '.'), strlen($filename) - 1);
if($ext==".xlsx")
$ext=".xls";
if (!in_array($ext, $allowed_filetypes))
echo "<h1>The file you attempted to upload is not allowed...</h1>";
else if (filesize($fieldName['tmp_name']) > $max_filesize)
echo "<h1>The file you attempted to upload is too large...</h1>";
else
{
$uploadfile = $folderName . "/" . $MaxID . $ext;
if (move_uploaded_file($fieldName['tmp_name'], $uploadfile) == FALSE)
{
echo "<h1>Error in Uploading File...</h1>";
$MaxID = "";
}
else
$MaxID = $MaxID . $ext;
}
}
return $MaxID;
}
if(isset($_POST['submit']))
{
if($_FILES['csvFile']['name']!="")
{
$fileName=uploadFile($_FILES['excelFile'],array(".csv"),"excel_file");
$row=0;
if(($handle = fopen("excel/".$fileName , "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$num = count($data);
//print_r($data);
$query="INSERT INTO StudentData(FirstName,LastName,MobileNo,City)VALUES('".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."')";
mysql_query($query);
}
fclose($handle);
}
}
else if($_FILES['excelFile']['name']!="")
{
$fileName=uploadFile($_FILES['excelFile'],array(".xls",".xlsx"),"excel_file");
$data = new Spreadsheet_Excel_Reader();
$data->read('excel_file/'.$fileName);
for($i=1;$i<=$data->sheets[0]['numRows'];$i++)
{
$firstname=$data->sheets[0]['cells'][$i][1];
$lastname=$data->sheets[0]['cells'][$i][2];
$mobile=$data->sheets[0]['cells'][$i][3];
$city=$data->sheets[0]['cells'][$i][4];
$query="INSERT INTO StudentData(FirstName,LastName,MobileNo,City)VALUES('".$firstname."','".$lastname."','".$mobile."','".$city."')";
mysql_query($query);
}
}
}
if(isset($_POST['delete']))
{
mysql_query("DELETE FROM StudentData");
}
?>
采纳答案by Darshan
According to ASNAOUI Ayoub I made the following changes:
根据 ASNAOUI Ayoub，我进行了以下更改：
; Maximum allowed size for uploaded files.
upload_max_filesize = 40M
; Must be greater than or equal to upload_max_filesize
post_max_size = 40M
But Stil the Problem was same then I tried to change the
但问题还是一样，然后我试图改变
$max_filesize = 41943040
Now It perfectly works.....
现在它完美地工作......
Thanks Everyone for the help
谢谢大家的帮助
回答by truesource
You can use LOAD DATAcommand in MySQL : Read More
您可以LOAD DATA在 MySQL 中使用命令：阅读更多
you have to used load data in mysql statement. This can load your large file in database.
您必须在 mysql 语句中使用加载数据。这可以将您的大文件加载到数据库中。
mysqli_query($dblink, '
LOAD DATA LOCAL INFILE "'.$file.'"
INTO TABLE transactions
FIELDS TERMINATED by ","
OPTIONALLY ENCLOSED BY "\'"
LINES TERMINATED BY "\n"
');
回答by Blake
Look at these values in your php.ini
查看 php.ini 中的这些值
upload_max_filesize = 10M post_max_size = 10M
upload_max_filesize = 10M post_max_size = 10M
回答by ASNAOUI Ayoub
You need to set the value of upload_max_filesize and post_max_size in your php.ini :
您需要在 php.ini 中设置 upload_max_filesize 和 post_max_size 的值：
; Maximum allowed size for uploaded files.
upload_max_filesize = 40M
; Must be greater than or equal to upload_max_filesize
post_max_size = 40M
I have to add that you have to restart the server
我必须补充一点，您必须重新启动服务器
