php 导入 CSV，排除第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12268114/
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
Import CSV, exclude first row
提问by Hector
I'm importing my csv fine now except one thing, how do i get the import to ignore the data in the first row? Employees will be uploading the same format which has the column names in that first row.
我现在正在导入我的 csv 很好，除了一件事，我如何让导入忽略第一行中的数据？员工将上传在第一行中具有列名称的相同格式。
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 tictoc(employee,taskname,tasktime,sessiontime,sessionstart,sessionend,sessionnotes) values('" . $userinfo['first_name'] . " " . $userinfo['last_name'] . "','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "Import done";
}
回答by bretterer
just set a variable $i = 0Then only insert when $i = 1or more
只是设置一个变量$i = 0然后只插入时$i = 1或更多
if (isset($_POST['submit'])) {
$i=0; //so we can skip first row
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) {
if($i>0) {
$import="INSERT into tictoc(employee,taskname,tasktime,sessiontime,sessionstart,sessionend,sessionnotes) values('".$userinfo['first_name']." ".$userinfo['last_name']."','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
mysql_query($import) or die(mysql_error());
}
$i++;
}
fclose($handle);
print "Import done";
}
回答by eggyal
You can simply use MySQL's LOAD DATA INFILEcommand, which will be considerably fasterthan parsing the CSV into PHP, constructing an INSERTstatement for every record, submitting it as a string to MySQL and having MySQL parse said string for SQL (at risk of SQL injection):
您可以简单地使用 MySQL 的LOAD DATA INFILE命令，这将比将CSV 解析为 PHP、为每条记录构建语句、将其作为字符串提交给 MySQL 并让 MySQL 为 SQL 解析所述字符串（存在 SQL 注入的风险）快得多INSERT：
LOAD DATA INFILE ? INTO TABLE tictoc
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(tasktime, sessiontime, sessionstart, sessionend, sessionnotes)
SET employee = ?, taskname = ?
Using PDO:
使用 PDO：
$dbh = new PDO('mysql:dbname='.$dbname, $username, $password);
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
$qry = $dbh->prepare('
LOAD DATA INFILE :filepath INTO TABLE tictoc
FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\'
IGNORE 1 LINES
(tasktime, sessiontime, sessionstart, sessionend, sessionnotes)
SET employee = :employee, taskname = :taskname
');
$qry->execute(array(
':filepath' => $_FILES['filename']['tmp_name'],
':employee' => $userinfo['first_name'],
':taskname' => $userinfo['last_name']
));
}
回答by J-J
No variable needed. It is much simpler than this. Here is a solution that requires the least amount of foreign code, and works just fine. Try putting:
不需要变量。它比这简单得多。这是一个需要最少外部代码的解决方案，并且工作正常。尝试放置：
$headers = fgetcsv($handle, 1000, ",");
Before your while() loop that grabs the CSV data. That grabs the headers, but leaves them out from being inserted into the database. Once the while loop begins, the first line has already been returned thus leaving them out of the mysql insert. On top of this it appears the headers were needed and so this solves that as well.
在获取 CSV 数据的 while() 循环之前。这会获取标题，但不会将它们插入到数据库中。一旦 while 循环开始，第一行就已经返回，从而将它们排除在 mysql 插入之外。最重要的是，似乎需要标题，所以这也解决了这个问题。
So in the end,
所以最后，
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");
//Grab the headers before doing insertion
$headers = fgetcsv($handle, 1000, ",");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$import = "INSERT into tictoc(employee,taskname,tasktime,sessiontime,sessionstart,sessionend,sessionnotes) values('" . $userinfo['first_name'] . " " . $userinfo['last_name'] . "','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "Import done";
}
回答by bungdito
maybe you can use this way if you like:
如果您愿意，也许可以使用这种方式：
$i=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$i++;
if($i==1) continue;
$import="INSERT into tictoc(employee,taskname,tasktime,sessiontime,sessionstart,sessionend,sessionnotes) values('".$userinfo['first_name']." ".$userinfo['last_name']."','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
mysql_query($import) or die(mysql_error());
}
回答by Abid Hussain
I have implement this code and it is tested code. I think it is very use full
我已经实现了这段代码，它是经过测试的代码。我认为这是非常有用的
You have follow some rule:-
您必须遵循一些规则：-
1.your csv file according to database table name (ex: db table name is users then csv should be users.csv)
1.你的csv文件根据数据库表名（例如：db表名是users然后csv应该是users.csv）
2.Your csv file's first row should be db table fields name (ex: Id, name etc) after the start your data entry
2.您的csv文件的第一行应该是开始您的数据输入后的db表字段名称（例如：Id，名称等）
3.you can download data source class from :- http://code.google.com/p/php-csv-parser/because i have require below the code: require_once 'CSV/DataSource.php';
3.您可以从以下位置下载数据源类：- http://code.google.com/p/php-csv-parser/因为我在代码下方有要求： require_once 'CSV/DataSource.php';
<?php
ini_set('memory_limit','512M');
$dbhost = "localhost";
$dbname = "excel_import";
$dbuser = "root";
$dbpass = "";
$conn=mysql_connect ($dbhost, $dbuser, $dbpass) or die ("I cannot connect to the database because: " . mysql_error());
mysql_select_db($dbname) or die("Unable to select database because: " . mysql_error());
require_once 'CSV/DataSource.php';
$filename = "users.csv";
$ext = explode(".",$filename);
$path = "uploads/".$filename;
$dbtable = $ext[0];
import_csv($dbtable, $path);
function import_csv($dbtable, $csv_file_name_with_path)
{
$csv = new File_CSV_DataSource;
$csv->load($csv_file_name_with_path);
$csvData = $csv->connect();
$res='';
foreach($csvData as $key)
{
$myKey ='';
$myVal='';
foreach($key as $k=>$v)
{
$myKey .=$k.',';
$myVal .="'".$v."',";
}
$myKey = substr($myKey, 0, -1);
$myVal = substr($myVal, 0, -1);
$query="insert into ".$dbtable." ($myKey)values($myVal)";
$res= mysql_query($query);
}
if($res ==1)
{
echo "record successfully Import.";
}else{
echo "record not successfully Import.";
}
}
回答by Moe Tsao
You can try this:
你可以试试这个：
array_shift($data);
回答by mohitkantgupta
Declare one variable before whileloop
while循环前声明一个变量
$row=1;
then in whileloop put this condition
然后在while循环中放置这个条件
if($row==1){
$row++;
continue;
}
回答by Ibrahim
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");
fgetcsv($handle); //skip the reading of the first line from the csv file
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$import = "INSERT into tictoc(employee,taskname,tasktime,sessiontime,sessionstart,sessionend,sessionnotes) values('" . $userinfo['first_name'] . " " . $userinfo['last_name'] . "','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "Import done";
}
}
