Skip first line on import csv

Question

Need to add an extra line to skip the first line with headers on csv file. but i dont know from where to start.

<?php

if(isset($_POST["submit"]))
{
$host="localhost"; // Host name.
$db_user="root"; //mysql user
$db_password=""; //mysql pass
$db='local'; // Database name.
$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());

echo $filename=$_FILES["file"]["name"];
$ext=substr($filename,strrpos($filename,"."),(strlen($filename)-strrpos($filename,".")));

  $file = fopen($filename, "r");
    $handle = fopen("$filename", "r");
    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE)
    {
    $import="INSERT into customers(fname,lname,company,address,city,state,country,postal_code,phone,email) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]')";
    mysql_query($import) or die(mysql_error());
    }
    fclose($handle);
    print "Import done";
    }
    else
    {
    print "<form enctype='multipart/form-data' method='post'>";
    print "Type file name to import:";
    print "<input type='file' name='file' id='file'>";
    print "<input type='submit' name='submit' value='submit'></form>";
    }
?>

Any help is apreciated.

Related: [How to make the header row be skipped in my while loop using fgetcsv?](http://stackoverflow.com/questions/4409911/how-to-make-the-header-row-be-skipped-in-my-while-loop-using-fgetcsv) — hakre, Dec 17 '12 at 05:54

score 14 · Answer 1 · edited May 25 '19 at 11:58

14

Consider using the SplFileObject to read CSV files, it supports iteration better, e.g. in conjunction with the standard SPL LimitIterator this is a piece of cake:

$file = new SplFileObject($filename);
$file->setFlags(SplFileObject::READ_CSV);
$it = new LimitIterator($file, 1);
foreach($it as $data) {
    $mask = "INSERT INTO customers (fname, lname, company, address, city, state, country, postal_code, phone, email) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";
    $sql  = vsprintf($mask, $data);
    mysql_query($sql) or die(mysql_error());
}

Additionally please notice:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 11:58

Zoe

27,060
21
118
148

answered Dec 17 '12 at 00:17

hakre

193,403
52
435
836

Is good, but how can i integrate this to my script?, after brace what data need to be inside? – Dar Dec 17 '12 at 00:28
@Dario: The `foreach` loop replaces your `while` loop you have in your code. – hakre Dec 17 '12 at 00:30
Notice: Undefined offset: for all lines, why?, yes i know about PDO, this is a test script, then i will integrate in in Codeigniter. – Dar Dec 17 '12 at 00:32
What does `var_dump($data);` give in your case? I also edited the answer to show the inner part adopted to your code. `SplFileObject` in CSV-Mode will return a zero-based array with the values per each line per each `foreach` iteration. – hakre Dec 17 '12 at 00:33
The response is: Column count doesn't match value count at row 1 – Dar Dec 17 '12 at 00:52
Was my mistake, now is Warning: vsprintf() [function.vsprintf]: Too few arguments in – Dar Dec 17 '12 at 01:00
That means there are more `%s` placeholder in the `$mask` than values in the `$data` array. check with `var_dump($data)` if that matches. You might have an empty line in the file (at the end for example), then `$data` is `array(NULL)`. You might need to filter that. – hakre Dec 17 '12 at 01:02

score 2 · Answer 2 · answered Dec 17 '12 at 00:15

fgetcsv($handle, 100000, ",") before while loop

$file = fopen($filename, "r");
    $handle = fopen("$filename", "r");

    $headers = fgetcsv($handle, 100000, ","); //gran headers, 

    // you can do additional check to see if headers are grabbed or is the exist or if it is first lien of data (depends if you are 100% sure that headers will exist)
    //and if they are not, reset the handle
    // rewind($handle);


    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE)
    {
    $import="INSERT into customers(fname,lname,company,address,city,state,country,postal_code,phone,email) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]')";
    mysql_query($import) or die(mysql_error());
    }
    fclose($handle);
    print "Import done";
    }
    else
    {
    print "<form enctype='multipart/form-data' method='post'>";
    print "Type file name to import:";
    print "<input type='file' name='file' id='file'>";
    print "<input type='submit' name='submit' value='submit'></form>";
    }

score 0 · Answer 3 · answered Dec 17 '12 at 00:19

Simple count can work

$count = 0;
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE)
{
    if($count)
    {
        $import="INSERT into customers(fname,lname,company,address,city,state,country,postal_code,phone,email) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]')";
        mysql_query($import) or die(mysql_error());
    }
    $count++;
}

Skip first line on import csv

3 Answers3