使用PHP代码和HTML表单将excel（.csv）导入MySQL

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?

或者是逐行解析文件并添加每条记录的唯一方法？

3 个解决方案

#1

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.

当然，您需要先验证数据。

#2

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;
}

?>

#3

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>

#1