Welcome to a tutorial on how to import an Excel Spreadsheet into a MySQL database with PHP. So you have a project that requires reading some data from a spreadsheet and save them into the database? Sadly, PHP cannot read Excel files natively. In order to import Excel files into the database:
Just how exactly is this done? Let us walk through an example in this guide – Read on!
ⓘ I have included a zip file with all the example source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.
QUICK SLIDES
TABLE OF CONTENTS
DOWNLOAD & NOTES
First, here is the download link to the source code as promised.
QUICK NOTES
- A copy of PHPSpreadsheet is not included in the zip file. Please download the latest version by yourself.
- Create a test database and import 2a-dummy.sql.
- Change the database settings in 3-import.php to your own, then launch it in the browser.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.
EXAMPLE CODE DOWNLOAD
Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
All right, let us now get into an example of importing an Excel file into the database.
STEP 1) INSTALL PHPSPREADSHEET
There are no native functions in PHP to read Excel files. So we need to download and use a third-party library call PHPSpreadsheet – Here’s how to get it.
- The easiest way is to use an application manager called Composer – Something like Git, quite a useful one for pulling libraries automatically. A small hassle to download and install, but a one-time effort nonetheless.
- After installing Composer – Simply open the command prompt, navigate to your project folder.
- Run composer require phpoffice/phpspreadsheet.
That’s all. Composer will automatically pull the latest version into the vendor/ folder.
STEP 2) DUMMY DATABASE TABLE & EXCEL FILE
DUMMY USERS TABLE
2a-dummy.sql
CREATE TABLE `users` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email` (`email`); ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;For this example, we will use a very simple users table with only 3 fields.
Field | Description |
id | Primary key, auto-increment. |
name | The user’s name. |
The user’s email address. |
DUMMY EXCEL FILE
2b-dummy.xlsx
Name | |
John Doe | |
Jane Doe | |
Josh Doe | |
Joy Doe | |
Janus Doe | |
Jay Doe | |
June Doe | |
Julius Doe | |
Jess Doe | |
Jack Doe |
Next, we have some dummy users in an Excel file that need to be imported into the above table.
STEP 3) PHP IMPORT SCRIPT
3-import.php
<?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbhost = "localhost"; $dbname = "test"; $dbchar = "utf8"; $dbuser = "root"; $dbpass = ""; try { $pdo = new PDO( "mysql:host=$dbhost;charset=$dbchar;dbname=$dbname", $dbuser, $dbpass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ] ); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) PHPSPREADSHEET TO LOAD EXCEL FILE require "vendor/autoload.php"; $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $spreadsheet = $reader->load("2b-dummy.xlsx"); $worksheet = $spreadsheet->getActiveSheet(); // (C) READ DATA + IMPORT $sql = "INSERT INTO `users` (`name`, `email`) VALUES (?, ?)"; foreach ($worksheet->getRowIterator() as $row) { // (C1) FETCH DATA FROM WORKSHEET $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $data = []; foreach ($cellIterator as $cell) { $data[] = $cell->getValue(); } // (C2) INSERT INTO DATABASE print_r($data); try { $stmt = $pdo->prepare($sql); $stmt->execute($data); echo "OK - USER ID - {$pdo->lastInsertId()}<br>"; } catch (Exception $ex) { echo $ex->getMessage() . "<br>"; } $stmt = null; } // (D) CLOSE DATABASE CONNECTION if ($stmt !== null) { $stmt = null; } if ($pdo !== null) { $pdo = null; }Yep, this one looks a little intimidating at first but is actually very straightforward.
- Connect to the database. Remember to change the settings to your own.
- Load the PHPSpreadsheet library, and use it to open the dummy Excel spreadsheet.
- Read the cells of the spreadsheet, import them into the database.
- The end, close the database connection.
SPREADSHEETS WITH FORMULA
Take note that PHPSpreadSheet will not evaluate the cell formula. Do your own “convert formulas to values” in Excel, and save it as a different spreadsheet before importing.
DATE & TIME CELLS
This is yet another gotcha… Basically, convert the date/time cell into a string or text first.
LINKS & REFERENCES
- Need to do the opposite of exporting into Excel files? – Here’s how.
- Check out the PHPSpreadsheet GitHub Page here.
- Also, the official documentation.
YOUTUBE TUTORIAL
INFOGRAPHIC CHEAT SHEET
THE END
Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!