使用 PHP/MySQL 导入 CSV 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11448307/
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
Importing CSV data using PHP/MySQL
提问by Pidge
I'm having a bit of a problem trying to import data from a CSV and have a couple of questions on it that I haven't managed to solve myself yet.
我在尝试从 CSV 导入数据时遇到了一些问题，并且有几个我还没有设法解决的问题。
First off here's my code to help put things in perspective (tidied it up a bit, removing CSS and DB connection):
首先，这是我的代码，可帮助您正确看待事物（稍微整理一下，删除 CSS 和 DB 连接）：
<body>
<div id="container">
<div id="form">
<?php
$deleterecords = "TRUNCATE TABLE tablename"; //empty the table of its current records
mysql_query($deleterecords);
//Upload File
if (isset($_POST['submit'])) {
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded
successfully." . "</h1>";
echo "<h2>Displaying contents:</h2>";
readfile($_FILES['filename']['tmp_name']);
}
//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$import="INSERT into importing(text,number)values('$data[0]','$data[1]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "Import done";
//view upload form
} else {
print "Upload new csv by browsing to file and clicking on Upload<br />\n";
print "<form enctype='multipart/form-data' action='upload.php' method='post'>";
print "File name to import:<br />\n";
print "<input size='50' type='file' name='filename'><br />\n";
print "<input type='submit' name='submit' value='Upload'></form>";
}
?>
</div>
</div>
</body>
It's basically an adaptation of an example I have found after many many attempts at various methods.
它基本上是我在对各种方法进行多次尝试后发现的一个例子的改编。
My CSV has two columns of data, the first one being text and the second is integers The table in the database also has two columns, the first called "text" and the second "number"
我的CSV有两列数据，第一列是文本，第二列是整数数据库中的表也有两列，第一列称为“文本”，第二列称为“数字”
So the questions I have are:
所以我的问题是：
- the text being uploaded is just being displayed as 0 in every field and i'm not sure why
- I keep reading about data ending up enclosed in "", if that happens how would I sort it?
- how can I ignore the first X lines of the CSV for headers etc?
- is the data format changed throughout this process or is it ready for me to use in a graph? e.g. would a decimal stay a decimal once placed in the database?
- 上传的文本在每个字段中都显示为 0，我不知道为什么
- 我一直在阅读有关以“”结尾的数据，如果发生这种情况，我将如何对其进行排序？
- 如何忽略标题等的 CSV 的前 X 行？
- 在整个过程中数据格式是否发生了变化，或者是否已准备好供我在图表中使用？例如，一旦放入数据库中，小数会保持小数吗？
I think that covers everything, thanks in advance for any help!
我认为这涵盖了所有内容，在此先感谢您的帮助！
EDIT:
编辑：
Just done a test of 10,000 record uploading and got the error:
刚刚做了10000条记录上传的测试，得到了错误：
"Fatal error: Maximum execution time of 30 seconds exceeded"
“致命错误：超过 30 秒的最大执行时间”
any thoughts?
有什么想法吗？
回答by SDC
I answered a virtually identical question just the other day: Save CSV files into mysql database
前几天我回答了一个几乎相同的问题：Save CSV files into mysql database
MySQL has a feature LOAD DATA INFILE, which allows it to import a CSV file directly in a single SQL query, without needing it to be processed in a loop via your PHP program at all.
MySQL 有一个特性LOAD DATA INFILE，它允许它在单个 SQL 查询中直接导入 CSV 文件，而根本不需要通过 PHP 程序在循环中处理它。
Simple example:
简单的例子：
<?php
$query = <<<eof
LOAD DATA INFILE '$fileName'
INTO TABLE tableName
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field1,field2,field3,etc)
eof;
$db->query($query);
?>
It's as simple as that.
就这么简单。
No loops, no fuss. And much muchquicker than parsing it in PHP.
没有循环，没有大惊小怪。和很多很多比PHP解析它更快。
MySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
MySQL 手册页在这里：http: //dev.mysql.com/doc/refman/5.1/en/load-data.html
Hope that helps
希望有帮助
回答by jit
$i=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if($i>0){
$import="INSERT into importing(text,number)values('".$data[0]."','".$data[1]."')";
mysql_query($import) or die(mysql_error());
}
$i=1;
}
回答by Naren Karthik
letsay $infile = a.csv //file needs to be imported.
letsay $infile = a.csv //需要导入文件。
class blah
{
static public function readJobsFromFile($file)
{
if (($handle = fopen($file, "r")) === FALSE)
{
echo "readJobsFromFile: Failed to open file [$file]\n";
die;
}
$header=true;
$index=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
// ignore header
if ($header == true)
{
$header = false;
continue;
}
if ($data[0] == '' && $data[1] == '' ) //u have oly 2 fields
{
echo "readJobsFromFile: No more input entries\n";
break;
}
$a = trim($data[0]);
$b = trim($data[1]);
if (check_if_exists("SELECT count(*) FROM Db_table WHERE a='$a' AND b='$b'") === true)
{
$index++;
continue;
}
$sql = "INSERT INTO DB_table SET a='$a' , b='$b' ";
@mysql_query($sql) or die("readJobsFromFile: " . mysql_error());
$index++;
}
fclose($handle);
return $index; //no. of fields in database.
}
function
check_if_exists($sql)
{
$result = mysql_query($sql) or die("$sql --" . mysql_error());
if (!$result) {
$message = 'check_if_exists::Invalid query: ' . mysql_error() . "\n";
$message .= 'Query: ' . $sql;
die($message);
}
$row = mysql_fetch_assoc ($result);
$count = $row['count(*)'];
if ($count > 0)
return true;
return false;
}
$infile=a.csv;
blah::readJobsFromFile($infile);
}
hope this helps.
希望这可以帮助。
回答by dzeno
set_time_limit(10000);
$con = mysql_connect('127.0.0.1','root','password');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("db", $con);
$fp = fopen("file.csv", "r");
while( !feof($fp) ) {
if( !$line = fgetcsv($fp, 1000, ';', '"')) {
continue;
}
$importSQL = "INSERT INTO table_name VALUES('".$line[0]."','".$line[1]."','".$line[2]."')";
mysql_query($importSQL) or die(mysql_error());
}
fclose($fp);
mysql_close($con);
回答by PHP Addict
i think the main things to remember about parsing csv is that it follows some simple rules:
我认为解析 csv 时要记住的主要事情是它遵循一些简单的规则：
a)it's a text file so easily opened b) each row is determined by a line end \n so split the string into lines first c) each row/line has columns determined by a comma so split each line by that to get an array of columns
a) 这是一个很容易打开的文本文件 b) 每行由行尾确定 \n 所以首先将字符串拆分为行 c) 每行/行都有由逗号确定的列，因此将每行拆分为一个数组列数
have a read of this postto see what i am talking about
阅读这篇文章，看看我在说什么
it's actually very easy to do once you have the hang of it and becomes very useful.
一旦你掌握了它并且变得非常有用，它实际上很容易做到。
回答by Dulith De Costa
Database Connection:
数据库连接：
try {
$conn = mysqli_connect($servername, $username, $password, $db);
//echo "Connected successfully";
} catch (exception $e) {
echo "Connection failed: " . $e->getMessage();
}
Code to read CSVfile and upload to table in database.
读取CSV文件并上传到数据库表的代码。
$file = fopen($filename, "r");
while (($getData = fgetcsv($file, 10000, ",")) !== FALSE) {
$sql = "INSERT into db_table
values ('','" . $getData[1] . "','" . $getData[2] . "','" . $getData[3] . "','" . $getData[4] . "','" . $getData[5] . "','" . $getData[6] . "')";
$result = mysqli_query($conn, $sql);
if (!isset($result)) {
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.
window.location = \"home.do\"
</script>";
} else {
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"home.do\"
</script>";
}
}
fclose($file);
