skip specific rows and columns of csv with PHP

Question

Hi I would like to know how can I skip certain colums/rows from a csv. I have a form that uploads a cvs to MySQL and works fine, I also know how to skip the first line of the csv, but the format of the file I need to upload seems pretty hard for me. This is my code:

<body>

<div class="container">

[enter image description here][1]<?php
if(isset($_POST['uploadBtn'])){
    $fileName=$_FILES['myFile']['name'];
    $fileTmpName=$_FILES['myFile']['tmp_name'];
    //FILE PATH
    $fileExtension=pathinfo($fileName,PATHINFO_EXTENSION);
    //ALLOWED FILE TYPES
    $allowedType = array('csv');
    if(!in_array($fileExtension,$allowedType)){?>

        <div class="alert alert-danger">
            INVALID FILE
        </div>
    <?php }else{

        $handle = fopen($fileTmpName, 'r');
        fgetcsv($handle);///////////////// SKIP FIRST ROW
        while (($myData = fgetcsv($handle,1000,','))!== FALSE){
                $name = $myData[0];
                $email = $myData[1];

                $query = "INSERT INTO databse.excel_table (name,email)
                VALUES ('".$name."','".$email."')";
                $run = mysql_query($query);

        }
        if(!$run){
            die("error in uploading file".mysql_error());
        }else{ ?>
                <div class="alert alert-success">
                    SUCCESS
                </div>
    <?php   }
    }
}
    ?>

<form action="" method="post" enctype="multipart/form-data">
    <h3 class="text-center">
        RESULTS
    </h3></hr>
    <div class="row">
        <div class="col-md-6">
            <div class="form-group">
                <input type="file" name="myFile" class="form-control">
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-6">
            <div class="form-group">
                <input type="submit" name ="uploadBtn" class="btn btn-info">
            </div>
        </div>
    </div>
</form>
</div>
</body>
</html>

Thanks in advance. example of csv

what are the conditions of those certain rows? Why not using if-then clause? — Ali Sheikhpour
– Ali Sheikhpour, Commented Oct 25, 2017 at 20:57
I need to upload all values from D 15 to D38 but I dont know how — Jiga
– Jiga, Commented Oct 25, 2017 at 21:05

JoeCrash · Accepted Answer · 2017-10-25 21:53:30Z

1

Using your loop, here is a way to pull that off.

USE ONE OF THESE NOT BOTH.

$rowsToSkip = [0,3,6,9]; //this is one way to manually enter row numbers
$rowsToSkip = range(5,10) //will return an array ex: [5,6,7,8,9,10]

AND SETUP YOUR LOOP

$i = 0; //this is used to keep an track of what row you are on
while (($myData = fgetcsv($handle,1000,','))!== FALSE){
  if($i == 0) continue; //this will skip your 1st row
  if(in_array($i, $rowsToSkip)) continue; //this will skip any row in $rowsToSkip array

  $name = $myData[0];
  $email = $myData[1];

  $query = "INSERT INTO databse.excel_table (name,email)
  VALUES ('".$name."','".$email."')";
  $run = mysql_query($query);
}

2 SUGGESTIONS / HELPFUL BUT NOT NEEDED TO GET THIS WORKING

Don't query in any for/while/each, its better to put the string together first then query once.
Don't use mysql, its old and insecure. USE PDO or mysqli instead

Your loop again

$i = 0; //this is used to keep an track of what row you are on
$values = []; //will hold your row values
while (($myData = fgetcsv($handle,1000,','))!== FALSE){
  if($i == 0) continue; //this will skip your 1st row
  if(in_array($i, $rowsToSkip)) continue; //this will skip any row in $rowsToSkip array

  $name = $myData[0];
  $email = $myData[1];

  $values[] = "('".$name."','".$email."')";
}

take the query out of the loop, use implode to add the values in later

$query = "INSERT INTO databse.excel_table (name,email) VALUES (". implode("," , $values) .")";
$run = mysql_query($query);

edited Oct 25, 2017 at 21:53

answered Oct 25, 2017 at 21:18

JoeCrash

5322 silver badges9 bronze badges

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

6 Comments

Jiga Over a year ago

wait im a little too slow to understand this, after: $handle = fopen($fileTmpName, 'r'); should I put the rest of the code? Because I did that but its not working, sorry but Im kinda bad for this :/

JoeCrash Over a year ago

I just read your comments, I'll tailor it, check in a few minutes

JoeCrash Over a year ago

this loop would go in between $handle = fopen($fileTmpName, 'r'); and if(!$run){

JoeCrash Over a year ago

this would skip the 1st 4 rows, allow the next 3 and skip the following 2 after $rowsToSkip = range(0,3) + range(7,8);

Jiga Over a year ago

should I make more changes besides the ones you did?

|

Sayonara · Accepted Answer · 2017-10-25 21:27:17Z

0

Count the lines and skipp all lines before 14th one.

    <body>

    <div class="container">
<?php
    if(isset($_POST['uploadBtn'])){
        $fileName=$_FILES['myFile']['name'];
        $fileTmpName=$_FILES['myFile']['tmp_name'];
        //FILE PATH
        $fileExtension=pathinfo($fileName,PATHINFO_EXTENSION);
        //ALLOWED FILE TYPES
        $allowedType = array('csv');
        if(!in_array($fileExtension,$allowedType)){?>

            <div class="alert alert-danger">
                INVALID FILE
            </div>
        <?php }else{

            $handle = fopen($fileTmpName, 'r');
            fgetcsv($handle);///////////////// SKIP FIRST ROW
            $k = 0;
            while (($myData = fgetcsv($handle,1000,','))!== FALSE){
             $k++;
              if ( $k > 14 ) {

                    $name = $myData[0];
                    $email = $myData[1];

                    $query = "INSERT INTO databse.excel_table (name,email)
                    VALUES ('".$name."','".$email."')";
                    $run = mysql_query($query);
                 }

            }
            if(!$run){
                die("error in uploading file".mysql_error());
            }else{ ?>
                    <div class="alert alert-success">
                        SUCCESS
                    </div>
        <?php   }
        }
    }
        ?>

    <form action="" method="post" enctype="multipart/form-data">
        <h3 class="text-center">
            RESULTS
        </h3></hr>
        <div class="row">
            <div class="col-md-6">
                <div class="form-group">
                    <input type="file" name="myFile" class="form-control">
                </div>
            </div>
        </div>
        <div class="row">
            <div class="col-md-6">
                <div class="form-group">
                    <input type="submit" name ="uploadBtn" class="btn btn-info">
                </div>
            </div>
        </div>
    </form>
    </div>
    </body>
    </html>

edited Oct 25, 2017 at 21:27

answered Oct 25, 2017 at 20:59

Sayonara

2771 silver badge10 bronze badges

2 Comments

Jiga Over a year ago

all colums are set I mean ther are not null the image was just an example

Jiga Over a year ago

this is the one that works for me, thaks Sayonara, btw how can i skip for example 4 rows, then read the next 3, then skip the next 2?

Collectives™ on Stack Overflow

skip specific rows and columns of csv with PHP

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related