php 将csv数据导入mysql的PHP脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20876043/
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
PHP script to import csv data into mysql
提问by Kabir
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.
我尝试了以下代码，但遇到了一些错误。在这里，我可以读取输入文件，但出现以下错误：已弃用：函数 split() 在第 63 行的 C:\wamp\www\aaj2\index.php 中已弃用。O/P：共发现 5124 条记录在这个 csv 文件中。
<?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.
编辑：错误：文件不可写，请检查权限。在这个 csv 文件中一共发现了 5124 条记录。
回答by Bill Karwin
Several tips:
几个提示：
Don't use the deprecated ext/mysql, when you can use ext/mysqli or PDO.
Don't read the entire csv file into a PHP variable. What happens when the file is 500MB?
Don't write custom PHP code to parse csv data, when you can use the builtin function fgetcsv().
Don't create a new SQL statement for every row in the data, when you can use prepared statements.
Don't interpolate data from an external file into SQL statements. This risks SQL injectionvulnerabilities, just like when you interpolate untrusted user input.
Don't parse and insert csv data row by row, when you can use MySQL's LOAD DATA INFILEcommand. It's 20x faster than inserting row by row.
当您可以使用 ext/mysqli 或 PDO 时，不要使用已弃用的 ext/mysql。
不要将整个 csv 文件读入 PHP 变量。当文件为 500MB 时会发生什么？
当您可以使用内置函数fgetcsv()时，不要编写自定义 PHP 代码来解析 csv 数据。
不要为数据中的每一行都创建一个新的 SQL 语句，当您可以使用准备好的语句时。
不要将来自外部文件的数据插入到 SQL 语句中。这会带来SQL 注入漏洞的风险，就像您插入不受信任的用户输入一样。
不要逐行解析和插入 csv 数据，当您可以使用 MySQL 的LOAD DATA INFILE命令时。它比逐行插入快 20 倍。
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";
?>
I tested this with PHP 5.3.26 on a Mac, connecting to MySQL 5.6.14 on Linux.
我在 Mac 上用 PHP 5.3.26 测试了这个，在 Linux 上连接到 MySQL 5.6.14。
回答by ReNiSh A R
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
?>
回答by Patel Yatin
**Data Import And Export**
**CSV To Mysql AND Mysql To CSV Using Mysqli**
<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<form method="post">
<button type="submit" name="btn_export">Data Export</button>
<button type="submit" name="btn_import">Data Import</button>
</form>
<?php
$host = "localhost";
$uname = "root";
$pass = "";
$database = "demo"; //Change Your Database Name
$conn = new mysqli($host, $uname, $pass, $database)or die("No Connection");
echo mysql_error();
//MYSQL MYADDMINPHP TO CSV
if (isset($_REQUEST['btn_export']))
{
$data_op = "";
$sql = $conn->query("select * from users"); //Change Your Table Name
while ($row1 = $sql->fetch_field())
{
$data_op .= '"' . $row1->name . '",';
}
$data_op .="\n";
while ($row = $sql->fetch_assoc())
{
foreach ($row as $key => $value)
{
$data_op .='"' . $value . '",';
}
$data_op .="\n";
}
$filename = "Database.csv"; //Change File type CSV/TXT etc
header('Content-type: application/csv'); //Change File type CSV/TXT etc
header('Content-Disposition: attachment; filename=' . $filename);
echo $data_op;
exit;
}
//CSV To MYSQL MYADDMINPHP
if (isset($_REQUEST['btn_import']))
{
$filename = 'Database.csv';
$fp = fopen($filename, "r");
while (($row = fgetcsv($fp, "40", ",")) != FALSE)
{
$sql = "INSERT INTO users (name,pass,city,id) VALUES('" . implode("','", $row) . "')";
if (!$conn->query($sql))
{
echo '<br>Data No Insert<br>';
}
}
fclose($fp);
}
?>
</body>
</html>
回答by Dipak Patil
I think you need to convert CSV file in collection first. After that you can add loop and call insert queries to insert data.
我认为您需要先在集合中转换 CSV 文件。之后，您可以添加循环并调用插入查询来插入数据。
Here you will get code to convert CSV file into collection array. best-way-to-upload-and-read-csv-file-in-php
在这里，您将获得将 CSV 文件转换为集合数组的代码。 上传和读取 csv-file-in-php 的最佳方式
