PHP脚本将csv数据导入mysql [英] PHP script to import csv data into mysql

问题描述

我试过下面的代码，但得到一些错误。这里我可以读取输入文件，但我得到以下错误：Deprecated：函数split（）在第63行的C：\wamp\www\aaj2\index.php中被弃用。
O / P：在此csv文件中找到总共5124条记录。

I have tried the following code but getting some errors. Here I can read the input file but I am getting the following error:Deprecated: Function split() is deprecated in C:\wamp\www\aaj2\index.php on line 63. O/P: Found a total of 5124 records in this csv file.

<?php
$databasehost = "localhost"; 
$databasename = "test"; 
$databasetable = "sample"; 
$databaseusername="test"; 
$databasepassword = ""; 
$fieldseparator = ","; 
$lineseparator = "\n";
$csvfile = "filename.csv";
$addauto = 0;
$save = 1; 
$outputfile = "output.sql";        

        if(!file_exists($csvfile)) {    echo "File not found. Make sure you specified the correct path.\n";     exit; }

        $file = fopen($csvfile,"r");

        if(!$file) {    echo "Error opening data file.\n";  exit; }

        $size = filesize($csvfile);

        if(!$size) {    echo "File is empty.\n";    exit; }

        $csvcontent = fread($file,$size);

        fclose($file);

        $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error()); @mysql_select_db($databasename) or die(mysql_error());

        $lines = 0; $queries = ""; $linearray = array();

        foreach(split($lineseparator,$csvcontent) as $line) {

            $lines++;

            $line = trim($line," \t");      $line = str_replace("\r","",$line);         /************************************   This line escapes the special character. remove it if entries are already escaped in the csv file   ************************************/   $line = str_replace("'","\'",$line);    /*************************************/         $linearray = explode($fieldseparator,$line);        $linemysql = implode("','",$linearray);         if($addauto)        $query = "insert into $databasetable values('','$linemysql');";     else        $query = "insert into $databasetable values('$linemysql');";        $queries .= $query . "\n";

            @mysql_query($query); }

        @mysql_close($con);

        if($save) {         if(!is_writable($outputfile)) {         echo "File is not writable, check permissions.\n";  }       else {      $file2 = fopen($outputfile,"w");
                        if(!$file2) {           echo "Error writing to the output file.\n";         }       else {          fwrite($file2,$queries);            fclose($file2);         }   }    }

        echo "Found a total of $lines records in this csv file.\n";


        ?>

编辑：错误：文件不可写，请检查权限。

EDIT : Error : File is not writable, check permissions. Found a total of 5124 records in this csv file.

推荐答案

几个提示：

• Don'

• Don't use the deprecated ext/mysql, when you can use ext/mysqli or PDO.

不要将整个csv文件读取到一个文件中，而是使用ext / mysqli或PDO。 PHP变量。

Don't read the entire csv file into a PHP variable. What happens when the file is 500MB?

不要编写自定义PHP代码来解析csv数据，当您可以使用内置函数 fgetcsv（）。

Don't write custom PHP code to parse csv data, when you can use the builtin function fgetcsv().

不要为每一行创建新的SQL语句在数据中，您可以使用准备的语句。

Don't create a new SQL statement for every row in the data, when you can use prepared statements.

不要将外部文件中的数据插入SQL语句中。这会导致 SQL注入漏洞的风险，就像插入不受信任的用户输入时一样。

Don't interpolate data from an external file into SQL statements. This risks SQL injection vulnerabilities, just like when you interpolate untrusted user input.

不要逐行解析和插入csv数据，当您可以使用MySQL的LOAD DATA INFILE 命令。

Don't parse and insert csv data row by row, when you can use MySQL's LOAD DATA INFILE command. It's 20x faster than inserting row by row.

这里有一个更简单的解决方案：

Here's a simpler solution:

<?php
$databasehost = "localhost"; 
$databasename = "test"; 
$databasetable = "sample"; 
$databaseusername="test"; 
$databasepassword = ""; 
$fieldseparator = ","; 
$lineseparator = "\n";
$csvfile = "filename.csv";

if(!file_exists($csvfile)) {
    die("File not found. Make sure you specified the correct path.");
}

try {
    $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
        $databaseusername, $databasepassword,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} catch (PDOException $e) {
    die("database connection failed: ".$e->getMessage());
}

$affectedRows = $pdo->exec("
    LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
      FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
      LINES TERMINATED BY ".$pdo->quote($lineseparator));

echo "Loaded a total of $affectedRows records from this csv file.\n";

?>

我在Mac上使用PHP 5.3.26测试了这一点，在Linux上连接到MySQL 5.6.14。

I tested this with PHP 5.3.26 on a Mac, connecting to MySQL 5.6.14 on Linux.

这篇关于PHP脚本将csv数据导入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！