Dear Mates, Please click the ads to support us.

Import excel data into database using PHP

Kailash Singh

Import excel data into database using PHP

Published Oct 25,2019 by Kailash Singh

0 Comment     3096 Views    


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


Comments ( 0 )