How to Import Excel or CSV file to MySQL Database using PHP though (phpspreadsheet.)

 

 

Step1: Download the PHP spreadsheet using composer

Click to spreadsheet site:- 

composer require phpoffice/phpspreadsheet 

Step 2:- Make the database name is and create the table

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL
);

Step 3:- Make the excel sheet and csv any one you have created.

Step 4 :- Create the file Html file like below screen shot

Index.php

<?php

session_start();
 

if(isset($_SESSION['status'])) {
    echo $_SESSION['status'];
    unset($_SESSION['status']);
}

?>

<form method="post" action="code.php" enctype="multipart/form-data">
    <input type="file" name="import_file"/>
    <input type="submit" name="submit"/>
</form> 

Step 5:- PHP code and include the lib

Code.php

 <?php
session_start();

$con=mysqli_connect('localhost','root','','test');
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

if(isset($_POST['submit'])){
    $allowed_ext = ['xls','csv','xlsx'];
    $fileName = $file=$_FILES['import_file']['name'];
    $checking = explode(".",$fileName);

    $file_ext = end($checking);

    if(in_array($file_ext, $allowed_ext)){
        $targetPath = $_FILES['import_file']['tmp_name'];//'./sampleData/example1.xls';
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($targetPath);

//Vishal concept is workin on that libary

        // foreach($spreadsheet->getWorksheetIterator() as $sheet){
        //     $getHighestRow=$sheet->getHighestRow();
        //     $getHighestRow=$sheet->getHighestRow();
        //     for($i=0;$i<=$getHighestRow;$i++){
        //         $name=$sheet->getCellByColumnAndRow(1,$i)->getValue(); // get name
        //         $email=$sheet->getCellByColumnAndRow(2,$i)->getValue(); // get email
        //         $sql = mysqli_query($con,"SELECT * FROM `user` WHERE name = '$name' AND email = '$email'");       
        //         if($name!='' && empty($sql->num_rows)){
        //             mysqli_query($con,"insert into user(name,email) values('$name','$email')");
        //         }
        //     }
        // }

        //other concept is working fine
        $data = $spreadsheet->getActiveSheet()->toArray();

        foreach($data as $row) {
           $name = $row['0'];
            $email = $row['1'];
            //$email = $row['2'];

            $checkStudent = mysqli_query($con,"SELECT * FROM `user` WHERE name = '$name' AND email = '$email'");   

            if(mysqli_num_rows($checkStudent) < 0 ){
                //Already Exists means please update
                $up_query = "UPDATE student SET name='$name', email='$email' WHERE name = '$name' AND email = '$email'";
                $up_result = mysqli_query($con,$up_query);
                $msg = 1;

            }else{
                //New recode Insert
                $in_query = "INSERT INTO user(name,email) values('$name','$email')";
                $in_result = mysqli_query($con, $in_query);
                $msg = 1;
            }
        }
        if(isset($msg)){
            $_SESSION['status'] = "File Imported Successfully";
            header('location:index.php');
        }else{
            $_SESSION['status'] = "File Importing Failed";
            header('location: index.php');
        }

    }else{
        $_SESSION['status'] = 'Invalid File';
        header('location: index.php');
        exit(0);
    }

}

?>

Step 3: Download the full code click me.