Friday 4 March 2016

Read Excel Data and Insert into Mysql Database using PHP




In previous post I had show you how import mysql data into excel sheet but In this post we are going to learn how to import excel data into MySql Database using php. For import from excel to MySql Database I have use PHPExcel Class. PHPExcel library is best library for Export and Import from excel to database and from database to Excel sheet. By using this functionality user can easily transfer excel data to Mysql database table using PHP programming language. In post user can easily read excel sheet data and insert into mysql database table and show on webpage. This functionality is mainly used in enterprise level application and even small application we can use this type of functionality. I hope you will enjoy this post.

You can download PHPExcel class from this link - https://phpexcel.codeplex.com/releases/view/119187

Source Code

Database Table

 CREATE TABLE IF NOT EXISTS `tbl_excel` (  
  `excel_id` int(11) NOT NULL AUTO_INCREMENT,  
  `excel_name` varchar(250) NOT NULL,  
  `excel_email` varchar(300) NOT NULL,  
  PRIMARY KEY (`excel_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  

index.php



<?php
$connect = mysqli_connect("localhost", "root", "", "test");
$output = '';
if(isset($_POST["import"]))
{
 $extension = end(explode(".", $_FILES["excel"]["name"])); // For getting Extension of selected file
 $allowed_extension = array("xls", "xlsx", "csv"); //allowed extension
 if(in_array($extension, $allowed_extension)) //check selected file extension is present in allowed extension array
 {
  $file = $_FILES["excel"]["tmp_name"]; // getting temporary source of excel file
  include("PHPExcel/IOFactory.php"); // Add PHPExcel Library in this code
  $objPHPExcel = PHPExcel_IOFactory::load($file); // create object of PHPExcel library by using load() method and in load method define path of selected file

  $output .= "<label class='text-success'>Data Inserted</label><br /><table class='table table-bordered'>";
  foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
  {
   $highestRow = $worksheet->getHighestRow();
   for($row=2; $row<=$highestRow; $row++)
   {
    $output .= "<tr>";
    $name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
    $email = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
    $query = "INSERT INTO tbl_excel(excel_name, excel_email) VALUES ('".$name."', '".$email."')";
    mysqli_query($connect, $query);
    $output .= '<td>'.$name.'</td>';
    $output .= '<td>'.$email.'</td>';
    $output .= '</tr>';
   }
  } 
  $output .= '</table>';

 }
 else
 {
  $output = '<label class="text-danger">Invalid File</label>'; //if non excel file then
 }
}
?>

<html>
 <head>
  <title>Import Excel to Mysql using PHPExcel in PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
  <style>
  body
  {
   margin:0;
   padding:0;
   background-color:#f1f1f1;
  }
  .box
  {
   width:700px;
   border:1px solid #ccc;
   background-color:#fff;
   border-radius:5px;
   margin-top:100px;
  }
  
  </style>
 </head>
 <body>
  <div class="container box">
   <h3 align="center">Import Excel to Mysql using PHPExcel in PHP</h3><br />
   <form method="post" enctype="multipart/form-data">
    <label>Select Excel File</label>
    <input type="file" name="excel" />
    <br />
    <input type="submit" name="import" class="btn btn-info" value="Import" />
   </form>
   <br />
   <br />
   <?php
   echo $output;
   ?>
  </div>
 </body>
</html>

30 comments:

  1. gets this error pls help me

    Fatal error: Uncaught exception 'PHPExcel_Reader_Exception' with message 'Could not open example.xls for reading! File does not exist.' in C:\xampp\htdocs\task3\PHPExcel\Reader\Excel5.php:443 Stack trace: #0 C:\xampp\htdocs\task3\PHPExcel\IOFactory.php(268): PHPExcel_Reader_Excel5->canRead('example.xls') #1 C:\xampp\htdocs\task3\PHPExcel\IOFactory.php(191): PHPExcel_IOFactory::createReaderForFile('example.xls') #2 C:\xampp\htdocs\task3\index.php(5): PHPExcel_IOFactory::load('example.xls') #3 {main} thrown in C:\xampp\htdocs\task3\PHPExcel\Reader\Excel5.php on line 443

    ReplyDelete
  2. Muito boa dica para resolver seu problema esta na hora de salvar o arquivo em excel pois deve salvar o arquivo como Planilha Microsoft Excel 97-2003 depois verifica na pasta onde voce salvou se o arquivo ficou com a extensão xls e pronto

    ReplyDelete
  3. thanks man for your efforts keep it up

    ReplyDelete
  4. thankyo after searching whole day i found good and working code.

    ReplyDelete
  5. gracias por el codigo que en verdad me ayudo

    ReplyDelete
  6. it's showing all the data on browser, but no data in mysql table
    help

    ReplyDelete
  7. ( ! ) Strict standards: Only variables should be passed by reference in C:\wamp\www\index.php on line 6

    ReplyDelete
  8. Fatal error: Class 'PHPExcel' not found in C:\xampp\htdocs\pic\PHPExcel\Reader\HTML.php on line 150

    why this error?

    ReplyDelete
  9. code not working

    error
    Notice: Only variables should be passed by reference in C:\xampp\htdocs\bootstrap\livedatatable\uploadexcel.php on line 6

    Fatal error: Uncaught Error: Class 'PHPExcel' not found in C:\xampp\htdocs\bootstrap\livedatatable\PHPExcel\Reader\Excel2007.php:491 Stack trace: #0 C:\xampp\htdocs\bootstrap\livedatatable\PHPExcel\IOFactory.php(192): PHPExcel_Reader_Excel2007->load('C:\\xampp\\tmp\\ph...') #1 C:\xampp\htdocs\bootstrap\livedatatable\uploadexcel.php(12): PHPExcel_IOFactory::load('C:\\xampp\\tmp\\ph...') #2 {main} thrown in C:\xampp\htdocs\bootstrap\livedatatable\PHPExcel\Reader\Excel2007.php on line 491

    ReplyDelete
  10. replace with

    $value = explode(".", $_FILES["excel"]["name"]);
    $extension = strtolower(array_pop($value));

    ReplyDelete
  11. Warning: include(PHPExcel/IOFactory.php): failed to open stream: No such file or directory in C:\xampp\htdocs\TRY\import.php on line 11

    Warning: include(): Failed opening 'PHPExcel/IOFactory.php' for inclusion (include_path='C:\xampp\php\PEAR') in C:\xampp\htdocs\TRY\import.php on line 11

    Fatal error: Class 'PHPExcel_IOFactory' not found in C:\xampp\htdocs\TRY\import.php on line 12

    help

    ReplyDelete
  12. how to solve :Warning: include(PHPExcel/IOFactory.php): failed to open stream: No such file or directory in C:\wamp64\www\myschool\index.php on line 14

    ReplyDelete
  13. waths are that problem > Undefined variable: on line 8<
    are that some one can help me. I have try width csv and excl fil :-((

    ReplyDelete
  14. My problem are on line 8 >Undefined variable:on line 8< :-(

    ReplyDelete
  15. i have try this from >belgacrem OUNISS< doesn't work either.
    are som one can help

    ReplyDelete
  16. not support import data type DATE

    ReplyDelete
  17. Get same issue

    Notice: Only variables should be passed by reference in D:\xampp\htdocs\exceltodp\index1.php on line 6
    xlsx
    Warning: include(PHPExcel/IOFactory.php): failed to open stream: No such file or directory in D:\xampp\htdocs\exceltodp\index1.php on line 11

    Warning: include(): Failed opening 'PHPExcel/IOFactory.php' for inclusion (include_path='D:\xampp\php\PEAR') in D:\xampp\htdocs\exceltodp\index1.php on line 11

    Fatal error: Uncaught Error: Class 'PHPExcel_IOFactory' not found in D:\xampp\htdocs\exceltodp\index1.php:12 Stack trace: #0 {main} thrown in D:\xampp\htdocs\exceltodp\index1.php on line 12

    ReplyDelete
  18. Hello, i need a help that is i need add date from Excel to db,

    In Excel first 5 rows not reading but date reading from 4th row
    How it is why I need insert date as indian format like d/m/Y please

    ReplyDelete
  19. https://github.com/PHPOffice/PHPExcel

    ReplyDelete
  20. Hello.
    Empty lines are added to the database. what could be the reason.

    ReplyDelete
  21. you saved my life thanks broo
    .

    ReplyDelete
  22. bonjour, et merci pour tout ce que vous faite pour nous
    de mon cote tous marche bien ,j'ai juste un problem comment le inserts dans la base de donne avec un buttons

    ReplyDelete
  23. Hi I am Geeting error as
    Fatal error: Uncaught Error: Class 'PHPExcel' not found in C:\xampp\htdocs\imprtxltodb\PHPExcel\Reader\Excel2007.php:342 Stack trace: #0 C:\xampp\htdocs\imprtxltodb\PHPExcel\IOFactory.php(192): PHPExcel_Reader_Excel2007->load('C:\\xampp\\tmp\\ph...') #1 C:\xampp\htdocs\imprtxltodb\index.php(14): PHPExcel_IOFactory::load('C:\\xampp\\tmp\\ph...') #2 {main} thrown in C:\xampp\htdocs\imprtxltodb\PHPExcel\Reader\Excel2007.php on line 342

    ReplyDelete
  24. Notice: Only variables should be passed by reference in C:\laragon\www\bup\sanlishop_import.php on line 7

    ReplyDelete
  25. It's been a while since the last comment, but for those who don't read all the comments

    Replace the extension Part with this then you have no error on line 6
    $value = explode(".", $_FILES["excel"]["name"]);
    $extension = strtolower(array_pop($value));

    the second is the PHPExcel Class is not in the downloaded archive you can get it here: https://github.com/paysera/fork-codeplex-phpexcel/archive/refs/heads/master.zip put it in the script folder and ready is.

    ReplyDelete