Read excel file and import data into MySQL database using PHPExcel

Sometimes, we need to read a huge amount of data from excel files (.xls or .xlsx in extension) and have to store these data into a database for future use. We can achieve this by using PHP, PHPExcel library, and MySQL. this So, in this tutorial, I will explain to you how to read excel file and import data into MySQL database using PHPExcel. PHPExcel is a PHP library for reading and writing spreadsheet files. In this tutorial, we will use MySQL database for storing data extracted from excel file.

Also, you can enjoy the live demo example of read excel file and import data into MySQL database using PHPExcel for better understanding. As well as you can get the full source code from the download link.

MySQL Database Table

1. CREATE TABLE IF NOT EXISTS `user_details` (
2. `id` int(11) NOT NULL,
3. `name` varchar(50) NOT NULL,
4. `mobile` bigint(10) NOT NULL,
5. `country` varchar(50) NOT NULL
6. );

If you want to store or import excel data into MySQL database, you need a database table. So, create the user_details table in the database. Copy the above query and execute in your database query editor.

Database Configuration (db.php)

1. <?php
2. define('HOSTNAME','localhost');
3. define('DB_USERNAME','username');
4. define('DB_PASSWORD','password');
5. define('DB_NAME', 'database-name');
7. //global $con;
8. $con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
9. // Check connection
10. if(mysqli_connect_errno($con)) echo "Failed to connect MySQL: " .mysqli_connect_error();
11. ?>

The above PHP code will establish the connection with your MySQL database. Just replace the username, password, and database-name with your credentials.

Upload Form (index.php)

1. <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" enctype="multipart/form-data">
2. Upload excel file :
3. <input type="file" name="uploadFile" value="" />
4. <input type="submit" name="submit" value="Upload" />
5. </form>

A simple HTML form for uploading excel file into the server. Excel file with both .xls and .xlsx extensions are applicable in this example.

Read excel file and import into MySQL table (index.php)

1. <?php
2. if(isset($_POST['submit'])) {
3. if(isset($_FILES['uploadFile']['name']) && $_FILES['uploadFile']['name'] != "") {
4. $allowedExtensions = array("xls","xlsx");
5. $ext = pathinfo($_FILES['uploadFile']['name'], PATHINFO_EXTENSION);
6. if(in_array($ext, $allowedExtensions)) {
7. $file_size = $_FILES['uploadFile']['size'] / 1024;
8. if($file_size < 50) {
9. $file = "uploads/".$_FILES['uploadFile']['name'];
10. $isUploaded = copy($_FILES['uploadFile']['tmp_name'], $file);
11. if($isUploaded) {
12. include("db.php");
13. include("Classes/PHPExcel/IOFactory.php");
14. try {
15. //Load the excel(.xls/.xlsx) file
16. $objPHPExcel = PHPExcel_IOFactory::load($file);
17. } catch (Exception $e) {
18. die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage());
19. }
21. //An excel file may contains many sheets, so you have to specify which one you need to read or work with.
22. $sheet = $objPHPExcel->getSheet(0);
23. //It returns the highest number of rows
24. $total_rows = $sheet->getHighestRow();
25. //It returns the highest number of columns
26. $total_columns = $sheet->getHighestColumn();
28. echo '<h4>Data from excel file</h4>';
29. echo '<table cellpadding="5" cellspacing="1" border="1" class="responsive">';
31. $query = "insert into `user_details` (`id`, `name`, `mobile`, `country`) VALUES ";
32. //Loop through each row of the worksheet
33. for($row =2; $row <= $total_rows; $row++) {
34. //Read a single row of data and store it as a array.
35. //This line of code selects range of the cells like A1:D1
36. $single_row = $sheet->rangeToArray('A' . $row . ':' . $total_columns . $row, NULL, TRUE, FALSE);
37. echo "<tr>";
38. //Creating a dynamic query based on the rows from the excel file
39. $query .= "(";
40. //Print each cell of the current row
41. foreach($single_row[0] as $key=>$value) {
42. echo "<td>".$value."</td>";
43. $query .= "'".mysqli_real_escape_string($con, $value)."',";
44. }
45. $query = substr($query, 0, -1);
46. $query .= "),";
47. echo "</tr>";
48. }
49. $query = substr($query, 0, -1);
50. echo '</table>';
52. // At last we will execute the dynamically created query an save it into the database
53. //mysqli_query($con, $query);
54. if(mysqli_affected_rows($con) > 0) {
55. echo '<span class="msg">Database table updated!</span>';
56. } else {
57. echo '<span class="msg">Can\'t update database table! try again.</span>';
58. }
59. // Finally we will remove the file from the uploads folder (optional)
60. unlink($file);
61. } else {
62. echo '<span class="msg">File not uploaded!</span>';
63. }
64. } else {
65. echo '<span class="msg">Maximum file size should not cross 50 KB on size!</span>';
66. }
67. } else {
68. echo '<span class="msg">This type of file not allowed!</span>';
69. }
70. } else {
71. echo '<span class="msg">Select an excel file first!</span>';
72. }
73. }
74. ?>

Example Explained

First, we will go through the validation process. We will check whether the file is uploaded or not, if uploaded then we will check the file extension, and also we will check the file size. Here in this example, the maximum file size is limited to 50 KB in size.

pathinfo($_FILES[‘uploadFile’][‘name’], PATHINFO_EXTENSION) – Returns the extension of the file.

$objPHPExcel->getSheet(0) – An excel file may contain many sheets, so you have to specify which one you need to read or work with.

$sheet->getHighestRow() – Returns the highest number of rows.

$sheet->getHighestColumn() – Returns the highest number of columns.

$sheet->rangeToArray(‘A’ . $row . ‘:’ . $highest_column . $row, NULL, TRUE, FALSE) – Read a single row of data and store it as an array.

unlink($file) – Removes the file from the specified path.

Complete source code – Read excel file and import data into MySQL database using PHPExcel

Try the live demo example of Read excel file and import data into MySQL database using PHPExcel. Download the complete source code from the below download link and please like and share the tutorial link with others.