现在我导入我的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";
}
Answer 1:
只需设置一个变量$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";
}
Answer 2:
你可以简单地使用MySQL的LOAD DATA INFILE
命令,这将是相当快比解析CSV到PHP,构建一个INSERT
语句中的每个记录,提交其作为一个字符串到MySQL,并具有MySQL的解析说字符串SQL(在SQL的风险注射):
LOAD DATA INFILE ? INTO TABLE tictoc
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(tasktime, sessiontime, sessionstart, sessionend, sessionnotes)
SET employee = ?, taskname = ?
使用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']
));
}
Answer 3:
也许,如果你喜欢,你可以用这样的方式:
$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());
}
Answer 4:
没有变量需要。 它是比这更简单。 这是需要的外来代码量最少,并且工作得很好的解决方案。 尝试把:
$headers = fgetcsv($handle, 1000, ",");
您而在此之前()循环,抓住CSV数据。 这抓住了头,但留给他们被插入到数据库了。 一旦while循环开始,第一行已经返回从而使他们走出了MySQL插件的。 在此之上似乎需要有头,因此这解决了为好。
所以,最后,
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";
}
Answer 5:
你可以试试这个:
array_shift($data);
Answer 6:
我有实现这个代码并且测试代码。 我觉得这是非常使用全
你必须遵循一些规则: -
根据数据库表名1.您的CSV文件(例如:数据库表名用户,那么CSV应该users.csv)
2.您的CSV文件的第一行应该是数据库表字段名(例如:ID,姓名等)后,开始您的数据录入
3.you可以从下载数据源的类: - 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.";
}
}
Answer 7:
之前声明一个变量while
循环
$row=1;
然后在while
循环把这个条件
if($row==1){
$row++;
continue;
}
Answer 8:
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";
}
文章来源: Import CSV, exclude first row