将大型CSV文件上传到Mysql数据库 [英] Uploading Large CSV File into Mysql Database

问题描述

我想将大型CSV文档上传到mysql数据库，任何人都可以帮助我，表由10个字段组成，但我想上传到只有5个字段的表没有标题。我想在浏览器中用php完成。

I want to upload large CSV document into mysql database can anyone help me out, the table consist of 10 fields but i want to upload into only 5 fields of the table without a heading. I want this done with php in the browser

$filename = $_FILES['sel_file']['tmp_name'];
<?php
    if($_FILES["file"]["type"] != "application/vnd.ms-excel"){
       die("This is not a CSV file.");
    }
    elseif(is_uploaded_file($_FILES['file']['name'])){
     $dbhost = 'localhost';
     $dbuser = 'root';
     $dbpass = '';
     $dbname = 'cbt_software';
     $link = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error        connecting to mysql server');
   mysql_select_db('cbt_software') or die(mysql_error());
   //Process the CSV file
   $handle = fopen($_FILES['file']['name'], "r");
   $data = fgetcsv($handle, 1000, ";"); 
   while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
      $att0 = mysql_real_escape_string($data[0]);
      $att1 = mysql_real_escape_string($data[1]);
      $att2 = mysql_real_escape_string($data[2]);
      $att3 = mysql_real_escape_string($data[3]);
      $att4 = mysql_real_escape_string($data[4]);  

      $sql = "INSERT INTO `course_reg` (`coursecode`,`coursename`,`coursedescription`,`coursemaster`,`courselevel`)VALUES     ('$att0','$att1','$att2','$att3','$att4')";
      mysql_query($sql) or die(mysql_error());
   }
   mysql_close($link);
   echo "CSV file successfully imported.";
}
else{
   die("You shouldn't be here");
}
?>

首先，将所有字段从csv导入数据库中的一个字段，然后被篡改

At first this imported all the field from the csv into just one field in the database and after i tampered with the code it is not recognicing it a s a CSV file.

推荐答案

如果您有适当的权限，可以直接在MySQL使用 LOAD DATA INFILE 命令，请参阅 http://dev.mysql.com/doc/refman/4.1/en/load-data.html 或 mysqlimport 实用程序，请参见 http://dev.mysql.com/doc/refman/ 4.1 / en / mysqlimport.html

If you have the appropriate permissions, you can do so directly in MySQL with the LOAD DATA INFILE command, see http://dev.mysql.com/doc/refman/4.1/en/load-data.html or the mysqlimport utility, see http://dev.mysql.com/doc/refman/4.1/en/mysqlimport.html

这两种方法都可以指定数据应该放在哪一列，例如：

Both methods will allow you to specify which columns the data should go in, for instance:

LOAD DATA INFILE'myfile.txt'INTO TABLE'mytable'（col1，col2，col3，...）

或

mysqlimport --columns ='col1，col2，...'tablename.csv

如果您打算从PHP进行，您应该能够读取CSV文件的每一行，并执行适当的SQL INSERT查询命名相应的列（虽然这不会像在MySQL中直接执行的那样高效）。

If you intend to do it from PHP, you should be able to read each line of the CSV file and execute an appropriate SQL INSERT query naming the appropriate columns (although that will not be as efficient as doing it directly in MySQL).

编辑：我应该补充一点，尝试了到目前为止或你发现困难;如果你坚持某事，而不是只是寻找如何去做的建议，请更新问题说这样。

I should add that you haven't mentioned what you've tried so far or what you're finding difficult; if you're stuck on something in particular, rather than just looking for suggestions on how to go about doing it, please update the question to say so.

这篇关于将大型CSV文件上传到Mysql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！