mysql insert into table from csv - drop first row

Question

is it possible to insert a csv file into a table and instruct the query to always ignore the first row from the csv file. Users will be uploading files but the first row will be the column headings so I need the query to ignore the first row. My script looks like this at the moment:

<?php
include 'datalogin.php';

      move_uploaded_file($_FILES["fileCSV"]["tmp_name"],
      "quiz/" . $_FILES["fileCSV"]["name"]);

    $objCSV = fopen("quiz/".$_FILES["fileCSV"]["name"], "r");
        while (($objArr = fgetcsv($objCSV, 1000, ",")) !== FALSE) {
        $strSQL = "INSERT INTO ex_question1 ";
        $strSQL .="(id,tn,qnr,qtype,pic,question,option1,option2,option3,option4) ";
        $strSQL .="VALUES ";
        $strSQL .="('".$objArr[0]."','".$objArr[1]."','".$objArr[2]."','".$objArr[3]."','".$objArr[4]."','".$objArr[5]."','".$objArr[6]."','".$objArr[7]."','".$objArr[8]."','".$objArr[9]."') ";
        $objQuery = mysql_query($strSQL);
    }
    fclose($objCSV);

    echo "Import completed.";
?>

You might want to investigate LOAD DATA INFILE dev.mysql.com/doc/refman/5.1/en/load-data.html — liquorvicar
– liquorvicar, Commented Dec 8, 2011 at 14:51

knittl · Accepted Answer · 2011-12-08 14:42:14Z

3

Simply call fgetcsv($objCSV, 1000, ",") once before going into the loop:

 $objCSV = fopen("quiz/".$_FILES["fileCSV"]["name"], "r");
 fgetcsv($objCSV, 1000, ","); // skip first row
 while (($objArr = fgetcsv($objCSV, 1000, ",")) !== FALSE) {
    $strSQL = "INSERT INTO ex_question1 ";
    $strSQL .="(id,tn,qnr,qtype,pic,question,option1,option2,option3,option4) ";
    $strSQL .="VALUES ";
    $strSQL .="('".$objArr[0]."','".$objArr[1]."','".$objArr[2]."','".$objArr[3]."','".$objArr[4]."','".$objArr[5]."','".$objArr[6]."','".$objArr[7]."','".$objArr[8]."','".$objArr[9]."') ";
    $objQuery = mysql_query($strSQL);
}
fclose($objCSV);

NB. your code is prone to SQL injections, please escape your input variables properly or use prepared statements!

answered Dec 8, 2011 at 14:42

knittl

269k59 gold badges339 silver badges405 bronze badges

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

Comments

Nicola Cossu · Accepted Answer · 2011-12-08 14:44:07Z

2

Consider even LOAD DATA command and IGNORE option

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

answered Dec 8, 2011 at 14:44

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Collectives™ on Stack Overflow

mysql insert into table from csv - drop first row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related