Import Data Into MySQL From Excel File Using PHPExcel

TL;DR

A PHP developer shares how to use the PHPExcel library to upload data from an Excel file into a MySQL database. The tutorial includes creating a table in the database, reading the Excel file using PHPExcel, and inserting the data into the MySQL database using a loop with an insert query. The tutorial also provides links to download the required libraries and source files.

Although rare, but there might come a case when you might have to ask someone to upload an excel file and use it’s data for storage purposes. Most of the times you’ll get a request for exporting data into an excel file and we’ll get there in the next post. But for now, let’s just say that we have a client that wants us to have his employees upload some data which he can save it in a database for further processing.

For this there are many PHP libraries available, but I”m are going to use the most common i.e. PHPExcel. It’s easy to use, has a ton of examples, has a lot of support and Q&A on stackoverflow. What I’m telling is no different than what’s already out there, but it should be a part of a developers blog. So here it is:

So, the client says that he has an excel spreadsheet having employees info which he would like in a MySQL database. The info he receives is provided to him daily in the lot of 100s and he has to upload them into the system. You can easily create a file upload system for him and then when the file is uploaded all you have to do is read it and save its content.

So we have a MySQL database `workforce` and in it we have to create an `employees` table with the given columns. We’ll run the following query for that:

CREATE TABLE IF NOT EXISTS `employees` (
`id` mediumint(8) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
`fname` varchar(255) DEFAULT NULL,
`lname` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`phone` varchar(100) DEFAULT NULL,
`company` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

No our table is ready. For importing the data we’ll need the latest PHPExcel Library. The latest stable release is PHPExcel 1.8.0, which we can download at:
PHPExcel by MarkBaker. It’s also available on github at PHPExcel on github, where you can also find it’s DOCUMENTATION.

In addition to this I’m also using a MySQLi library for db management, taken directly from opencart, as it’s light and reliable. You can download it along with the source files for this code.

Now, once your file is uploaded, we can manage it in our php code. The first thing to do is to include the required PHPExcel and MySQLi DB libraries.

/** Include PHPExcel and MySQLi db */
require_once dirname(__FILE__) . '/../Classes/DB.php';
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';

//Create DB object
use DBMySQLi;

Next thing is to read the the excel cell contents. For this we’ll load the uploaded file for our library, if your uploaded file is not in the same directory as that of this php file, remember to use the absolute path for loading the excel file like (/var/file/html/upload/file.xlsx). We’ll use the “getWorksheetIterator” function of the PHPExcel class to iterate through the cells and get the data in a desired array.

// Create new PHPExcel object
$objPHPExcel = PHPExcel_IOFactory::load("employees.xlsx");

$dataArr = array();

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle     = $worksheet->getTitle();
$highestRow         = $worksheet->getHighestRow(); // e.g. 10
$highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($row = 1; $row <= $highestRow; ++ $row) {
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$dataArr[$row][$col] = $val;
}
}
}

No we’ll just unset the first row as that’s a header row which we won;t need to insert, at least in our example.

unset($dataArr[1]); // since in our example the first row is the header and not the actual data

Now, all we have to do is insert the data into our MySQL database. As we are using an OpenCart MySQLi library, all we have to do is make a connection and write a loop with the insert query.

$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'workforce';

$db = new MySQLi($hostname, $username, $password, $database);

foreach($dataArr as $val){
$query = $db->query("INSERT INTO employees SET fname = '" . $db->escape($val['1']) . "', lname = '" . $db->escape($val['2']) . "', email = '" . $db->escape($val['3']) . "', phone = '" . $db->escape($val['4']) . "', company = '" . $db->escape($val['5']) . "'");
}

That’s it. If there’s an error the DB library will print it out. You also don’t have to escape the data as you can do so by using the $db->escape method.

If you have any question, feedback, requests or suggestions, feel free to say hello in the comment box below. You can download the entire code and required files from HERE.