Import Excel Data To Database Using PHP

As many of developers face issues related to move data from Excel file into SQL Database. If data is less than you can do that manually but what if there are 1000 of data in Excel file & moving 1000 of data manually from Excel file into SQL Database is frustrated.

In this article, will share steps for importing data from an Excel file to a SQL Server database will be explained using PHPExcel library.

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. 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.

1. Install PHPExcel from github (Note: PHPExcel officially deprecated in 2017 and permanently archived in 2019 but still you can use it for your work done.)

2. After downloading PHPExcel from github store it in a folder excelData

3. Over their make a new file index.php & let start coding!

(Preview of excel file which we need to move that data into sql database)

1. We need to connect Database server to index.php.

    $connect = mysqli_connect("localhost", "root", "", "test");
                  

2. We need to include PHPExcel file into index.php.

    include("PHPExcel/Classes/PHPExcel/IOFactory.php"); // Add PHPExcel Library in this code
                  

3. After calling PHPExcel file will use its functionality.

    $objPHPExcel = PHPExcel_IOFactory::load($file);
                  

Above code will create object of PHPExcel library by using load() method and in load method define path of selected file

4. Now, create table in DB where imported data will store.

    CREATE TABLE `excelData` (
      `id` int(11) NOT NULL,
      `name` varchar(250) NOT NULL,
      `email` varchar(300) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
                  

(SQL database preview)

5. Let's set Html file which will get excel file as a input from user.

    <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>
                  

Now, when when user import excel file & click on submit button PHP code will call.

    if(isset($_POST["import"])){
      //insert code inside
    }
                  

This block will activate when user click on submit button. But what if user click on another file. To check file extension, I've used below code

    $tmp = explode(".", $_FILES["excel"]["name"]);
    $extension = end($tmp);

    $allowed_extension = array("xls", "xlsx", "csv"); //allowed extension
                  

Above code is used to define valid extension for import file. Now, its time to validate & for validation below is the code is used.

    if(in_array($extension, $allowed_extension)){
      //insert code inside
    } 
                    

If block will compare file imported by use from $extension with $allowed_extension & then only file will run otherwise it will throw an error.

Below is the code used in if block.

    $file = $_FILES["excel"]["tmp_name"]; // getting temporary source of excel file
    include("PHPExcel/Classes/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 excelData(name, email) VALUES ('".$name."', '".$email."')";
      mysqli_query($connect, $query);
      $output .= '<td>'.$name.'</td>';
      $output .= '<td>'.$email.'</td>';
      $output .= '</tr>';
      }
    } 
    $output .= '</table>';
                  

Now, if you look at the above code you will notice that to avoid first row from excel sheet which will contain heading have started loop from $row=2 & to get end point of loop have used $highestRow;. $highestRow; get last point of excel sheet & to get that i have called PHPExcel function $objPHPExcel->getWorksheetIterator() as $worksheet in foreach loop & inside that loop have used $highestRow = $worksheet->getHighestRow(); to get all data.

After running above code. Excel data will successfully imported into SQL Database.

(SQL database preview after running above code)

You will get all files, when you download the source code. And after than you can edit it according to you

if you face any issues you can contact by asking question with article link.

You can go through Demo as well as Download source code for the same & make changes according to you