Published Oct 25,2019 by Kailash Singh
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

Be First to Comment

Leave a Reply