PHP Skip first row in CSV when uploading

Question

i have this PHP code that uploads a CSV and inserts data into a database, once it has uploaded in the while loop it only selects one row from the CSV:

//do upload
    if (is_uploaded_file($_FILES['dd_submission_form']['tmp_name']))
    {
        echo "<h3>" . "File ". $_FILES['dd_submission_form']['name'] ." uploaded successfully." . "</h3>";
        echo '<h3>Display file contents:</h3>';
        //readfile($_FILES['dd_submission_form']['tmp_name']);
    }

    //Import uploaded file to Database
    $handle = fopen($_FILES['dd_submission_form']['tmp_name'], "r");

    fgetcsv($handle, 1000, ",");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
    {
        $sql="SELECT * from customer where directdebit_reference = '".$data[0]."' ";
        echo $sql;
        $rs=mysql_query($sql,$conn) or die(mysql_error());
        $customer=mysql_fetch_array($rs);
        if(mysql_num_rows($rs) > 0)
        {
            $sql2="INSERT into dd_submissions (customer_seq, dd_reference, sortcode, account_number, account_name, amount, bacs_code, invoice_no, title, initial, forename, surname, salutation_1, salutation_2, address_1, address_2, area, town, postscode, phone, mobile, email, notes) values ('".$customer["sequence"]."', '$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]', '$data[19]', '$data[20]', '$data[21]', '$data[22]')";
            $rs2=mysql_query($sql2,$conn) or die(mysql_error());
        }
    }
    fclose($handle);
    print "<br><br><h3>Successfully Imported Clients</h3><br>";

robz228 · Accepted Answer · 2013-08-02 14:57:26Z

4

Just address the first row before the loop separately and do nothing with it to skip it.

fgets($handle); then your loop

answered Aug 2, 2013 at 14:57

robz228

6401 gold badge4 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

robz228 Over a year ago

@charlie Sorry, not sure. Pretty new to coding, Might be better off making a new question now, or trying the method vinodadhikary posted below. I just knew how to skip the first row from having this issue myself :p

vee · Accepted Answer · 2013-08-02 17:52:38Z

2

The simplest way would be to make use of the continue keyword inside the while loop like follows:

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
  if ($i == 0) { $i++; continue; }
  ...
  $i++;

However, to be safe you have to make sure that the first row does not contain required data.

edited Aug 2, 2013 at 17:52

answered Aug 2, 2013 at 14:56

vee

38.7k7 gold badges76 silver badges80 bronze badges

3 Comments

Trent Over a year ago

This will skip every row as yor $i++ is after the continue :)

vee Over a year ago

@Trent, you're right, thanks. It's so simple to overlook things isn't it? :)

Trent Over a year ago

it happens more frequently on the easier answers; stuff we probably copy and paste a thousand times a year from existing code

Spudley · Accepted Answer · 2013-08-02 15:04:57Z

I recommend you replace all of this code with a single call to mySQL's LOAD DATA INFILE query, which is designed to load CSV files directly into the database without needing all the PHP loops you're doing. It runs massively faster than doing it in a PHP loop.

Your customer field can be added easily using a subquery as part of the main LOAD DATA INFILE query.

Oh, and to answer the original question, it is also capable of skipping the first row (or rows) as part of the standard syntax.

See the mySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

[EDIT]

Your query would look roughly like this:

LOAD DATA INFILE
INTO TABLE dd_submissions
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROW
(
@dd_reference,
sortcode,
account_number,
account_name,
amount,
bacs_code,
invoice_no,
title,
initial,
forename,
surname,
salutation_1,
salutation_2,
address_1,
address_2,
area,
town,
postscode,
phone,
mobile,
email,
notes
)
SET
    customer_seq = SELECT sequence from customer where directdebit_reference = @dd_reference,
    dd_reference = @dd_reference
;

Cuagau · Accepted Answer · 2013-08-02 15:14:04Z

0

I suspect the problem you are having might be because of the "1000", which is unnecessary anyways. An easy way to skip the first line is to do the following:

fgetcsv($handle) //skips the first line
while($data = fgetcsv($handle)) { //will process second line to end of file
  ...
}

However, you have a much bigger problem here. If your csv file looks like:

'; DROP TABLE customer #

Then $sql becomes

SELECT * from customer where directdebit_reference = ''; DROP TABLE customer #'

And now you have no customers. Look up SQL injection to learn how to make sure the queries that you are passing on to MySQL are safe.

answered Aug 2, 2013 at 15:14

Cuagau

5483 silver badges7 bronze badges

Comments

Rahul Gupta · Accepted Answer · 2013-10-08 07:57:49Z

0

One probable reason of the breaking of the reading the rows from the csv file is that the data in your csv file might contain single or double quotes which breaks the sql query, so this problem can be solved by using the mysql_real_escape_string function. So your new insert query would look like :

....
......

$sql2="INSERT into dd_submissions (customer_seq, dd_reference, sortcode, account_number, account_name, amount, bacs_code, invoice_no, title, initial, forename, surname, salutation_1, salutation_2, address_1, address_2, area, town, postscode, phone, mobile, email, notes) values ('".mysql_real_escape_string($customer["sequence"])."', '".mysql_real_escape_string($data[0])."', .......);

hope it helps...

answered Oct 8, 2013 at 7:57

Rahul Gupta

10.2k7 gold badges66 silver badges69 bronze badges

Comments

ρяσѕρєя K · Accepted Answer · 2017-01-02 07:40:56Z

0

Declare one variable before while loop:

 $row=1;

then in while loop put this condition:

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

edited Jan 2, 2017 at 7:40

ρяσѕρєя K

133k54 gold badges203 silver badges216 bronze badges

answered Jan 2, 2017 at 7:19

mohitkantgupta

291 silver badge7 bronze badges

Collectives™ on Stack Overflow

PHP Skip first row in CSV when uploading

6 Answers 6

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related