使用 PHP/MySQL 导入 CSV 数据 [英] Importing CSV data using PHP/MySQL
问题描述
我在尝试从 CSV 导入数据时遇到了一些问题，并且有几个我还没有设法解决的问题.
首先，这是我的代码，可帮助您正确看待事物(稍微整理一下，删除 CSS 和 DB 连接):
<div id="容器"><div id="表单"><?php$deleterecords = "TRUNCATE TABLE 表名";//清空其当前记录的表mysql_query($deleterrecords);//上传文件如果(isset($_POST['提交'])){如果 (is_uploaded_file($_FILES['filename']['tmp_name'])) {回声<h1>".文件 ".$_FILES['filename']['name'] ." 上传成功." . "</h1>";echo "<h2>显示内容:</h2>";读取文件($_FILES['文件名']['tmp_name'])；}//将上传的文件导入数据库$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) 或 die(mysql_error());}fclose($handle);打印导入完成"；//查看上传表单} 别的 {打印通过浏览文件并单击上传来上传新的 csv
\n";打印<form enctype='multipart/form-data' action='upload.php' method='post'>";print "要导入的文件名:<br/>\n";打印<input size='50' type='file' name='filename'><br/>\n";print "这基本上是对我在各种方法上多次尝试后发现的一个例子的改编.
我的 CSV 有两列数据，第一列是文本，第二列是整数数据库中的表也有两列，第一列称为文本"，第二列称为数字"
所以我的问题是:
- 上传的文本在每个字段中都显示为 0，我不知道为什么
- 我一直在阅读有关以"结尾的数据，如果发生这种情况，我将如何对其进行排序?
- 如何忽略标题等的 CSV 的前 X 行?
- 在整个过程中数据格式是否发生了变化，或者是否已准备好供我在图表中使用?例如小数点放入数据库后会保持小数点吗?
我认为这涵盖了所有内容，在此先感谢您的帮助！
刚刚做了10,000条记录上传的测试，得到了错误:
致命错误:超过最大执行时间 30 秒"
有什么想法吗?
我前几天回答了一个几乎相同的问题:将CSV文件保存到mysql数据库
MySQL 有一个功能 LOAD DATA INFILE，它允许它在单个 SQL 查询中直接导入 CSV 文件，而根本不需要通过 PHP 程序在循环中处理它.
简单例子:
query($query);?>就这么简单.
没有循环，没有大惊小怪.而且比在 PHP 中解析它要快得多.
MySQL 手册页在这里:http://dev.mysql.com/doc/refman/5.1/en/load-data.html
希望有帮助
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.
First off here's my code to help put things in perspective (tidied it up a bit, removing CSS and DB connection):
<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"
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?
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:
"Fatal error: Maximum execution time of 30 seconds exceeded"
any thoughts?
I answered a virtually identical question just the other day: 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.
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 much quicker than parsing it in PHP.
MySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Hope that helps
这篇关于使用 PHP/MySQL 导入 CSV 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
