Import excel data into database using PHP

In this tutorial, we are going to learn about how to Import excel (.csv) data into database using PHP.

Step 1 :- Create tables in Database.

In this step, we are going to create table in database.

CREATE TABLE `codingmantra`.`excel_detail` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(200) NOT NULL , `phone` VARCHAR(50) NOT NULL , `email` VARCHAR(200) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

Step 2 :- Create .csv file.

Step 3 :- Save as .csv format.

Step 4 :- Create design to upload file.

<!doctype html>
<html>
    <head>
      <title>Codingmantra</title>
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
      <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
      <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
      <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
    </head>
    <body>
        <div class="container">
          <div class="row" align="center" style="margin-top: 70px;">
              <div class="col-sm-3"></div>
              <div class="col-sm-6">
                    <h2>Import excel data into database using PHP</h2><br>

                    <!-- Success message print here -->
                    <?php echo $msg; ?>


                    <form method="post" action="" enctype='multipart/form-data'>
                        <input type='file' name='file' class="form-control" /><br>
                        <input type='submit' class="btn btn-primary" value='Upload Data' name='import'>
                    </form>
              </div> 
              <div class="col-sm-3"></div>
          </div>        
        </div>
    </body>
</html>

Step 5 : Create PHP Code to import data form the csv file.

<?php 
 
// connection of database
 $con = mysqli_connect('localhost','root','','codingmantra');


$msg = '';


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

    $filename = $_FILES["file"]["tmp_name"];

    if($_FILES["file"]["size"] > 0)
    {
        
        $file = fopen($filename, "r");

        while (($col = fgetcsv($file, 10000, ",")) !== FALSE) 
        {
           // echo'<pre>'; print_r($col);

            $insert = "INSERT INTO excel_detail (name,phone,email)values('".$col[0]."','".$col[1]."','".$col[2]."')";
            mysqli_query($con,$insert);


        }
        $msg = '<p style="color: green;"> CSV Data inserted successfully</p>';

    }

}

?>

Hope this will help our developers.

Because we believe Mantra means CodingMantra