Import CSV, exclude first row

问题:

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.

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";
}

回答1:

just set a variable $i = 0 Then only insert when $i = 1 or more

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";
}

回答2:

You can simply use MySQL's LOAD DATA INFILE command, which will be considerably faster than parsing the CSV into PHP, constructing an INSERT statement for every record, submitting it as a string to MySQL and having MySQL parse said string for SQL (at risk of SQL injection):

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:

$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']
  ));
}

回答3:

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());
}

回答4:

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.

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";
}

回答5:

You can try this:

array_shift($data);

回答6:

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)

2.Your csv file's first row should be db table fields name (ex: Id, name etc) after the start your data entry

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';

<?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.";
    }
}

回答7:

Declare one variable before while loop

$row=1;

then in while loop put this condition

if($row==1){
   $row++; 
   continue;
}

回答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";

}