How to Skip Header from Excel Spreadsheet While Uploading to MySQL

Opeyemi AbdulRasheed
Opeyemi AbdulRasheed used Ask the Experts™
on
Hello Experts!

Is it possible by code to skip the Header (Top Row) in Excel Spreadsheet?
I'm using PHPExcel_Reader to process upload into database.
See attached.
Screenshot_4.png
Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Hi Opeyemi,

You'll need to show us your code.

I would guess that if you're running a for loop to get the data, then you would start at row 1 instead of row 0
Mark BradyPrincipal Data Engineer

Commented:
yes agreed. Skip the first row as you iterate over the spreadsheet.
<?php
require_once('../../php-excel-reader/excel_reader2.php');
require_once('../../SpreadsheetReader.php');

if (isset($_POST["import"]))
{
    
    
  $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
  
  if(in_array($_FILES["file"]["type"],$allowedFileType)){

        $targetPath = '../assets/uploads/'.$_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
        
        $Reader = new SpreadsheetReader($targetPath);
        
        $sheetCount = count($Reader->sheets());
        for($i=0;$i<$sheetCount;$i++)
        {
            
            $Reader->ChangeSheet($i);
            
            foreach ($Reader as $Row)
            {
          
                $student_id = "";
                if(isset($Row[0])) {
                    $student_id = mysqli_real_escape_string($conn,$Row[0]);
                }
                
                $roll_no = "";
                if(isset($Row[1])) {
                    $roll_no = mysqli_real_escape_string($conn,$Row[1]);
                }

                $student_name = "";
                if(isset($Row[2])) {
                    $student_name = mysqli_real_escape_string($conn,$Row[2]);
                }

                $class_name = "";
                if(isset($Row[3])) {
                    $class_name = mysqli_real_escape_string($conn,$Row[3]);
                }

                $date_of_birth = "";
                if(isset($Row[4])) {
                    $date_of_birth = DateTime::createFromFormat("d/m/Y", $Row[4]); // create a new datetime object
                    $dob = $date_of_birth->format("Y-m-d"); // format the datetime object
                }

                $gender = "";
                if(isset($Row[5])) {
                    $gender = mysqli_real_escape_string($conn,$Row[5]);
                }

                $state = "";
                if(isset($Row[6])) {
                    $state = mysqli_real_escape_string($conn,$Row[6]);
                }

                $year_of_adm = "";
                if(isset($Row[7])) {
                    $year_of_adm = mysqli_real_escape_string($conn,$Row[7]);
                }
                
                $password = mt_rand(10000000,99999999);
        
                if (!empty($student_id) || !empty($roll_no) || !empty($student_name) || !empty($class_name) || !empty($date_of_birth) || !empty($gender) || !empty($state) || !empty($year_of_adm) ) {
                    $query = "INSERT INTO ... (...,...,..) VALUES('".$student_id."','".$roll_no."','".$student_name."','".$class_name."','".$dob."','".$gender."','".$state."','".$year_of_adm."','".$password."')";
                    $result = mysqli_query($conn, $query);
                
                    if (! empty($result)) {
                        $type = "success";
                        $message = "Students' Data Imported into the Database";
                    } else {
                        $type = "error";
                        $message = $conn->error;
                    }
                }
             }
        
         }
  }
  else
  { 
        $type = "error";
        $message = "Invalid File Type. Choose Excel File.";
  }
}
?>

Open in new window

Please if you think the script could be better, help me out.
In addition, please help me re-arrange the logic so it goes like this:
If no file is selected:
   $message = "Select Excel file to continue"

If wrong file is selected:
   $message = "Invalid File Type. Choose Excel File Only"

Thank you so much
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
OK. It looks like there's no built in method for skipping the first row, so you'll have to roll your own. Something like this:

for($i=0;$i<$sheetCount;$i++)
{            
    $Reader->ChangeSheet($i);

    $skipRows = 1;
    $currentRow = 0;

    foreach ($Reader as $Row)
    {
        if ($currentRow < $skipRows) continue;
        $currentRow++;

        $student_id = "";
        if(isset($Row[0])) {
            $student_id = mysqli_real_escape_string($conn,$Row[0]);
        }
        ...      
    }
}

Open in new window

As for your logic, something like this might work:

if (isset($_POST["import"]))
{

    $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

    if ( ! isset($_FILES['file']) || empty($_FILES['file']['name']) {

        $message = "Select Excel file to continue";

    } elseif ( ! in_array($_FILES["file"]["type"],$allowedFileType) ) {

        $message = "Invalid File Type. Choose Excel File Only";

    } else {

        $targetPath = '../assets/uploads/'.$_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
        ...

    }

}

Open in new window

Thank you so much sir

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial