How do you import data from excel to sql using 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 Composer – composer 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

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.

DUMMY EXCEL FILE

2b-dummy.xlsx

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.

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.

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!