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.

In order to import Excel files into the database:

  1. We have to use a third-party library that can read Excel files. PHPSpreadsheet is a good recommendation, and we can get it easily using Composercomposer require phpoffice/phpspreadsheet
  2. Create the database table, and Excel file to import.
  3. Use PHPSpreadsheet to read the Excel spreadsheet, extract the data, and insert them 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 Import Excel Useful Bits & Links
Tutorial Video The End

 

 

DOWNLOAD & NOTES

First, here is the download link to the source code as promised.

 

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.

 

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, please feel free to comment below. I try to answer 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.

 

IMPORT EXCEL INTO MYSQL

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.
email The user’s email address.

 

DUMMY EXCEL FILE

2b-dummy.xlsx
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

3-import.php
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbhost = 'localhost';
$dbname = 'test';
$dbchar = 'utf8';
$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, import them into the database.
  4. The end, close the database connection.

 

 

USEFUL BITS & LINKS

That’s all for this tutorial, and here is a small section on some extras that may be useful to you.

 

LINKS & REFERENCES

 

YOUTUBE TUTORIAL

 

INFOGRAPHIC CHEAT SHEET

How To Import Excel Files In PHP MySQL (click to enlarge)

 

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!

11 thoughts on “3 Steps To Import Excel Spreadsheet Into MySQL With PHP”

  1. Hi,
    This is an amazing solution and it helped my needs, I had never used Composer or PHPSpreadsheet before today, but I researched it and used it for this.

    The issue I have is that I have a date column and a time column in my xlsx file but the import script imports the date column as “44274” and the time column as “0.47916666666667”.
    Any idea why? and can you provide a solution please?

  2. thanks for your creation , step and stop , It’s work very well , but I must use excel to mssql , I try to use pdo , It’s not work . I can connect the mssql 2019 server , the script is different , can you help me , how to use excel inport to mssql .

  3. Thank you for creating, and posting such a GREAT, succinct explanation. Unfortunately, for some strange reason when the doe family data file is selected for upload, the page refreshes to display the entire text of the ‘3-import.php’ file…and the doe family data does not appear in the db table. Any idea as to what’s going on???

  4. how do i specify the rows i want to insert to database… example i only want to save the data from row 6 till the end.
    i find it easier using phpexcel

    $excel->getActiveSheet()->getCell(‘F’.$i)->getValue();
    how do i do it in phpspreadsheet

  5. Hi,

    Why do you require this file:
    // (2) INIT MYSQL
    require “config.php”;

    What should should be the role of this config.php file?

Leave a Comment

Your email address will not be published. Required fields are marked *