CODEWITHSUNDEEP
phpmysqlcsv
Mayeenul Islam
Mayeenul Islam

Reputation: 4762

How to exclude column header from CSV file in PHP?

When my client's are giving their inputs into the Excel file, they need the column header. But when they export the file into CSV (.csv) it includes the column heads into it. That's problem.

+---------+--------+-------------+
| Post id |  Name  | Designation |
+---------+--------+-------------+
    101     noYon      designer
    102     nakib     accountant
    103     nishi      developer
+---------+--------+-------------+

In my CSV (comma delimited) I got:

Post id,Name,Designation
101,noYon,designer
102,nakib,accountant
103,nishi,developer

I need to exclude the column heads (the first row) from the CSV when uploading it to MySQL using PHP.

Is there any way?

Upvotes: 2

Views: 3509

Answers (3)

Mamta
Mamta

Reputation: 11

$filename = $_FILES["file"]["tmp_name"];
if ($_FILES["file"]["size"] > 0){
    $file = fopen($filename, "r");
    fgetcsv($file);
    while (($getData = fgetcsv($file, 10000, ",")) !== false){
        echo "<pre>";
        print_r($getData);
        echo "</pre>";
    }

    fclose($file);
}

Upvotes: 0

user1864610
user1864610

Reputation:

A simple solution: read the first line and discard it before reading the rest of the file line by line in a loop:

//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file,"r");
fgetcsv($handle);  // Discard first line. If we know the maximum line length
                   // add it as second argument: it's slightly faster 

while( $data = fgetcsv( $handle) ) {
   $query = mysqli->query( "INSERT INTO table (post_id,name,designation) VALUES ('". 
                implode($data,"','")."'"; 
}

A faster solution which I've not coded here: append multiple sets of values to the INSERT like this:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Upvotes: 1

Mayeenul Islam
Mayeenul Islam

Reputation: 4762

With assistance of This article and This forum thread I came up to a viable solution for me with personal assistance of my colleague Ms. Tahmina Aktar Nishi. Hope it will help anyone trying something like this:

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

        <?php
        // make the loop and do upload

        $find_header = 0; // a simple counter

        while( $data = fgetcsv( $handle,6000,",","'") ) {
            $find_header++; //update counter

            if( $find_header > 1 ) {
                $sql = mysql_query( "INSERT INTO table_name (
                        post_id,
                        name,
                        designation
                    )
                    VALUES
                    (
                        '".$data[0] ."',
                        '".$data[1] ."'
                        '".$data[2]."'
                    )
                ");
            }
        } // endwhile

The logic is very simple and a bit childish. If we know how fgetcsv() works. fgetcsv() is:

The fgetcsv() function parses a line from an open file, checking for CSV fields.

The fgetcsv() function stops returning on a new line, at the specified length, or at EOF, whichever comes first.

That means it fetches each line of the CSV each time the loop rounds. So if we pass a simple PHP counter and exclude the first line, then it's that simple.

And of course, don't forget to sanitize the data using PHP sanitizing functions like trim() or addslashes() etc. :)

You can get my whole bunch of codes HERE.

Upvotes: 1

Related Questions