PHP script to import csv data into mysql

几个提示:

当你可以使用ext/mysqli或PDO时,不要使用已弃用的ext/mysql.

不要将整个csv文件读入PHP变量.当文件为500MB时会发生什么？

当您可以使用内置函数fgetcsv()时,不要编写自定义PHP代码来解析csv数据.

当您可以使用预准备语句时,不要为数据中的每一行创建新的SQL 语句.

不要将外部文件中的数据插入到SQL语句中.这会冒SQL注入漏洞的风险,就像插入不受信任的用户输入一样.

当您可以使用MySQL的LOAD DATA INFILE命令时,不要逐行解析和插入csv数据.它比逐行插入快20倍.

这是一个更简单的解决方案:

<?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.

Try this:

<?php
// specify connection info
$connect = mysql_connect('localhost','root','12345');
if (!$connect)
{
   die('Could not <span id="IL_AD1" class="IL_AD">
    connect to</span> MySQL: ' . mysql_error());
}

$cid =mysql_select_db('test',$connect); //specify db name

define('CSV_PATH','C:/wamp/www/csvfile/'); // specify CSV file path

$csv_file = CSV_PATH . "infotuts.csv"; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile))
{
   $csv_data[] = fgets($csvfile, 1024);
   $csv_array = explode(",", $csv_data[$i]);
   $insert_csv = array();
   $insert_csv['ID'] = $csv_array[0];
   $insert_csv['name'] = $csv_array[1];
   $insert_csv['email'] = $csv_array[2];
   $query = "INSERT INTO csvdata(ID,name,email)
     VALUES('','".$insert_csv['name']."','".$insert_csv['email']."')";
   $n=mysql_query($query, $connect );
   $i++;
}
fclose($csvfile);
echo "File data successfully imported to database!!";
mysql_close($connect); // closing connection
?>