Import CSV and XLSX File - Bluethink Inc.

Spread the love

Follow The Below Step To Import CSV, XLSX File Data Into MYSQL Database Using PHP Spreadsheet Library.

Step 1: Create PHP Project
Step 2: Create database
Step 3: Create Table In Database
Step 4: Create Database Connection File
Step 5: Create HTML Form to Upload CSV, XLSX File
Step 6: Create PHP File To Import CSV,XLSX File Data Into Database

Step 1: CREATE PHP PROJECT

First of all, visit you web server directory and create a new project which name excel.

Step 2: Open the localhost/phpmyadmin and click top new button.

And write database name Image and click create button

Step 3: CREATE TABLE IN DATABASE

CREATE TABLE `upload_table` (
 `id`  int(11)  NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `name`   varchar(50)  NOT NULL,
 `email`  varchar(50)   NOT NULL,
 `address` varchar(15)  NOT NULL
)

CREATE TABLE `upload_table` (

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` varchar(50) NOT NULL,

`email` varchar(50) NOT NULL,

`address` varchar(15) NOT NULL

)

Step 4: CREATE Database Connection File

Create a File Name database.php and include into your PHP file.

<?php 

  $servername = 'localhost';
  $username ='root';
  $password ='';
  $dbname = "image";
  $conn =  mysqli_connect( $servername , $username ,$password, $dbname);
  if(!$conn){
      die('Could not Connect MySql Server:' .mysql_error());
    }

?>

<?php

$servername = 'localhost';

$username ='root';

$password ='';

$dbname = "image";

$conn = mysqli_connect( $servername , $username ,$password, $dbname);

if(!$conn){

die('Could not Connect MySql Server:' .mysql_error());

}

Step 5: CREATE HTML Form To Upload CSV,XLSX File.

Create a simple html form which name is import.php

<?php session_start();?>
<!doctype html>
<html lang="en">
   <head>
      <!-- Required meta tags -->
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width, initial-scale=1">
      <!-- Bootstrap CSS -->
      <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css"      rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
      <title>Import</title>
   </head>
   <body>
      <div class="container mt-5">
         <div class="row">
            <div class="card">
               <div class="card-header">
                  <?php include(“import_session.php”);?>
                  <h4>How to import excel data into database in php</h4>
               </div>
               <div class="card-body">
                   <form action="import_data.php" method="post" enctype="multipart/form-data"    
                     >
                     <input type="file" name="import_file"  value="import" class="form-  
                     control" /><br>
                     <button type="submit" name="submit" class="btn btn-success  
                     ">Import</button>
              </div>
                </form>
            </div>
         </div>
      </div>
      <script 
         src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"
         integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM"
         crossorigin="anonymous"></script>
   </body>
</html>

<?php session_start();?>

<!doctype html>

<head>

<title>Import</title>

</head>

<body>

<?php include(“import_session.php”);?>

<h4>How to import excel data into database in php</h4>

</div>

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

<input type="file" name="import_file" value="import" class="form-

control" /><br>

<button type="submit" name="submit" class="btn btn-success

">Import</button>

</div>

</form>

</div>

<script

src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"

integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM"

crossorigin="anonymous"></script>

</body>

</html>

Output Like this-

Step 6: Create PHP File to import CSV,XLSX File Data into database

Before follow this step , you check in your system composer is install or not.

Using this command check composer is install or not.

Composer –V

If composer install then go to Step 12:

If composer is not install then follow this step and install the composer.

Step 6: Open the any browser and write url is -> getcomposer.org when you click this link

Then open the screen like this

Then click download button

Step 7: Then click on the composer-Setup.exe

Step 8: Then click on “install for all users”.

Step 8: Do not make any changes and click next button

Step 9: You can see the installation location here click next.

Step 9: Do not any changes click next

Step 10: click the install button

Step 11: then click next and finally click finish button

Then check composer is install using composer –V command

Step 12: After install the composer.

You run this command –

Composer require phpoffice/phpspreadsheet

Step 13: Then put it on PHP File code to import CSV,XLSX File Data into database and php file name is import_data.php

<?php 
    session_start();
    include("database.php");
   require 'vendor/autoload.php';
   use PhpOffice\PhpSpreadsheet\Spreadsheet;
   use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
   if(isset($_POST['submit']))
   {
       $filename = $_FILES["import_file"]['name'];
   
       $pathdata = (pathinfo($filename));
       $fileext = $pathdata['extension'];
       $allowed_ext = ['xlsx','csv'];
       if(in_array($fileext,$allowed_ext))
       {
           $inputFileNamePath = $_FILES["import_file"]['tmp_name'];
           $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileNamePath);
           $data = $spreadsheet->getActiveSheet()->toArray();
           $count="0";
           foreach($data as $row)
           {
            if($count>0)
            {
              $name = $row['0'];
              $email = $row['1'];
              $address = $row['2'];
              $sql = "insert into upload_table(name,email,address)   
              values('$name','$email','$address')";
              $result = mysqli_query($conn,$sql);
            }
            else 
            {
              $count="1";
            }
           }
           if($result)
           {
             $_SESSION['data'] =  "Data saved successfully";
             header("location:import.php");
             die();
           }
           else 
           {
             echo "Data not saved";
           }
       }
         else 
         {
            $_SESSION['msg'] =  "Extension not allowed";
            header("location:import.php");
            die();
       }  
   }
   ?>

<?php

session_start();

include("database.php");

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

if(isset($_POST['submit']))

{

$filename = $_FILES["import_file"]['name'];

$pathdata = (pathinfo($filename));

$fileext = $pathdata['extension'];

$allowed_ext = ['xlsx','csv'];

if(in_array($fileext,$allowed_ext))

{

$inputFileNamePath = $_FILES["import_file"]['tmp_name'];

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileNamePath);

$data = $spreadsheet->getActiveSheet()->toArray();

$count="0";

foreach($data as $row)

{

if($count>0)

{

$name = $row['0'];

$email = $row['1'];

$address = $row['2'];

$sql = "insert into upload_table(name,email,address)

values('$name','$email','$address')";

$result = mysqli_query($conn,$sql);

}

else

{

$count="1";

}

if($result)

{

$_SESSION['data'] = "Data saved successfully";

header("location:import.php");

die();

}

else

{

echo "Data not saved";

}

else

{

$_SESSION['msg'] = "Extension not allowed";

header("location:import.php");

die();

}

Step 14: Above html form I am using session

And the session file name is import_session.php and code

<?php 
   if(isset($_SESSION['data'])=="Data saved successfully")
   {
       ?>
    <div class="alert alert-warning alert-dismissible fade show"   role="alert">
    <strong>Hey!</strong> <?php  echo $_SESSION['data'];
      unset( $_SESSION['data']);
      ?>
   <button type="button" class="btn-close " data-bs-dismiss="alert" aria- 
    label="Close"></button>
   </div>
  <?php 
   }
   
   else 
   {
       if(isset($_SESSION['msg']))
   {
       ?>
  <div class="alert alert-warning alert-dismissible fade show"   role="alert">
   <strong>Hey!</strong> <?php  echo $_SESSION['msg'];
      unset( $_SESSION['msg']);
      ?>
   <button type="button" class="btn-close " data-bs-dismiss="alert" aria-  
   label="Close"></button>
</div>
<?php 
   }
   }
   ?>