CODEWITHSUNDEEP
phpmysql
Bala
Bala

Reputation: 3638

Removing the first row in a csv file upload to mysql database

I have the following code for uploading a csv file to a mysql database. Its working just fine, but if the csv file contains a heading for each column its getting uploaded in the first row of the table. I want to remove the first row of the csv file while getting stored in the database. How can i do that ?

<?php  

    //connect to the database 
    $connect = mysql_connect("localhost","root",""); 
    mysql_select_db("crm",$connect); //select the table 
    // 

    if ($_FILES[csv][size] > 0) { 

        //get the csv file 
        $file = $_FILES[csv][tmp_name]; 
        $handle = fopen($file,"r"); 

        //loop through the csv file and insert into database 
        do { 
            if ($data[0]) { 
                mysql_query("INSERT INTO lead (name, lead_value, status) VALUES 
                    ( 
                        '".addslashes($data[0])."', 
                        '".addslashes($data[1])."', 
                        '".addslashes($data[2])."' 
                    ) 
                "); 
            } 
        } while ($data = fgetcsv($handle,1000,",","'")); 
        // 

        //redirect 
        header('Location: import.php?success=1'); die; 

    } 

    ?> 

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
    <html xmlns="http://www.w3.org/1999/xhtml"> 
    <head> 
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> 
    <title>Import a CSV File with PHP & MySQL</title> 
    </head> 

    <body> 

    <?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?> 

    <form action="" method="post" enctype="multipart/form-data" name="form1" id="form1"> 
      Choose your file: <br /> 
      <input name="csv" type="file" id="csv" /> 
      <input type="submit" name="Submit" value="Submit" /> 
    </form> 

    </body> 
    </html>

Upvotes: 2

Views: 4575

Answers (3)

cristi _b
cristi _b

Reputation: 1813

example code from what I'm using, and it should work because I'm using it in production environment 

$handle = fopen("stuff.csv","r");

/* assuming that first row has collumns names */
$query = "INSERT INTO table(";
$cols = fgetcsv($handle,100000,';','"');
$query .= implode(", ",$cols).") VALUES";

$values = "";

    /* cycle through each row and build mysql insert query */
while($data = fgetcsv($handle,100000,';','"')) {
    $values .= " ( ";

    foreach($data as $text) {

        $values .= "'".$text."', ";

    }

    $values = substr($values,0,-2);

    $values .= "), ";

}

    /* remove last 2 chars */

$values = substr($values,0,-2);

$query .= $values;

echo $query;

fclose($handle);

Mind the fact that this script will return the mysql query... not execute it, so alter it to your needs.

Upvotes: 2

Ben English
Ben English

Reputation: 3918

If I am understanding you correctly you want to remove the header row? I would use LOAD DATA instead of INSERT INTO

LOAD DATA INFILE '/tmp/test.csv' INTO TABLE test FIELDS TERMINATED BY ',' IGNORE 1 LINES;

LOAD DATA is vastly superior performance-wise than INSERT.

MySQL - LOAD DATA

Upvotes: 1

Mike
Mike

Reputation: 8100

I think you mean that you want to not insert the first row of the CSV data into your table. Working on that assumption (note that I'm not a PHP programmer, so the changes I've made should be taken as pseudocode):

if ($_FILES[csv][size] > 0) { 
    $first_time = true;

    //get the csv file 
    $file = $_FILES[csv][tmp_name]; 
    $handle = fopen($file,"r"); 

    //loop through the csv file and insert into database 
    do {
            if ($first_time == true) {
            $first_time = false;
            continue;
        }

        if ($data[0]) { 
            mysql_query("INSERT INTO lead (name, lead_value, status) VALUES 
                ( 
                    '".addslashes($data[0])."', 
                    '".addslashes($data[1])."', 
                    '".addslashes($data[2])."' 
                ) 
            "); 
        } 
    } while ($data = fgetcsv($handle,1000,",","'")); 

    //redirect 
    header('Location: import.php?success=1'); die; 
}

Upvotes: 1

Related Questions