Import Excel to MySQL using PHP and Bootstrap

Import Excel to MySQL database is a very important method.Sometimes you have a very big list of items in an excel file and at sometimes you need to import all that list into your database so you can see that list on your website.Today I will show you how you can Import Excel to MySQL using PHP programming language and bootstrap frontend framework.

Working:

In this Import Excel to MySQL script, we made one HTML form in which we use file input HTML element. A user will choose the CSV file and press on the import button. A form will be submitted and on the PHP script we will get the file and open this file through fopen() method and then separate the values through while loop.

 

import excel to mysql using php

Import Excel to MySQL using PHP

Application Development Process:

 

Step-1) Creating HTML Form

Here we create the HTML form which contains the one file input element and one button.

<?php /* Developer: Ehtesham Mehmood Site: PHPCodify.com Script: Import Excel to MySQL using PHP and Bootstrap File: index.php */ ?>

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>Import Excel to MySQL using PHP and Bootstrap</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  </head>
  <body style="padding-top:50px;">






<div class="container">
<!-- container class is used to centered the body of the browser with some decent width-->
    


<div class="row">
<!-- row class is used for grid system in Bootstrap-->
        


<div class="col-md-4 col-md-offset-4">
<!--col-md-4 is used to create the no of colums in the grid also use for medimum and large devices-->
            



<div class="login-panel panel panel-success">

<div class="panel-heading">

<h3 class="panel-title">Import Excel to MySQL</h3>



                </div>


<div class="panel-body">

<form method="post" action="import.php" enctype="multipart/form-data">

<fieldset>

<div class="form-group">
                              <input type="file" name="file"/>
                            </div>



                        <input class="btn btn-success" type="submit" name="submit_file" value="Submit"/>
                        </fieldset>




                    </form>



                </div>





            </div>




        </div>




    </div>




</div>




</body>
</html>


Step-2) Creating MySQL Database

For importing excel into MySQL database first we have to create a database.

CREATE DATABASE import-excel-to-mysql;

Step-3) Create Employee Table

Now we will create the table named the employee. Below is the SQL query.

 

Step-4) Creat Database Connection with PDO

Now we will create the database connection with our application using PDO.You may also like PDO CRUD Operations.

<?php /* Developer: Ehtesham Mehmood Site: PHPCodify.com Script: Import Excel to MySQL using PHP and Bootstrap File: db_con.php */ $DB_host = "localhost"; $DB_user = "root"; $DB_pass = ""; $DB_name = "import-excel-to-mysql"; try { $DBcon = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass); $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 catch(PDOException $e)
 {
     echo "ERROR : ".$e->getMessage();
 }

Step-5) Import.php Script

We make a PHP file named import.php. In this file, we are getting the contents of the uploaded Excel CSV file through while loop and using the fgetcsv function we are separating comma separated values one by one and then storing into the MySQL Database using PDO library. You can download Excel CSV file from here for your demo importing.


<?php /* Developer: Ehtesham Mehmood Site: PHPCodify.com Script: Import Excel to MySQL using PHP and Bootstrap File: import.php */ // Including database connections require_once 'db_con.php'; if(isset($_POST["submit_file"])) { $file = $_FILES["file"]["tmp_name"]; $file_open = fopen($file,"r"); while(($csv = fgetcsv($file_open, 1000, ",")) !== false) { $employee_name = $csv[0]; $employee_designation = $csv[1]; $employee_salary = $csv[2]; $stmt = $DBcon->prepare("INSERT INTO employee(employee_name,employee_designation,employee_salary) VALUES(:employee_name,:employee_designation,:employee_salary)");

$stmt->bindparam(':employee_name', $employee_name);
$stmt->bindparam(':employee_designation', $employee_designation);
$stmt->bindparam(':employee_salary', $employee_salary);
$stmt->execute();
}
}

echo "CSV Imported Successfully";
?>




download-code excel export data




Conclusion:

So this how we can import excel to MySQL database using PHP.Now you can easily import excel CSV files into MySQL database.If you like this tutorial script then share it with your community. Feel free to comments.

Read previous post:
Insert Data in PHP using jQuery AJAX without Page Refresh

In this article, we will see step by step that how we can Insert Data in PHP using jQuery AJAX...

Close