Friday, 4 March 2016

Read Excel Data and Insert into Mysql Database using PHP

           ,      30 comments   



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. raghu2 June 2016 at 02:32

    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. Save26 June 2016 at 11:59

    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. papah12 October 2016 at 00:12

    THANK YOU SO MUCH

    ReplyDelete
  4. pcgeek43168 November 2016 at 05:10

    How do I get Sheet 1 only

    ReplyDelete
  5. Herbert9 November 2016 at 02:53

    thanks man for your efforts keep it up

    ReplyDelete
  6. Unknown27 November 2016 at 04:17

    thks

    ReplyDelete
  7. Unknown2 May 2017 at 22:45

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

    ReplyDelete
  8. Unknown9 May 2017 at 17:37

    gracias por el codigo que en verdad me ayudo

    ReplyDelete
  9. Aj12 June 2017 at 04:55

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

    ReplyDelete
  10. Milos2 September 2017 at 08:31

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

    ReplyDelete
  11. Unknown8 March 2018 at 05:51

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

    why this error?

    ReplyDelete
  12. Unknown25 May 2018 at 19:19

    code not working

    ReplyDelete
  13. Unknown25 May 2018 at 19:19

    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
  14. Hollaman22 June 2018 at 21:17

    Hi, Thanks

    ReplyDelete
  15. Unknown13 January 2019 at 10:43

    replace with

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

    ReplyDelete
  16. Unknown16 February 2019 at 01:16

    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
  17. farisbavas19 March 2019 at 06:49

    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
  18. Unknown31 March 2019 at 13:32

    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
  19. Unknown31 March 2019 at 13:47

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

    ReplyDelete
  20. Unknown31 March 2019 at 14:10

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

    ReplyDelete
  21. Reza10 December 2019 at 23:43

    not support import data type DATE

    ReplyDelete
  22. Chithra11 December 2019 at 08:29

    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
  23. Dadapeer19 January 2020 at 07:32

    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
  24. Unknown1 February 2020 at 00:44

    https://github.com/PHPOffice/PHPExcel

    ReplyDelete
  25. ismailfedakar14 February 2020 at 08:24

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

    ReplyDelete
  26. Unknown1 March 2020 at 04:35

    you saved my life thanks broo
    .

    ReplyDelete
  27. Pc astuces14 May 2020 at 20:11

    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
  28. Unknown7 June 2020 at 02:03

    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
  29. Unknown31 August 2020 at 02:54

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

    ReplyDelete
  30. Marcus25 January 2023 at 11:05

    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