3 Steps To Import Excel Spreadsheet Into MySQL With PHP

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.

Just how exactly is this done? Let us walk through an example in this guide – Read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

TUTORIAL VIDEO

 

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 called 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, open the command prompt and navigate to your project folder – cd HTTP/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

2A) DUMMY USERS TABLE

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

ALTER TABLE `users`
  MODIFY `id` bigint(20) 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.
email	The user’s email address.

 

 

2B) DUMMY EXCEL FILE

Name	Email
John Doe	john@doe.com
Jane Doe	jane@doe.com
Josh Doe	josh@doe.com
Joy Doe	joy@doe.com
Janus Doe	janus@doe.com
Jay Doe	jay@doe.com
June Doe	june@doe.com
Julius Doe	julius@doe.com
Jess Doe	jess@doe.com
Jack Doe	jack@doe.com

Next, we have some dummy users in an Excel file that need to be imported into the above table.

 

 

STEP 3) PHP IMPORT SCRIPT

<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8mb4";
$dbuser = "root";
$dbpass = "";
$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
]);
 
// (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.

1. Connect to the database. Remember to change the settings to your own.
2. Load the PHPSpreadsheet library, and use it to open the dummy Excel spreadsheet.
3. Read the cells of the spreadsheet, and import them into the database.
4. 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.