Insert Excel File into MySQL using PHP

Hi! Let's see how to insert excel file into mysql database using php. In PHP importing CSV data to MySQL is a breeze but not the same case when it comes to Excel file. Excel format is somewhat tricky to parse with php but you don't have to worry. I'm going to show you a much simpler method to achieve it. For that you'll need an excel parser library PHPExcel. This is an excellent lib and supports both 'XLS' and 'XLSX' format.

For those who don't like using third-party library here's a way to do it with core php alone. Just save your excel file as csv format, then read it with native fgetcsv() function and insert into db. Simple! But I'm not going into greater detail about the process here. As for this tutorial I'm going to focus on dealing with excel file. Come, let me show you how to read excel file and store data into mysql db with php.

How to Insert Excel File into MySQL using PHP?

Read:

The process is supposed to be like this. You have a mysql table and an excel file with some data to be stored in db. Here I'm going to assume the excel column headers matches the table fields. Now establish mysql connection, read through excel rows one by one and insert into database. Here goes the step by step process for inserting excel to mysql.

Step 1) First download PHPExcel library, unzip and move it to your root folder.

Step 2) Create necessary database and table in mysql. This is the one I'm going to use for the demo.

CREATE DATABASE IF NOT EXISTS `db_employee`;
USE `db_employee`;
CREATE TABLE IF NOT EXISTS `Employee` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(30) NOT NULL,
  `Designation` varchar(30) NOT NULL,
  `Salary` varchar(10) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Step 3) Create an excel file 'empdetails.xlsx' and add some data to it.

Id	Name	Designation	Salary
1	Suki Burks	Developer	114500
2	Fred Zupers	Technical Author	145000
3	Gavin Cortez	Team Leader	235500

Step 4) Create index.php file and add the below code to it. This is the core of the entire process. The php script loads PHPExcel library, establish connection to mysql database, read the entire excel file, store the rows in associative arrays, loop through it and insert into the database.

<?php
// set path
set_include_path(get_include_path() . PATH_SEPARATOR . "Classes/");

// include phpexcel lib
include "PHPExcel/IOFactory.php";

// mysql connection settings
$host = "localhost";
$uname = "root";
$pwd = "";
$dbname = "db_employee";

// excel file to import
$fname = "empdetails.xlsx";

try
{
    // connect to mysql
    $con = mysqli_connect($host, $uname, $pwd, $dbname);
    
    // load excel file
    $obj = @PHPExcel_IOFactory::load($fname);

    $obj->setActiveSheetIndex(0);
    $data = $obj->getActiveSheet()->toArray(null, true, true, true);

    $columns = implode(",", array_values($data[1]));

    for ($i=2; $i<=count($data); $i++)
    {
        $sql = "INSERT INTO employee ($columns) VALUES ('" . implode("','",array_values($data[$i])) . "')";
        mysqli_query($con, $sql);
    }
    echo "Successfully imported excel file to mysql!";
}
catch(Exception $err)
{
    die('Error ' . $err->getMessage());
}
mysqli_close($con);
?>

The PHPExcel parser returns excel data as array of arrays. That is, one array per row. The first row is the column headers and the subsequent ones are the actual data themselves.

Now run the file and if everything goes right, the data from excel file will be imported to mysql db and will show you some success message.

Now this is what I got - the mysql table after inserting excel data.

Read:

I hope now you have got idea about inserting excel file into mysql using php. Feel free to tweak the code to fit your logic. And don't forget to share the script in social media.

Insert Excel File into MySQL using PHP

How to Insert Excel File into MySQL using PHP?

No comments:

Post a Comment