使用 PHP 代码和 HTML 表单将 excel (.csv) 导入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11893501/
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
Import an excel (.csv) into MySQL using PHP code and an HTML form
提问by DMor
I know there are other posts similar to this, but everyone recommends just doing it directly in PHPMyAdmin into MySQL (Which works perfectly, but I need to import through an HTML form to PHP to MySQL.
我知道还有其他类似的帖子，但每个人都建议直接在 PHPMyAdmin 中将其执行到 MySQL（这工作完美，但我需要通过 HTML 表单导入到 PHP 到 MySQL。
I would like to have an HTML form that collects a file. Then passes that file to a PHP script and I would like to know if it is possible to simply call a PHP function that converts a comma delimeted .csv file into MySQL and adds it to the database.
我想要一个用于收集文件的 HTML 表单。然后将该文件传递给一个 PHP 脚本，我想知道是否可以简单地调用一个 PHP 函数，将逗号分隔的 .csv 文件转换为 MySQL 并将其添加到数据库中。
Or is the only way to parse the file line by line and add each record?
或者是逐行解析文件并添加每条记录的唯一方法？
回答by JDavis
I haven't fully tested this, but I don't see any reason why it wouldn't work.
我还没有完全测试过这个，但我看不出有什么理由让它不起作用。
<?php
if ( isset( $_FILES['userfile'] ) )
{
$csv_file = $_FILES['userfile']['tmp_name'];
if ( ! is_file( $csv_file ) )
exit('File not found.');
$sql = '';
if (($handle = fopen( $csv_file, "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql .= "INSERT INTO `table` SET
`column0` = '$data[0]',
`column1` = '$data[1]',
`column2` = '$data[2]';
";
}
fclose($handle);
}
// Insert into database
//exit( $sql );
exit( "Complete!" );
}
?>
<!DOCTYPE html>
<html>
<head>
<title>CSV to MySQL Via PHP</title>
</head>
<body>
<form enctype="multipart/form-data" method="POST">
<input name="userfile" type="file">
<input type="submit" value="Upload">
</form>
</body>
</html>
Of course you would need to validate the data first.
当然，您需要先验证数据。
回答by Dean Grell
We used this awhile ago, and it works just fine. Just watch your file and directory permissions. csv_upload_mysql_conf.inc is just the DB link. This will parse multiple files at once, and put them in a table called import. Update accordingly.
我们不久前使用过这个，它工作得很好。只需注意您的文件和目录权限。csv_upload_mysql_conf.inc 只是数据库链接。这将一次解析多个文件，并将它们放入名为 import 的表中。相应地更新。
<?php
/* The conf file */
include_once "csv_upload_mysql_conf.inc";
$php_self = $_SERVER['PHP_SELF'];
$file_open = 0;
$file_exts = array
( 'csv');
#Our Form.....
$form = <<< EOFFORM
<div align='center' style='border: 1px solid #CCC; background-color: #FAFAFA;padding: 10px; color: #006699; width: 620px; font-family: palatino, verdana, arial, sans-serif;' >
<table align=center style='border: 1px solid #CCC; background-color: #FFF;padding: 20px; color: #006699;' cellspacing=1><tbody>
<tr><td>
<form enctype='multipart/form-data' action='$php_self' method='post'><input type='hidden' name='MAX_FILE_SIZE' value='2000000' /><input type='hidden' name='selected' value='yes' /> Selected file: <input name='userfile[]' type='file' id='userfile[]' multiple='' onChange='makeFileList();' /><br /><br /><input type='submit' value='Upload CSV' />
</td></tr></tbody></table></div>
<p>
<strong>Files You Selected:</strong>
</p>
<ul id="fileList"><li>No Files Selected</li></ul>
<script type='text/javascript'>
function makeFileList() {
var input = document.getElementById('userfile[]');
var ul = document.getElementById('fileList');
while (ul.hasChildNodes()) {
ul.removeChild(ul.firstChild);
}
for (var i = 0; i < input.files.length; i++) {
var li = document.createElement('li');
li.innerHTML = input.files[i].name;
ul.appendChild(li);
}
if(!ul.hasChildNodes()) {
var li = document.createElement('li');
li.innerHTML = 'No Files Selected';
ul.appendChild(li);
}
}
</script>
EOFFORM;
#End Form;
if(!isset($_POST['selected'])){
echo "$form";
}
elseif($_POST['selected'] == "yes"){
$uploaddir = 'uploads/';
if(count($_FILES['userfile']['name'])) {
foreach ($_FILES['userfile']['name'] as $key => $error) {
if ($error == UPLOAD_ERR_OK) {
$tmp_name = $_FILES['userfile']['tmp_name'][$key];
$name = $_FILES['userfile']['name'][$key];
$f_type = trim(strtolower(end(explode('.', $name))));
if (!in_array($f_type, $file_exts)) die("Sorry, $f_type files not allowed");
}
$uploadfile = $uploaddir . $name;
if (! file_exists($uploadfile)) {
if (move_uploaded_file($tmp_name, $uploadfile)) {
print "File is valid, and was successfully uploaded. ";
$flag = 1;
chmod($uploadfile, 0777);
} else {
print "File Upload Failed. ";
$flag = 0;
}
$flag = 1;
if ($flag == 1) {
echo "\n parsing Data...";
flush();
if (file_exists($uploadfile)) {
$fp = fopen($uploadfile, 'r') or die (" Can't open the file");
$fileopen = 1;
$length = calculate_length($uploadfile);
}
$replace = "REPLACE";
$field_terminater = ",";
$enclose_option = 1;
$enclosed = '"';
$escaped = '\\';
$line_terminator = 1;
$local_option = 1;
$sql_query = 'LOAD DATA';
if ($local_option == "1") {
$sql_query .= ' LOCAL';
}
$sql_query .= ' INFILE \'' . $uploadfile . '\'';
if (!empty($replace)) {
$sql_query .= ' ' . $replace;
}
$sql_query .= ' INTO TABLE ' . "`import`";
if (isset($field_terminater)) {
$sql_query .= ' FIELDS TERMINATED BY \'' . $field_terminater . '\'';
}
if (isset($enclose_option) && strlen($enclose_option) > 0) {
$sql_query .= ' OPTIONALLY';
}
if (strlen($enclosed) > 0) {
$sql_query .= ' ENCLOSED BY \'' . $enclosed . '\'';
}
if (strlen($escaped) > 0) {
$sql_query .= ' ESCAPED BY \'' . $escaped . '\'';
}
if (strlen($line_terminator) > 0){
$sql_query .= ' LINES TERMINATED BY \'' . '\r\n' . '\'';
}
$result = mysql_query ($sql_query);
echo mysql_error() ;
if(mysql_affected_rows() > 1) {
echo " <div align=center><b><font color=#66CC33>The csv data was added.</font></div> ";
}
else {
error_log(mysql_error());
echo " <div align=center><b><font color=#E96B10> Couldn't enter the data to db </font></div>";
}
if ($file_open ==1) {
fclose($fp) or die("Couldn't close the file");
}
}
}
}
echo "<meta http-equiv='refresh' content='0; url=index.php'>";
}
}
function calculate_length($fp) {
$length = 1000;
$array = file($fp);
for($i=0;$i<count($array);$i++)
{
if ($length < strlen($array[$i]))
{
$length = strlen($array[$i]);
}
}
unset($array);
return $length;
}
?>
回答by kiran
Everything in your code is fine, only mistake is you are not using mysql_query for inserting the data into table. Mysql query not running in your script. corrected code follows...
代码中的一切都很好，唯一的错误是您没有使用 mysql_query 将数据插入表中。Mysql 查询未在您的脚本中运行。更正后的代码如下...
<?php
if ( isset( $_FILES['userfile'] ) )
{
$csv_file = $_FILES['userfile']['tmp_name'];
if ( ! is_file( $csv_file ) )
exit('File not found.');
$sql = '';
if (($handle = fopen( $csv_file, "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql = mysql_query("INSERT INTO `table` SET
`column0` = '$data[0]',
`column1` = '$data[1]',
`column2` = '$data[2]';
");
}
fclose($handle);
}
// Insert into database
//exit( $sql );
exit( "Complete!" );
}
?>
<!DOCTYPE html>
<html>
<head>
<title>CSV to MySQL Via PHP</title>
</head>
<body>
<form enctype="multipart/form-data" method="POST">
<input name="userfile" type="file">
<input type="submit" value="Upload">
</form>
</body>
</html>
