Don't want to Include certain rows in excel when I'm importing it to mysql using PHP

Question

This image is the file I wish to importI tried to import my excel file in MySQL, and I was able to do it but I want to remove certain rows and start importing from E4 instead of A1 cell. I only need some columns like the ones highlighted in yellow. This is my code, I only got this through a tutorial and want to make some difference.

if(isset($_POST['import_mandate']))
{
    $filename = $_FILES['import-file']['name'];
    $file_ext = pathinfo($filename, PATHINFO_EXTENSION);
    
    $allowed_ext = ['Xls','xlsx','csv'];
    
    if(in_array($file_ext, $allowed_ext))
    {
        $inputFileName = $_FILES['import-file']['tmp_name'];
        /** Load $inputFileName to a Spreadsheet object **/
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
        $data = $spreadsheet->getActiveSheet()->toArray();
        
      
        foreach($data as $row)
        {
                 
            $nycid = $row['4'];
            $lastname = $row['5'];
            $firstname = $row['6'];
            $grade = $row['10'];
            $adbn = $row['12'];
            $pldbn = $row['13'];
            $stype = $row['18'];
            $mb = $row['19'];
            $language = $row['20'];
            $ig = $row['21'];
            $gsize = $row['22'];
            $freq = $row['23'];
            $dur = $row['24'];
            $provider = $row['28'];

            $mandatequery = "INSERT INTO mandates (nycid, lastname, firstname, grade, adbn, pldbn, stype, mb, language, ig, gsize, freq, dur, provider) VALUES ('$nycid', '$lastname', '$firstname', '$grade', '$adbn', '$pldbn', '$stype', '$mb', '$language', '$ig', '$gsize', '$freq', '$dur', '$provider')";

            $query_run = mysqli_query($con,$mandatequery);
            $msg = true;
            
        }

        if(isset($msg))
        {
            $_SESSION['message'] = "Successfully Imported!";
            header("Location: mandates.php");
            exit(0);
        }
        else
        {
            $_SESSION['message'] = "Not Imported!";
            header("Location: mandates.php");
            exit(0);   
        }
    }
    else
    {
        $_SESSION['message'] = "Invalid File!";
        header("Location: mandates.php");
        exit(0);`enter code here`
    }

Is there any way I can have some code that will allow me to only get data from E4 cell of my excel file?

Please read up on SQL Injection and don't directly include variables from any source into a SQL string - https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php. — Nigel Ren, Jun 03 '22 at 16:55

score 0 · Accepted Answer · answered Jun 03 '22 at 16:53

0

To access specific parts of the spreadsheet rather than the whole sheet, you can use rangeToArray(). You can then specify E4 as the start point. To make sure you get all the rows, use...

$endRow = $spreadsheet->getActiveSheet()->getHighestRow();

This can be used to specify the end of the range to fetch...

$data = $spreadsheet->getActiveSheet()->rangeToArray('E4:X' . $endRow);

(Not sure if X is the right range for the end column, you can adjust this as required.)

One thing to note is that as this starts at column E, this columns value will be in the 0 element of the array, so you may need to adjust the way you access the data.

Just as an alternative - you could just skip the first couple of rows in the array of all of the data (you can use array_slice() to chop of the rows you don't want.)

answered Jun 03 '22 at 16:53

Nigel Ren

56,122
11
43
55

I got an error when I tried it. I'm actually getting some of the columns not all. Maybe when I write this code, it contradicts the foreach lines in my code. – Nine Delos Reyes Jun 03 '22 at 18:34
I edited my question above and added the file I wish to import. – Nine Delos Reyes Jun 03 '22 at 18:42
@NineDelosReyes as I mentioned, this code starts extracting from E4, which is what you asked for. So you will have to adjust the array references, `$row['4']` is what you use at the moment, but as the array starts at E4, this sounds like it needs to be `$row[0]` instead. – Nigel Ren Jun 03 '22 at 18:56

Don't want to Include certain rows in excel when I'm importing it to mysql using PHP

1 Answers1